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.
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
Lets 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.
Last Update: 2017-12-28