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. Here is how to find the compression ratio of backup files with a quick script.

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:

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:

• SQL SERVER – 2008 – Introduction to New Feature of Backup Compression
• SQL SERVER – SQL Server Compression Estimator
• SQL SERVER – COLUMNSTORE and COLUMNSTORE_ARCHIVE data compression
• SQL SERVER – 2008 – Introduction to Row Compression

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.