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
|