SQL SERVER – Finding Compression Ratio of Backup

During the recent Comprehensive Database Performance Health Check, I was asked if there is any way to know compression ration of backup if we enable compressed backup settings in SQL Server.

As you know that a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

Let us see a quick script which will estimate how much compression of the database will happen if we have enabled backup compression settings for SQL Server

SELECT database_name, backup_size, compressed_backup_size,
backup_size/compressed_backup_size AS CompressedRatio
FROM msdb..backupset; 

When you run above script it will return results like the following:

SQL SERVER - Finding Compression Ratio of Backup compression

If you look at the above script you can see the name of the database the backup size as well as compressed backup size estimation. Based on this, you can figure out the compression ration.

Here are the few related articles:

Let me know if you have any other script which can help us to figure out the compression ration of the backup. I will post the blog with due credit to you.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Backup, SQL DMV, SQL Scripts, SQL Server
Previous Post
SQL SERVER – How to Get List of SQL Server Instances Installed on a Machine Via T-SQL?
Next Post
SQL SERVER – Disable IntelliSense in SQL Server Management Studio

Related Posts

1 Comment. Leave new

  • just for info, to get the percentage reduced by and to use

    (compressed_backup_size/backup_size) * 100 AS CompressedTo
    100-((compressed_backup_size/backup_size) * 100) AS CompressedBy


Leave a Reply