SQL Server Stored Procedure to Calculate Database Backup Compression Ratio

By: Eli Leiba   |   Updated: 2017-12-28   |   Comments (4)   |   Related: > Compression

Problem

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.

Solution

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
GO

-- ============================================================
-- 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
   )
AS
BEGIN
   DECLARE @dynaTSQL VARCHAR(400)

   SET NOCOUNT ON
   SET @dynaTSQL = CONCAT (
         'BACKUP DATABASE ',
         @dbName,
         ' TO DISK = N',
         '''',
         'nul',
         '''',
         ' 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
         )

   SET NOCOUNT OFF
END
GO
   

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
GO

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).
19.4 

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.

About the author

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