SQL Server Backup Compression: Enable and Verify

calendar Apr 17, 2026 / · 7 min read · sql server backup recovery backup compression sp_configure msdb sql scripts database administration maintenance scripts  ·
Share on: linkedin copy

Backup compression reduces the on-disk size of SQL Server backup files and can significantly shorten backup duration by reducing the amount of data written to disk. This post shows how to enable compression at the server level, verify the setting is active, and confirm compression ratios from backup history using the msdb catalog.

Purpose and Overview

SQL Server backup compression has been available since SQL Server 2008, but it was initially limited to Enterprise and Developer editions. From SQL Server 2016 SP1 onward it is available in all editions including Standard and Express. Despite this, many instances still run with compression disabled because the default setting is off.

Enabling backup compression reduces backup file sizes by 60–80% in typical OLTP workloads, cuts backup duration by reducing IO, and lowers storage and network transfer costs when backups are replicated offsite. There is a CPU cost, but on most modern servers the IO savings outweigh it, making net backup time shorter even with the extra CPU work.

This post covers three scripts:

  1. Enable backup compression as the server default using sp_configure
  2. Verify the current setting and which backups ran with compression active
  3. Report compression ratios from backup history using msdb.dbo.backupset

Code Breakdown

Script 1: Enable Backup Compression at the Server Level

 1-- Enable backup compression as the server default
 2EXEC sys.sp_configure N'backup compression default', 1;
 3RECONFIGURE WITH OVERRIDE;
 4GO
 5
 6-- Confirm the new value
 7SELECT
 8    name,
 9    value,
10    value_in_use,
11    description
12FROM sys.configurations
13WHERE name = N'backup compression default';

sp_configure sets server-wide options. The option name backup compression default controls whether new backup operations use compression unless the individual BACKUP statement explicitly overrides it with COMPRESSION or NO_COMPRESSION. Setting the value to 1 turns compression on; 0 turns it off.

RECONFIGURE WITH OVERRIDE applies the change immediately. Unlike some sp_configure options, backup compression default is dynamic and takes effect for the next backup without a SQL Server restart.

The confirmation SELECT reads from sys.configurations, which exposes every sp_configure option. The value column shows the configured value saved to disk; value_in_use shows the value currently active in the running instance. Immediately after RECONFIGURE, these two columns should match.

Script 2: Verify Backup Compression Setting and Recent Backup Behavior

 1-- Check whether compression is enabled at the server level
 2SELECT
 3    name,
 4    value_in_use                    AS compression_enabled,
 5    CASE value_in_use
 6        WHEN 1 THEN 'Compression ON (default)'
 7        WHEN 0 THEN 'Compression OFF (default)'
 8    END                             AS status
 9FROM sys.configurations
10WHERE name = N'backup compression default';
11GO
12
13-- Confirm recent full backups ran with compression active
14SELECT TOP 20
15    database_name,
16    backup_start_date,
17    backup_finish_date,
18    CASE type
19        WHEN 'D' THEN 'Full'
20        WHEN 'I' THEN 'Differential'
21        WHEN 'L' THEN 'Log'
22        ELSE type
23    END                                                     AS backup_type,
24    CAST(backup_size / 1024000.0 AS DECIMAL(18, 2))        AS uncompressed_mb,
25    CAST(compressed_backup_size / 1024000.0 AS DECIMAL(18, 2)) AS compressed_mb,
26    compress_backup_size            -- 1 = compressed; NULL/0 = not compressed
27FROM msdb.dbo.backupset
28WHERE database_name NOT IN ('tempdb')
29    AND backup_finish_date >= DATEADD(DAY, -7, GETDATE())
30ORDER BY backup_finish_date DESC;

The first query is a simple verification step: read sys.configurations and decode the numeric value into a plain-English label. Run this immediately after enabling compression to confirm the change applied.

The second query looks at the last seven days of backup history in msdb.dbo.backupset. The compressed_backup_size column holds the actual byte count written to the backup file. When compression is active, this value is smaller than backup_size (the logical uncompressed data size). If compressed_backup_size equals backup_size, that backup ran without compression. If it is NULL, the version of SQL Server or the edition in use did not support compression at the time of the backup.

Script 3: Compression Ratio Report From Backup History

 1SELECT
 2    @@SERVERNAME                                                        AS server_name,
 3    bs.database_name,
 4    bs.backup_start_date,
 5    bs.backup_finish_date,
 6    DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date)      AS duration_minutes,
 7    CAST(bs.backup_size / 1024000.0 AS DECIMAL(18, 2))                 AS uncompressed_mb,
 8    CAST(bs.compressed_backup_size / 1024000.0 AS DECIMAL(18, 2))      AS compressed_mb,
 9    CAST(
10        (1.0 - (bs.compressed_backup_size * 1.0 / NULLIF(bs.backup_size, 0))) * 100
11        AS DECIMAL(5, 1)
12    )                                                                   AS compression_pct_saved,
13    bmf.physical_device_name
14FROM msdb.dbo.backupset AS bs
15LEFT JOIN msdb.dbo.backupmediafamily AS bmf
16    ON bs.media_set_id = bmf.media_set_id
17WHERE bs.type = 'D'
18    AND bs.database_name NOT IN ('tempdb')
19    AND bs.backup_finish_date >= DATEADD(DAY, -30, GETDATE())
20    AND bmf.physical_device_name NOT LIKE '{%'
21ORDER BY bs.database_name, bs.backup_finish_date DESC
22OPTION (RECOMPILE);

This script joins msdb.dbo.backupset to msdb.dbo.backupmediafamily and adds a compression ratio calculation. The ratio formula (1 - compressed / uncompressed) * 100 gives the percentage of space saved. A result of 65 means the backup file is 65% smaller than the raw data size. NULLIF(bs.backup_size, 0) guards against division by zero if any row has a zero uncompressed size.

Filtering to bs.type = 'D' limits output to full backups, where the size difference is easiest to evaluate. Remove or change this filter to include differentials (I) or log backups (L).

Key Benefits and Use Cases

  • Reduces backup file sizes by 60–80% for typical OLTP databases, cutting storage costs
  • Shortens backup window duration by reducing the volume of data written to disk
  • Available on all SQL Server editions from 2016 SP1 onward — no Enterprise license required
  • Compression can be enabled or disabled per individual BACKUP statement, overriding the server default
  • Historical compression ratios from msdb help justify the setting change to management
  • Useful for auditing whether all instances in an environment have compression enabled

Performance Considerations

  • CPU overhead: Backup compression consumes more CPU during the backup operation. On servers where CPU is consistently near capacity during backup windows, test before enabling globally. Most servers have spare CPU capacity during overnight maintenance windows.
  • Resource Governor: On SQL Server Enterprise edition, Resource Governor can cap the CPU percentage backup operations consume, balancing compression gains against CPU impact on concurrent workloads.
  • Encryption interaction: When backup encryption is enabled (SQL Server 2014+), compression should be applied before encryption. Encrypting first, then compressing, produces little size reduction because encrypted data is not compressible.
  • compressed_backup_size availability: This column exists in msdb.dbo.backupset on SQL Server 2008 and later, but is populated only when compression was used. Rows for uncompressed backups may show NULL or a value equal to backup_size depending on the version.
  • VDI/third-party backup tools: Some third-party backup agents apply their own compression outside of SQL Server's native mechanism. In those cases, the native sp_configure setting has no effect. Check your vendor documentation.

Practical Tips

To take a single compressed backup immediately without changing the server default:

1BACKUP DATABASE AdventureWorks2019
2TO DISK = N'C:\Backups\AdventureWorks2019_compressed.bak'
3WITH COMPRESSION, STATS = 10;

To take an uncompressed backup when the server default is ON:

1BACKUP DATABASE AdventureWorks2019
2TO DISK = N'C:\Backups\AdventureWorks2019_nocomp.bak'
3WITH NO_COMPRESSION, STATS = 10;

To check compression status across multiple instances from a Central Management Server, run the sys.configurations query against all registered servers using the CMS multi-server query feature in SSMS. Add @@SERVERNAME to the SELECT list to identify which instance each row comes from.

Schedule the compression ratio report as a SQL Server Agent job that writes results to a DBA monitoring table. Tracking ratios over time reveals databases where data compressibility changes — for example, if a table column type changes from VARCHAR to VARBINARY storing pre-compressed blobs.

Conclusion

Enabling SQL Server backup compression is one of the highest-return configuration changes available to a DBA: it requires a single sp_configure call, takes effect immediately, and reduces both backup file size and backup duration with no schema or application changes. The verification and ratio-reporting scripts in this post confirm the setting is active and let you document the space savings before and after the change.

References

Posts in this series