SQL Server 2008 - calculating backup ratios

Backup compression is one of the coolest new features in SQL Server 2008. This nice little query gives you an overview of the average compression ratios. I used another T-SQL enhancement (GROUPING SETS) to show the ratio per database, backup type, ...


SELECT
DatabaseName =ISNULL(database_name,'(All Databases)'),
BackupType =ISNULL(CAST(type AS VARCHAR(15)), '(All Types)'),
CompressionRatio = AVG( CAST(backup_size/ compressed_backup_size AS DECIMAL(5,2)))
from msdb..backupset
WHERE (backup_size <> compressed_backup_size)
GROUP BY GROUPING SETS((database_name,type), (database_name), (type), ())
ORDER BY 1,2

Check out these excelent blogposts for more info on backup compression:

Paul Randal - SQL Server 2008: Backup Compression CPU Cost

SQLCAT - Tuning the Performance of Backup Compression in SQL Server 2008<