top of page
Search

SQL Server 2025 Backup Compression and Restore Review

SQL Server 2025 Logo
SQL Server 2025

In my previous blog post, we briefly covered the new compression being introduced in SQL Server 2025. After some feedback, I decided to cover the complete picture with my medium-sized Stack Overflow database, as the tests with 'World Wide Importers' seem small.


To give a little bit of background, compression was introduced in SQL Server version 2008.

In SQL Server 2022, a new hardware acceleration Intel QuickAssist (Intel QAT) technology was introduced, as well as a software library called QATzip, which has an API with a built-in software fallback for the hardware, but you could also just use the software if you didn't have the hardware.


Now, in SQL Server 2025, a new 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.


Test Database


My test database is a 444 GB Stack Overflow database. (It includes a copy of the dbo.Votes table that I partitioned, so it's a little larger.)


Hardware

My test was run on an Intel 16-core I9 MacBook Pro with 64GB of RAM and an nVME drive.


Test 1 - Standard Backup - No Compression


To get our baseline, let's run a backup without any compression on the database. I always verify my backups, so my timings are included for all backups.


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

Restore Script

USE [master];
ALTER DATABASE [StackOverflow]
    SET
        SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [StackOverflow]
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.bak'
    WITH
    FILE = 2,
    NOUNLOAD,
    REPLACE,
    STATS = 5;
ALTER DATABASE [StackOverflow]
    SET
        MULTI_USER;
GO

Test 2 - Backup MS_XPRESS - Low


The next backup test uses the MS_XPRESS with the Low compression option.

BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.Compression.Low.bak'
    WITH
        NOFORMAT,
        NOINIT,
        NAME = N'StackOverflow-Full Database Backup',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        COMPRESSION (ALGORITHM = MS_XPRESS, LEVEL = LOW),
        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.Low.bak'
    WITH
    FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND;
GO

Restore Script

USE [master];
ALTER DATABASE [StackOverflow]
    SET
        SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [StackOverflow]
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.Compression.Low.bak'
    WITH
    NOUNLOAD,
    REPLACE,
    STATS = 5;
ALTER DATABASE [StackOverflow]
    SET
        MULTI_USER;
GO

Test 3 - Backup MS_XPRESS - Medium


The next backup test uses the MS_XPRESS with the Medium compression option.

BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.Compression.Medium.bak'
    WITH
        NOFORMAT,
        NOINIT,
        NAME = N'StackOverflow-Full Database Backup',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        COMPRESSION (ALGORITHM = MS_XPRESS, LEVEL = MEDIUM),
        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.Medium.bak'
    WITH
    FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND;
GO

Restore Script

USE [master];
ALTER DATABASE [StackOverflow]
    SET
        SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [StackOverflow]
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.Compression.Medium.bak'
    WITH
    NOUNLOAD,
    REPLACE,
    STATS = 5;
ALTER DATABASE [StackOverflow]
    SET
        MULTI_USER;
GO

Test 4 - Backup MS_XPRESS - High


The next backup test uses the MS_XPRESS with the High compression option.

BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.Compression.High.bak'
    WITH
        NOFORMAT,
        NOINIT,
        NAME = N'StackOverflow-Full Database Backup',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        COMPRESSION (ALGORITHM = MS_XPRESS, 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.Compression.High.bak'
    WITH
    FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND;
GO

Restore Script

USE [master];
ALTER DATABASE [StackOverflow]
    SET
        SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [StackOverflow]
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.Compression.High.bak'
    WITH
    NOUNLOAD,
    REPLACE,
    STATS = 5;
ALTER DATABASE [StackOverflow]
    SET
        MULTI_USER;
GO

Test 5 - Backup ZSTD - Low


The next backup test uses the ZSTD with the Low compression option.

BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.ZSTD.Low.bak'
    WITH
        NOFORMAT,
        NOINIT,
        NAME = N'StackOverflow-Full Database Backup',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        COMPRESSION (ALGORITHM = ZSTD, LEVEL = LOW),
        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.Low.bak'
    WITH
    FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND;
GO

Restore Script

USE [master];
ALTER DATABASE [StackOverflow]
    SET
        SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [StackOverflow]
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.ZSTD.Low.bak'
    WITH
    NOUNLOAD,
    REPLACE,
    STATS = 5;
ALTER DATABASE [StackOverflow]
    SET
        MULTI_USER;
GO

Test 6 - Backup ZSTD - Medium


The next backup test uses the ZSTD with the Medium compression option.


BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.ZSTD.Medium.bak'
    WITH
        NOFORMAT,
        NOINIT,
        NAME = N'StackOverflow-Full Database Backup',
        SKIP,
        NOREWIND,
        NOUNLOAD,
        COMPRESSION (ALGORITHM = ZSTD, LEVEL = MEDIUM),
        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.Low.bak'
    WITH
    FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND;
GO

Restore Script


USE [master];
ALTER DATABASE [StackOverflow]
    SET
        SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [StackOverflow]
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.ZSTD.Medium.bak'
    WITH
    NOUNLOAD,
    REPLACE,
    STATS = 5;
ALTER DATABASE [StackOverflow]
    SET
        MULTI_USER;
GO

Test 7 - Backup ZSTD - High


The next backup test uses the ZSTD with the High compression option.

BACKUP DATABASE [StackOverflow]
    TO
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.ZSTD.High.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.High.bak'
    WITH
    FILE = @backupSetId,
    NOUNLOAD,
    NOREWIND;
GO

Restore Script

USE [master];
ALTER DATABASE [StackOverflow]
    SET
        SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [StackOverflow]
    FROM
        DISK = N'C:\DatabaseBackups\2025\StackOverflow2025.ZSTD.High.bak'
    WITH
    NOUNLOAD,
    REPLACE,
    STATS = 5;
ALTER DATABASE [StackOverflow]
    SET
        MULTI_USER;
GO

Results


Here is a combined list of the tests, their times, sizes, space savings and restore time.

Test

Type

Backup Time

Size GB

%  Savings

Restore Time

Test 1

Regular Backup

00:38:45

258


00:08:22

Test 2

MS_XPRESS - Low

00:27:28

128

50.39

00:09:21

Test 3

MS_XPRESS - Medium

00:25:13

121

53.10

00:07:00

Test 4

MS_XPRESS - High

00:30:18

121

53.10

00:07:01

Test 5

ZSTD Low

00:16:15

129

50.00

00:05:22

Test 6

ZSTD Medium

00:28:28

106

58.91

00:06:26

Test 7

ZSTD High

01:06:00

100

61.24

00:06:30

Conclusion


The top two were tests 5 with ZSTD Low (Gold) and 6 with ZSTD Medium (Silver). Test 6 saved over 50% space, with about 10 minutes saved from the regular backup.


But I think the clear Gold Medal winner here is ZSTD Low, which performed the backup in less than half the time, at just over 16 minutes, with 50% of the regular backup. The restore time is also the fastest.


Going for the highest level of compression may not always be the best. As you can see in test 7, it took over an hour and only increased by 11.24% from test 5.


Keep in mind that your database may compress differently, so be sure to test multiple compression levels and compare sizes and times.


I hope to test this with a larger database soon, but until then, if you have used this compression on a larger database, please comment below. I'd love to hear your experience.



 
 
 

Comments

©2021-2025 by Jeff Taylor

bottom of page