SQL Server Backup Compression: Benefits, CPU Cost, ZSTD and Enabling It by Default

Backup compression is one of the few SQL Server settings that usually makes things smaller and faster at the same time. Yet many servers still take uncompressed backups, simply because the instance-level default was never changed. This guide explains how backup compression works, what it costs, which algorithms are available in newer versions, and how to enable it as the default for every backup.

How Backup Compression Works and Why It’s Usually Faster

With compression enabled, SQL Server compresses the data as it writes the backup. Because a compressed backup is smaller, it needs less I/O on the backup device, and Microsoft notes that this usually increases backup speed significantly. The same is true in reverse: a restore reads less data from the backup device. The benefits are:

• Less storage: backup files are often a fraction of the database size, so you can keep more backups for the same disk space.
• Faster backups and restores: on most servers the backup is limited by I/O, not CPU, so writing less data takes less time. A faster restore directly shortens your RTO.
• Less network traffic: smaller files travel faster to a backup server, another data center or cloud storage.

Which Editions Support Backup Compression?

Creating compressed backups is supported in the Enterprise, Standard and Developer editions. Every edition from SQL Server 2008 onward can restore a compressed backup, so you can, for example, restore a compressed production backup on an Express instance. Two restrictions to know: compressed and uncompressed backups can’t be mixed in the same media set, and older SQL Server versions can’t read compressed backups.

The Cost: CPU

Compression isn’t free. Microsoft notes that compression significantly increases CPU usage by default, and that extra CPU can affect other work running at the same time. In practice:

• Full and differential backups usually run in a quiet window, where spare CPU is available.
• Frequent log backups during the business day are small, so their CPU cost is usually modest, but check it on CPU-bound servers.
• If CPU is tight, you can run compressed backups in a Resource Governor workload group with a CPU limit, at the cost of a longer backup.
• From SQL Server 2022, compression can be offloaded to Intel QuickAssist Technology (QAT) hardware through integrated acceleration (the QAT_DEFLATE algorithm), which frees up the server’s CPUs.

Compression Algorithms: MS_XPRESS, QAT_DEFLATE and ZSTD

On SQL Server 2025 you can choose ZSTD for a single backup, or make it the default algorithm for every compressed backup:

-- One backup with ZSTD (SQL Server 2025)
BACKUP DATABASE [SalesDB]
TO DISK = N'\\backupsrv\sql\SalesDB\SalesDB_FULL.bak'
WITH COMPRESSION (ALGORITHM = ZSTD), CHECKSUM;

-- Make ZSTD the default algorithm for WITH COMPRESSION backups
EXEC sys.sp_configure N'backup compression algorithm', 3;
RECONFIGURE;

What Affects the Compression Ratio?

How much smaller a backup gets depends on the data:

• Data type: character data compresses better than most other data.
• Repetition: pages where many rows share the same values compress well; random data, or one large row per page, barely compresses.
• Encryption: encrypted data, for example columns protected with Always Encrypted or application-level encryption, compresses very little.
• Data compression: if tables already use row or page compression, the backup won’t shrink much more.
• TDE: from SQL Server 2016, an optimized algorithm decrypts, compresses and re-encrypts pages when MAXTRANSFERSIZE is larger than 64 KB. From SQL Server 2019 CU5, SQL Server raises MAXTRANSFERSIZE automatically for compressed backups, so TDE databases compress well without extra settings.

You can measure the actual ratio from the backup history:

SELECT TOP (20)
       database_name,
       CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' END AS backup_type,
       CAST(backup_size / 1048576.0 AS decimal(18, 1)) AS backup_mb,
       CAST(compressed_backup_size / 1048576.0 AS decimal(18, 1)) AS compressed_mb,
       CAST(backup_size / NULLIF(compressed_backup_size, 0) AS decimal(9, 2)) AS ratio,
       DATEDIFF(SECOND, backup_start_date, backup_finish_date) AS duration_sec
FROM msdb.dbo.backupset
ORDER BY backup_finish_date DESC;

A ratio of 3.00 means the backup is a third of its original size, roughly 66% saved. A ratio near 1.00 means compression isn’t helping that database.

Enable Backup Compression by Default

The server option backup compression default decides whether backups are compressed when the BACKUP command doesn’t say. It’s off on a new installation. Turning it on means every backup (jobs, maintenance plans, third-party tools and one-off backups from SSMS) is compressed unless it explicitly asks not to be:

-- Check the current setting (1 = compressed by default)
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN (N'backup compression default', N'backup compression algorithm');

-- Enable compression for all backups by default
EXEC sys.sp_configure N'backup compression default', 1;
RECONFIGURE;

In SSMS, the same setting is under Server Properties → Database Settings → Compress backup. An individual backup can still override the default with WITH COMPRESSION or WITH NO_COMPRESSION.

When Not to Compress

Compression is the right default for most servers, but there are exceptions:

• CPU-bound servers where backups have to run during peak hours and there’s no spare CPU; consider Resource Governor or QAT first.
• Data that doesn’t compress: encrypted or already compressed data where the measured ratio stays near 1.
• Deduplicating backup appliances: some storage vendors recommend sending uncompressed backups so their own deduplication works better. Follow your vendor’s guidance.

For how compression fits into the rest of a backup strategy, see How to Build a SQL Server Backup Plan and SQL Server Backup Types Explained.

Frequently Asked Questions