Cool Tips‎ > ‎Backup and Restore‎ > ‎

Backup - 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


ċ
Get Backup Compression Effectiveness.sql
(1k)
Andy Hughes,
Jun 7, 2012, 10:54 AM
Comments