SQL Server Stored Procedure to Calculate Database Backup Compression Ratio

By:   |   Comments (4)   |   Related: > Compression


SQL Server offers the ability to create compressed backups, but it is hard to tell what the size of the compressed backup will be compared to the uncompressed backup.  So in this tip we look at a simple procedure to help figure what the size of the compressed backup will be before implementing backup compression.


The requirement is to create a simple T-SQL stored procedure that will take a user database and provide accurate database backup compression information without consuming disk space. It is very hard to give an estimation of the database compression ratio, because the compression ratio of a compressed backup depends on the data that needs to be compressed (data types, encryption, consistency, density, etc.).

The benefit of using database backup compression is that a compressed backup is usually smaller than an uncompressed backup of the same data.  Compressing a backup typically requires less I/O and therefore usually increases the backup speed significantly.  The drawback of using backup compression is that by default, database compression significantly increases the CPU usage, and the additional CPU consumed by the compression process might impact other concurrent operations and reduce performance.

The procedure that is shown here will act as a decision tool for deciding whether to use backup compression or not.  The solution involves creating a T-SQL stored procedure that takes the database name as a parameter and then produces the backup compression ratio percentage as an output parameter.

  1. The procedure takes the database name as @dbName.
  2. The procedure creates a BACKUP DATABASE T-SQL statement that does a compressed backup to the given database parameter, but to the 'nul' DISK device.  The 'nul' device is a special 'file' in the file system.  Anything that is written to the 'nul' device is discarded. So actually, you're not writing the backup file anywhere. The result is that you are backing up the database, but the file is never written in the first place. So no disk space is consumed. However, a row in msdb..backupset is created, containing the data needed to figure out the compression percentage.
  3. The procedure calculates the database backup compression ratio as the ratio of the compressed_backup_size and the backup_size columns that are written to the msdb..backupset table. This result is multiplied by 100 and cast as a decimal (5, 1) to the output the size of the compressed backup compared to the uncompressed backup.

Calculate Backup Compression in SQL Server

USE master

-- ============================================================
-- Author:      Eli Leiba
-- Create date: 12-2017
-- Description: Compute the DB backup compression ratio %
-- ============================================================
CREATE PROCEDURE usp_Calc_DB_Compression_Ratio_Pct (
   @dbName SYSNAME,
   @compressPct DECIMAL (5, 1) OUTPUT

   SET @dynaTSQL = CONCAT (
         'BACKUP DATABASE ',
         ' TO DISK = N',
         ' with compression, copy_only '

   EXEC (@dynaTSQL)

   SELECT @compressPct = cast (100.0*a.compressed_backup_size / a.backup_size AS DECIMAL (5, 1))
   FROM msdb..backupset a
   WHERE lower (a.database_name) = @dbName AND a.backup_finish_date = (
         SELECT max (backup_finish_date)
         FROM msdb..backupset


Example Use of SQL Server Backup Compression Stored Procedure

Letís calculate the database backup compression ratio of Microsoft's demonstration database - NORTHWIND.

The T-SQL code to run is as follows:

USE master

DECLARE @comppct DECIMAL (5, 1)

EXEC usp_Calc_DB_Compression_Ratio_Pct @dbname = 'Northwind',
   @compressPct = @comppct OUTPUT

PRINT @comppct

The result is:

Processed 1184 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 1186 pages in 0.312 seconds (29.674 MB/sec).

We can see above that the backup ran, but a backup file was not created. 

The value that is returned is 19.4.  This means the compressed backup would be 19.4% of the size of the uncompressed backup. This is pretty significant, the compressed backup would be more than 5 times smaller than the uncompressed backup.

Next Steps
  • You can create this simple procedure in your master database and use it to calculate the backup compression ratio of user databases and decide if compression is useful or not.
  • It is assumed that your server default database backup compression method is set to No compression.
  • The procedures was tested on SQL Server 2014, 2016 and 2017.
  • The procedure should be compatible with SQL Server 2012 and above.
  • If you want the procedure to be compatible with versions earlier than 2012 you should replace the CONCAT functions with the "+" concatenation operator.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, December 28, 2017 - 3:55:16 PM - Jun Back To Top (74548)

It does not work if database name has "."

It works after I changed it to '[' + @dbName + ']',


Thursday, December 28, 2017 - 12:11:45 PM - Gerald Back To Top (74544)


but anyone who'll give this a try should know, that taking a backup to the nul device will have a huge impact on his disksubsystem because it'll read with full throttle...



Thursday, December 28, 2017 - 11:00:24 AM - Greg Robidoux Back To Top (74540)

Hi Jonathan,

The compression ratio will be different for each database.  It all depens on how compressible the data is.

For the one example providef, this database was compressed down to 19.4% of the uncompressed backup.

This will vary for each database.  Most databases with just textual data will yield high compression.


Thursday, December 28, 2017 - 10:19:04 AM - Jonathan D. Myers Back To Top (74538)

Good morning, Eli,

I've read thru your tip and tested it in one of my dev environments. While it works, I do have a question concerning the compression ratio.

One of the assumptions you indicate at the end of your tip is that the server default is set to "no compression". Based on that assumption, what compression setting provides the result you've indicated in your example? I.E. does it provide the suggested compression setting to use for that particular database's compressed backup?

For example, you indicate a result showing a compressed backup would be 1/5th the uncompressed backup size. Does that suggest you should manually set the compression for that database's backup job to some particular value, or that adding "WITH COMPRESSION" to the backup statement will simply result in an 80% space savings over the uncompressed backup, for that particular database?

Thank you!


get free sql tips
agree to terms