SQL Server backup very large compared to table size I have a database with 31 tables and my largest table has about 12 columns and 750,000 records. I checked the size of all my tables using this query:
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID &gt; 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

and it says that my largest table has 147.45 MB of data:

When I total all of the space used from the tables it is not even 200 MB. However, when I create a backup file it is 4.5 GB.
I am not sure what is going on.
It is interesting that shrinking reduced the backup size. Backup doesn't include unused extents. And such a massive reduction in backup size can't be explained by &quot;lots of half-full pages&quot; alone.
This was apparently some very special situation, in the sense that a shrink did indeed reduce the backup size to that level. One theory I have is that there are heap tables that had lots and lots of unused pages (but still being considered as used from the backup's perspective). I.e., you have some table which is being heavily inserted into and deleted from, and the delete pattern causes the free space to never being deallocated, so to speak.
If you want to aid in the value for this thread for future readers, you can run something like below to see if you have heap tables. We can't say now if potential heap tables has vast amount of free space since the shrink was performed already, but at least we would know if you have heap tables.
SELECT OBJECT_SCHEMA_NAME(OBJECT_ID), OBJECT_NAME(object_id), type_desc
FROM sys.indexes 
WHERE type_desc = 'HEAP'
AND OBJECTPROPERTY(object_id, 'IsMsShipped') = 0

Perhaps LOB data not being compacted/deallocated could also explain this, but that is just me thinking out loud...