MSSQL Server备份文件大小与恢复后数据占用空间不符咨询

• 压缩备份导致体积大幅缩小：MSSQL的压缩备份仅存储数据库中实际有数据的页，对于存在大量未使用空间（比如曾批量删除数据但未收缩）的数据库，压缩后的备份文件体积会远小于数据库实际文件大小。恢复时数据库会还原到原文件的总容量（含未使用空间），所以会出现备份204GB但恢复后总空间706GB的情况。用以下查询确认备份是否启用压缩：

  SELECT 
      bs.database_name,
      ROUND(bs.backup_size/1024/1024/1024, 2) AS backup_size_GB,
      ROUND(bs.compressed_backup_size/1024/1024/1024, 2) AS compressed_backup_size_GB,
      bs.is_compressed
  FROM msdb.dbo.backupset bs
  WHERE bs.database_name = '你的目标数据库名'
  ORDER BY bs.backup_finish_date DESC;
  

• 数据库预留了大量未分配空间：原数据库的数据文件总大小为706GB，但实际仅使用204GB存储数据，备份文件只会打包已使用的数据页。恢复过程会重建与原库一致的文件结构，所以数据文件总大小回到706GB，但实际已用数据仍为204GB。用以下查询区分文件总容量、已用空间和空闲空间：

  SELECT 
      name AS 文件名称,
      ROUND(size/1024/1024, 2) AS 总容量_GB,
      ROUND(FILEPROPERTY(name, 'SpaceUsed')/1024/1024, 2) AS 已用空间_GB,
      ROUND((size - FILEPROPERTY(name, 'SpaceUsed'))/1024/1024, 2) AS 空闲空间_GB
  FROM sys.database_files
  WHERE type_desc = 'ROWS';
  

• 误将多备份文件的单个文件体积当成总备份大小：如果数据库包含多个数据文件，备份可能生成多个物理备份文件，你看到的204GB只是其中一个，所有备份文件的总大小会接近706GB。用以下查询查看完整备份文件列表：

  SELECT 
      bs.database_name,
      bmf.physical_device_name,
      ROUND(bs.backup_size/1024/1024/1024, 2) AS 单备份集总大小_GB
  FROM msdb.dbo.backupset bs
  JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
  WHERE bs.database_name = '你的目标数据库名'
  ORDER BY bs.backup_finish_date DESC;
  

• 错误统计了日志文件空间：你查询的“已用数据总空间”可能包含了日志文件的容量。完整备份仅包含活动日志部分，恢复后日志文件会还原到原库的日志文件大小。通过sys.database_files查询的type_desc字段可以区分数据文件（ROWS）和日志文件（LOG），单独统计数据文件的空间即可。

内容的提问来源于stack exchange，提问作者luckyluke