Finding exact compression ratio of a SQL 2008 backup
Using native backup compression in SQL 2008 can be very rewarding for many reasons. However it is always nice to know exactly how much space you are saving by enabling backup compression over traditional backup and have a statistical proof of the same.
Fortunately SQL Server maintains exact backup compression ratio for all backups which were compressed. Use below code to find exact compression ratio for an exact backup:
select backup_set_id, name, description,backup_start_date, type,
cast((backup_size/1048576) * 100 as real) / 100 as BackupSizeMB,
cast ((compressed_backup_size/1048576) * 100 as real) /100 as CompressedSizeMB,
cast((((cast(cast(((compressed_backup_size/backup_size) * 10000) as int)/100 as real) - 100)) * -1) as varchar) + '%' as CompressedRatio
from msdb..backupset
where name like '%[Backupfilename]%'
order by backup_finish_date desc
Note : For an uncompressed backup, Compression ratio will be reported as -0%
Fortunately SQL Server maintains exact backup compression ratio for all backups which were compressed. Use below code to find exact compression ratio for an exact backup:
select backup_set_id, name, description,backup_start_date, type,
cast((backup_size/1048576) * 100 as real) / 100 as BackupSizeMB,
cast ((compressed_backup_size/1048576) * 100 as real) /100 as CompressedSizeMB,
cast((((cast(cast(((compressed_backup_size/backup_size) * 10000) as int)/100 as real) - 100)) * -1) as varchar) + '%' as CompressedRatio
from msdb..backupset
where name like '%[Backupfilename]%'
order by backup_finish_date desc
Note : For an uncompressed backup, Compression ratio will be reported as -0%
Comments