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.

Last verified: 19 October 2026. Checked against Microsoft Learn (backup compression, the two server configuration options, BACKUP, integrated acceleration and the SQL Server 2025 known issues). T-SQL tested on SQL Server 2025 (build 17.0.4055.5). Known issues change with updates, so check the Microsoft page for your cumulative update.

Should You Compress? Quick Decision Table

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. Microsoft also documents a server-wide default for it (backup compression algorithm = 3), but its SQL Server 2025 known-issues page lists an error when you set that value:

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

-- Server-wide ZSTD default (value 3). Works on SQL Server 2025 RTM (17.0.4055.5);
-- Microsoft's known-issues page lists Msg 15129 on some builds, so confirm on a test
-- instance first, or specify ZSTD per BACKUP as above.
-- 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.

Version and Edition Notes

Official Behavior, Aryasoft Field Notes and Our Recommendation

Frequently Asked Questions

Sources (Microsoft Learn)

Technical claims in this guide were checked against these primary sources on 19 October 2026. Microsoft can change documentation at any time, so re-check version-specific details before a production change.

• Backup compression (SQL Server)
• Server configuration: backup compression default
• Server configuration: backup compression algorithm
• BACKUP (Transact-SQL)
• Integrated acceleration and offloading
• SQL Server 2025 known issues
• Editions and supported features of SQL Server 2025