The backup finished, but the disk still looks uncomfortably full. SQL Server 2025 adds ZSTD compression, giving you another algorithm to test against your current backups.
Start With One ZSTD Backup Command
SQL Server 2025 introduces this algorithm for database backups. You select it inside the compression option. The level controls how much compression effort the operation uses. Start with LOW, then test other levels against the same database and workload. The smallest output file does not automatically make the best operational choice.
Use an existing scratch database named BackupLab for these examples. Create the backup folder on the SQL Server computer first. The database engine service account needs permission to write there. A folder on your laptop does nothing for a remote instance. File paths belong to the server running the command.
BACKUP DATABASE [BackupLab]
TO DISK = N'C:\SqlBackups\BackupLab_ZstdLow.bak'
WITH COPY_ONLY, CHECKSUM,
COMPRESSION (ALGORITHM = ZSTD, LEVEL = LOW),
NAME = N'Compression Test Low';Keep the Comparison Honest
I use copy-only full backups for compression experiments. They leave the regular differential backup base alone. They still consume resources and storage, so choose a sensible testing window. Record the existing backup schedule before you begin. An experiment should fit around recovery operations rather than interrupt them.
Compare like with like. Keep the source database, destination disk, encryption settings, and backup options consistent. A busy database changes while separate backups run. Use a stable restored copy when you need a cleaner comparison. Also record concurrent activity. Otherwise, you end up measuring somebody else's report alongside your compression choice.
Use fresh filenames for a new experiment. These commands omit INIT and FORMAT, so existing media is not deliberately overwritten. Reusing filenames appends backup sets and complicates both file-size comparisons and restore selection. A backup folder can become an archaeological site remarkably quickly.
Give ZSTD Medium and High Their Own Files
ZSTD offers LOW, MEDIUM, and HIGH compression levels. Higher effort trades additional work for the possibility of smaller output. Data patterns determine the result. Repeated character values behave differently from already compressed or encrypted content. Treat each level as a candidate and measure its actual effect.
BACKUP DATABASE [BackupLab]
TO DISK = N'C:\SqlBackups\BackupLab_ZstdMedium.bak'
WITH COPY_ONLY, CHECKSUM,
COMPRESSION (ALGORITHM = ZSTD, LEVEL = MEDIUM),
NAME = N'Compression Test Medium';
BACKUP DATABASE [BackupLab]
TO DISK = N'C:\SqlBackups\BackupLab_ZstdHigh.bak'
WITH COPY_ONLY, CHECKSUM,
COMPRESSION (ALGORITHM = ZSTD, LEVEL = HIGH),
NAME = N'Compression Test High';Run the commands separately if you need to observe each operation. Watch processor use, disk throughput, and application response during the backup. Do not infer processor cost from file size alone. A tight overnight window and a busy daytime server create different tradeoffs, even with identical data.
Inspect Both Server Defaults
Two settings answer different questions. The compression default controls whether backups compress without an explicit instruction. The algorithm setting controls which algorithm compressed backups select when none is named. Read both before assuming your existing jobs use the older algorithm.
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN
(N'backup compression default',
N'backup compression algorithm');The documented algorithm value 1 selects MS_XPRESS, and 3 selects the new choice. My instance showed 0 there, and a plain COMPRESSION backup still recorded MS_XPRESS in its history. Setting the algorithm alone does not turn compression on for every backup. An explicit NO_COMPRESSION still requests an uncompressed backup. An explicit algorithm in the command removes doubt about the test you are running.
The following change requires ALTER SETTINGS permission. Apply it only after reviewing the instance-wide effect. SQL Server 2025 also has a documented build issue that rejects value 3 with error 15129. If your build returns that error, use the explicit BACKUP option instead. Check the SQL Server 2025 Known Issues page before deploying a default change.
EXEC sys.sp_configure N'backup compression algorithm', 3;
RECONFIGURE;
Capture the Current Default Separately
Take a comparison backup using COMPRESSION without naming an algorithm. The actual choice comes from the server setting. If that setting already selects the new algorithm, this is another backup with that algorithm. It does not establish an older-algorithm baseline. The history column settles that question.
BACKUP DATABASE [BackupLab]
TO DISK = N'C:\SqlBackups\BackupLab_Default.bak'
WITH COPY_ONLY, CHECKSUM, COMPRESSION,
NAME = N'Compression Test Default';For a deliberate older-algorithm comparison, use ALGORITHM = MS_XPRESS in a separate command and fresh file. Keep that choice explicit instead of changing the whole instance for one test. I prefer commands that explain their own behavior when someone reads them later.
Read Sizes From Backup History
The history query returns successful backup sets from your experiment. Backup_size describes the uncompressed backup amount, while compressed_backup_size records stored backup bytes. Neither value is simply the sum of current database file sizes. Read compression_algorithm to confirm what actually produced each backup.
SELECT backup_set_id, name, backup_start_date,
backup_finish_date, compression_algorithm,
backup_size, compressed_backup_size,
CAST(backup_size /
NULLIF(compressed_backup_size, 0)
AS decimal(12,2)) AS CompressionRatio,
DATEDIFF(SECOND,
backup_start_date, backup_finish_date) AS ElapsedSeconds
FROM msdb.dbo.backupset
WHERE database_name = N'BackupLab'
AND type = 'D'
AND name LIKE N'Compression Test %'
ORDER BY backup_set_id DESC;The ratio measures size reduction for that backup set. The elapsed column uses the history timestamps, which msdb keeps to the whole second, so a small test backup shows zero. It does not measure application slowdown or processor consumption. Retain each experiment's settings alongside the results. Backup history does not replace a complete test record.
Restore a ZSTD Backup Without a Compression Switch
The restore engine reads the backup format automatically. You do not specify the compression level or algorithm again. Use a compatible SQL Server restore destination. A backup from SQL Server 2025 cannot be restored onto an older SQL Server engine, regardless of compression settings.
RESTORE HEADERONLY
FROM DISK = N'C:\SqlBackups\BackupLab_ZstdLow.bak';
RESTORE VERIFYONLY
FROM DISK = N'C:\SqlBackups\BackupLab_ZstdLow.bak'
WITH FILE = 1, CHECKSUM;FILE = 1 assumes the fresh file from this example. If you reused media, inspect the header and select the correct position. VERIFYONLY checks readability and backup completeness. Complete a separate restore into an isolated database, then run integrity and application checks. Only that exercise tests the broader recovery path.
Choose for Your Recovery Window
Which level fits both your backup window and your restore target? Include the people responsible for recovery in that decision. Test the algorithms against representative data and repeat the comparison under comparable load. Preserve encryption keys when the backup uses encryption. Compression does not replace protection for backup content. Also test the copy step to your recovery location. A local backup that never reaches the recovery server cannot satisfy an offsite recovery requirement. Include available disk space during that transfer.
Adopt ZSTD after your own measurements justify the setting. Keep the previous algorithm value in the change record so you can restore it if needed. Review job commands as well as defaults. Commands that name an algorithm keep their explicit choice after the server setting changes.
Related reading on this blog: Full, Differential and Log Backups: A Practical Guide and Sample Script for Compressed and Uncompressed Backup.
Backup compression is not a recovery test, it is a storage and processing choice.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
