Get Backup Compression Effectiveness
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Not Supported
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: ChillyDBA
Date: 7 May 2012
Description
Retrieves the backup compression effectiveness for all types of backup on all databases on a server.
Defaults to the last 7 days only.
Code
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