SQL Server 2025 Backups - New ZSTD Compression

SQL Server 2025 Logo

ZSTD - ZStandard Logo

Compression was introduced in SQL Server version 2008. While it helped, the compression ratios were frequently too low to justify the additional compute in high-volume situations.

In SQL Server 2022, a new hardware acceleration Intel QuickAssist (Intel QAT) technology was introduced, which solves the additional compute problem. This also included a software library called QATzip, which has an API with a built-in software fallback for the hardware.

Now, in SQL Server 2025, another compression option is being introduced. It's called ZStandard or ZSTD for short. ZSTD was originally developed by Yann Collet at Facebook and is open-source.

This new ZSTD allows a DBA to balance multiple compression ratios and speed to create the optimal backup solution. Microsoft claims 30-50% better compression.

Let's run through a few tests here to see how this is working.

Test Scenario

My test scenario is a 444 GB Stack Overflow database. Included in this database is a copy of the dbo.Votes table that I partitioned. My test was run on an Intel 16-core I9 MacBook Pro with 64GB of RAM and an nVME drive.

Test 1 - Standard Backup

First, I started with a standard backup in SQL Server 2025, nothing special, and it took 38 minutes and 45 seconds. I always verify my backups, so here is the backup and the verification script below.

BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.bak'
    WITH
        NOFORMAT,
        NOINIT,
        NAME = N'StackOverflow-Full Database Backup',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        STATS = 10,
        CHECKSUM;
GO
DECLARE @backupSetId AS INT;
SELECT
    @backupSetId = position
FROM
    msdb..backupset
WHERE
    database_name = N'StackOverflow'
    AND backup_set_id =
        (
            SELECT
                MAX(backup_set_id)
            FROM
                msdb..backupset
            WHERE
                database_name = N'StackOverflow'
        );
IF @backupSetId IS NULL
    BEGIN
        RAISERROR(N'Verify failed. Backup information for database ''StackOverflow'' not found.', 16, 1);
    END;
RESTORE VERIFYONLY
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.bak'    
WITH
    FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND;
GO

Test 2 - Backup with Compression

The next backup test uses the default compression option, and this backup and verification takes 37 minutes and 20 seconds.

BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.Compression.bak'
    WITH
        NOFORMAT,
        NOINIT,
        NAME = N'StackOverflow-Full Database Backup',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        COMPRESSION,
        STATS = 10,
        CHECKSUM;
GO
DECLARE @backupSetId AS INT;
SELECT
    @backupSetId = position
FROM
    msdb..backupset
WHERE
    database_name = N'StackOverflow'
    AND backup_set_id =
        (
            SELECT
                MAX(backup_set_id)
            FROM
                msdb..backupset
            WHERE
                database_name = N'StackOverflow'
        );
IF @backupSetId IS NULL
    BEGIN
        RAISERROR(N'Verify failed. Backup information for database ''StackOverflow'' not found.', 16, 1);
    END;
RESTORE VERIFYONLY
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.Compression.bak'
    WITH
    FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND;
GO

Test 3 - Backup with ZSTD Compression

Now we will test the ZSTD backup compression at 'HIGH' compression. This backup took 1 hour and 6 minutes to complete.

BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.ZSTD.bak'
    WITH
        NOFORMAT,
        NOINIT,
        NAME = N'StackOverflow-Full Database Backup',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        COMPRESSION (ALGORITHM = ZSTD, LEVEL = HIGH),
        STATS = 10,
        CHECKSUM;
GO
DECLARE @backupSetId AS INT;
SELECT
    @backupSetId = position
FROM
    msdb..backupset
WHERE
    database_name = N'StackOverflow'
    AND backup_set_id =
        (
            SELECT
                MAX(backup_set_id)
            FROM
                msdb..backupset
            WHERE
                database_name = N'StackOverflow'
        );
IF @backupSetId IS NULL
    BEGIN
        RAISERROR(N'Verify failed. Backup information for database ''StackOverflow'' not found.', 16, 1);
    END;
RESTORE VERIFYONLY
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.ZSTD.bak'
    WITH
    FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND;
GO

Test 4 - Single Filegroup Backup

I made a copy of the dbo.Votes table and partitioned it by year. I selected the largest file group to test a backup with ZSTD. This filegroup has 23,244,895 rows. Below is a script to back up the latest/newest file group

DECLARE @FileGroupName NVARCHAR(128), @SQL NVARCHAR(MAX);

SELECT TOP 1 @FileGroupName = name FROM sys.filegroups ORDER BY data_space_id DESC;

SET @SQL = '
BACKUP DATABASE StackOverflow
FILEGROUP = ''' + @FileGroupName + '''
TO DISK = ''C:\Files\DatabaseBackups\2025\StackOverflow_' + @FileGroupName + '.bak''
WITH NOFORMAT,
        NOINIT,
        NAME = N''StackOverflow-' + @FileGroupName + ' Database Backup'',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        COMPRESSION (ALGORITHM = ZSTD, LEVEL = HIGH),
        STATS = 10,
        CHECKSUM;';

EXEC sp_executesql @SQL;

Conclusion

ZSTD High has better compression than the standard SQL Server compression, about 30GB, but it takes 30 more minutes to back up and verify. ZSTD saved 158GB for one backup, and if you take a full backup every day over two weeks, that saves over 2TB of space.

I want to test this with a larger database, possibly in the multi-terabyte range, and see how it performs, but as of right now 61% is a pretty decent space savings and worth it in my opinion.

Something odd I noticed while monitoring the ZSTD backup was that the file size started out at 150GB and stayed that way until it was about finished, when it reduced to 100 GB.

Backup Test	Type	Time	Size
Test 1	Regular Backup	00:38:45	258 GB
Test 2	Compression	00:37:20	129 GB
Test 3	ZSTD High Compression	01:06:00	100 GB
Test 4	Filegroup With ZSTD	00:00:01	182 MB