Get Backup Compression Effectiveness

SELECT

Database_Name AS Database_Name,

CASE

WHEN type = 'D' THEN 'Database'

WHEN type = 'I' THEN 'Differential Database'

WHEN type = 'L' THEN 'Transaction log'

WHEN type = 'F' THEN 'File or Filegroup'

WHEN type = 'G' THEN 'Differential File'

WHEN type = 'P' THEN 'Partial'

WHEN type = 'Q' THEN 'Differential Partial'

ELSE NULL

END As BackupType,

Backup_Start_Date AS Backup_Start_Date,

CONVERT(int,Backup_Size/1048576) AS 'BackupSizeMB',

CONVERT(int, Compressed_Backup_Size/1048576) AS 'CompressedBackupSizeMB',

(Backup_Size/1048576)/(Compressed_Backup_Size/1048576) AS 'CompressionRatio'

FROM msdb.dbo.backupset

WHERE backup_finish_date > GETDATE() - 7

ORDER BY Database_name, backup_finish_date DESC