SQL Server Backup Compression: How Much Space It Saves

Backup compression in SQL Server needs one word: add COMPRESSION to the BACKUP statement. The file gets much smaller, the copy to another server goes faster, and the restore works as before. The test below measures the size, the time and the one error that surprises people.

Build a Demo Database

The test needs data that looks like real business data. The script creates a database named BackupCompressDemo and fills a table with 300,000 shipment rows. The notes are repeated phrases, and the tracking value is a random GUID that doesn’t compress well. GENERATE_SERIES needs SQL Server 2022 and compatibility level 160. Older versions can use any numbers table.

IF DB_ID(N'BackupCompressDemo') IS NULL CREATE DATABASE BackupCompressDemo;
GO
ALTER DATABASE BackupCompressDemo SET RECOVERY SIMPLE;
GO
USE BackupCompressDemo;
GO
DROP TABLE IF EXISTS dbo.ShipmentLog;
CREATE TABLE dbo.ShipmentLog (ShipmentID int NOT NULL PRIMARY KEY, Notes char(120) NOT NULL, Tracking char(36) NOT NULL);
INSERT dbo.ShipmentLog (ShipmentID, Notes, Tracking)
SELECT value,
       CONCAT(N'Garden center shipment, ', CHOOSE(value % 4 + 1, N'seed packets', N'clay pots', N'potting soil', N'watering cans'), N', dock ', value % 12 + 1),
       CONVERT(char(36), NEWID())
FROM GENERATE_SERIES(1, 300000);

Take a Plain and a Compressed Backup

The next script runs two backups of the same database. The first one says NO_COMPRESSION. The second one says COMPRESSION. That’s the only difference. A file name without a folder goes to the instance’s default backup folder. INIT overwrites any earlier backup in the same file but keeps the file’s media header. CHECKSUM makes SQL Server validate the pages as it reads them.

USE master;
GO
BACKUP DATABASE BackupCompressDemo TO DISK = N'BackupCompressDemo_plain.bak' WITH INIT, NO_COMPRESSION, CHECKSUM;
BACKUP DATABASE BackupCompressDemo TO DISK = N'BackupCompressDemo_compressed.bak' WITH INIT, COMPRESSION, CHECKSUM;

SQL Server keeps a record of every backup in msdb. The query below reads it and compares the two. backup_size is the amount of data the backup read. compressed_backup_size is what was written to the file. For a plain backup, the two are equal.

SELECT CASE WHEN bs.compressed_backup_size < bs.backup_size THEN N'Compressed' ELSE N'Plain' END AS BackupKind,
       CAST(bs.backup_size / 1048576.0 AS decimal(10,1)) AS DataMB,
       CAST(bs.compressed_backup_size / 1048576.0 AS decimal(10,1)) AS StoredMB,
       CAST(1.0 * bs.backup_size / bs.compressed_backup_size AS decimal(10,2)) AS Ratio
FROM msdb.dbo.backupset AS bs
WHERE bs.database_name = N'BackupCompressDemo'
ORDER BY bs.backup_set_id;

The plain backup stored all 55.1 MB. The compressed backup stored 11.2 MB, a ratio of 4.93 to 1. That’s about 80 percent less space for the same data. The ratio is not a promise. It depends on how repetitive your data is.

Check the Restore

A compressed backup restores with the ordinary RESTORE statement. There’s no option to add. SQL Server reads the header and handles the compression itself. RESTORE VERIFYONLY confirms that the file is readable. RESTORE HEADERONLY has a Compressed column, and it shows 1 for the second backup. Only a real restore proves the data comes back, so test one.

RESTORE VERIFYONLY FROM DISK = N'BackupCompressDemo_compressed.bak' WITH CHECKSUM;

SQL Server answers that the backup set on file 1 is valid. Restoring a compressed file works like restoring a plain one.

Why Old Scripts Used FORMAT

You will meet WITH FORMAT in scripts that switch between compressed and plain files. The error below is the reason. A backup file is a media set, and the first backup written to it decides whether it holds compressed data. Appending a compressed backup to a plain file fails.

BACKUP DATABASE BackupCompressDemo TO DISK = N'BackupCompressDemo_plain.bak' WITH COMPRESSION, CHECKSUM;

The Messages pane cuts the line after its first sentence. The rest of the message offers two ways out. Append without compression, by omitting COMPRESSION or writing NO_COMPRESSION.

The other way out is to start a new media set with WITH FORMAT. That overwrites all of its backup sets. FORMAT writes a new media header and doesn’t format a disk, as the name suggests. A new file name is the safer fix. That advice is mine, not the message’s. To reuse the same file on purpose, you need FORMAT. INIT alone keeps the old media header. A compressed backup with INIT into a plain file therefore fails with the same error.

Make Compression the Default

The instance has a setting named backup compression default. On this instance its value is 0, so a backup that doesn’t say COMPRESSION is plain. Run sp_configure with that name and the value 1, then run RECONFIGURE. That turns compression on for every backup that doesn’t say otherwise. It’s a server-level change, so test it first and tell your team.

What Compression Costs

Compression uses CPU time. On this small database, the compressed backup took longer: 0.162 seconds against 0.068. The database is small and the disk is fast, so the saving in writes doesn’t help. On a large database or a slow backup disk, writing about five times less data can pay for the CPU.

Compression doesn’t help every database. Not every edition can create compressed backups either, so check the feature list for your edition first. Data that is already compressed or encrypted shrinks little. A database with Transparent Data Encryption compresses well only when the backup uses a transfer size above 64 KB. Test with your own data, because the ratio here depends on the demo’s repeated text.

You could argue that disk space is cheap and compression isn’t needed. Space isn’t the only cost. A smaller file also copies faster to another server and restores from a shorter transfer. I turn it on unless a test shows no gain.

What to Remember

Backup compression costs one word and saved most of the file size in this test. Compare backup_size with compressed_backup_size to see your real ratio. Don’t mix compressed and plain backups in one file, and don’t use FORMAT unless you mean to overwrite it.

When you finish, run the cleanup script. SQL Server doesn’t delete backup files. Remove the two .bak files from your default backup folder yourself.

USE master;
GO
SELECT SERVERPROPERTY('InstanceDefaultBackupPath') AS BackupFolder;
GO
IF DB_ID(N'BackupCompressDemo') IS NOT NULL
BEGIN
    ALTER DATABASE BackupCompressDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE BackupCompressDemo;
END;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'BackupCompressDemo';

A backup is not smaller because it is better, it is smaller because you asked SQL Server to compress it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.