Compressed and Uncompressed Backup: One Script, One Switch

A compressed and uncompressed backup differ by one word in the BACKUP statement: COMPRESSION or NO_COMPRESSION. The restore does not change at all. This post puts both in one script with a switch, so a job can run either one on purpose.

Say the Word Every Time

A server has a default for backup compression. It is the setting backup compression default, and a new instance has it off. A backup that names neither option follows that default, so the same script can behave differently on two servers. Name the option in every script, for a compressed and uncompressed backup alike. An explicit word always beats the default. Read the current default with one query.

SELECT name, value_in_use FROM sys.configurations WHERE name = N'backup compression default';

The value is 0 on the test server, so a backup without a word is plain there.

Compression is available in the Standard, Enterprise and Developer editions. The demo database below holds 300,000 parcel rows. The notes repeat the same sentence, and the tracking code is a random identifier. Real data lies somewhere between those two kinds.

IF DB_ID(N'BackupModeDemo') IS NULL CREATE DATABASE BackupModeDemo;
GO
USE BackupModeDemo;
GO
DROP TABLE IF EXISTS dbo.Parcels;
CREATE TABLE dbo.Parcels (ParcelID int IDENTITY(1,1) PRIMARY KEY, Status nvarchar(40) NOT NULL, Note nvarchar(120) NOT NULL, TrackingCode uniqueidentifier NOT NULL);
INSERT INTO dbo.Parcels (Status, Note, TrackingCode)
SELECT CHOOSE(x.n % 3 + 1, N'Packed', N'On the way', N'Delivered'), N'Standard parcel handled by the regional depot, signature not required', NEWID()
FROM (SELECT TOP (300000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b) AS x;
GO
USE master;

One Script With a Switch

The script loops over the two modes so you can compare them. In a real job, you would set @Compress once and run a single backup. The file name carries the mode, so you can tell the files apart later. CHECKSUM validates the pages as they are read, and INIT overwrites an earlier backup in the same file.

Change @Folder to a folder that exists and that the SQL Server service can write to. The demo uses D:\data\. Each backup is timed with SYSDATETIME, and the sizes come from the backup history in msdb.

DECLARE @Database sysname = N'BackupModeDemo';
DECLARE @Folder nvarchar(260) = N'D:\data\';
DECLARE @Compress bit = 1;
DECLARE @File nvarchar(400), @Start datetime2(3);
CREATE TABLE #Runs (Mode nvarchar(12), BackupFile nvarchar(400), BackupMs int);
WHILE @Compress IS NOT NULL
BEGIN
    SET @File = @Folder + @Database + CASE WHEN @Compress = 1 THEN N'_compressed.bak' ELSE N'_plain.bak' END;
    SET @Start = SYSDATETIME();
    IF @Compress = 1
        BACKUP DATABASE @Database TO DISK = @File WITH COMPRESSION, CHECKSUM, INIT;
    ELSE
        BACKUP DATABASE @Database TO DISK = @File WITH NO_COMPRESSION, CHECKSUM, INIT;
    INSERT INTO #Runs VALUES (CASE WHEN @Compress = 1 THEN N'Compressed' ELSE N'Plain' END, @File, DATEDIFF(MILLISECOND, @Start, SYSDATETIME()));
    SET @Compress = CASE WHEN @Compress = 1 THEN 0 ELSE NULL END;
END;
SELECT r.Mode, CAST(s.backup_size / 1048576.0 AS decimal(9, 1)) AS DataMB, CAST(s.compressed_backup_size / 1048576.0 AS decimal(9, 1)) AS FileMB, r.BackupMs
FROM #Runs AS r
CROSS APPLY (SELECT TOP (1) bs.backup_size, bs.compressed_backup_size
             FROM msdb.dbo.backupmediafamily AS mf
             INNER JOIN msdb.dbo.backupset AS bs ON bs.media_set_id = mf.media_set_id
             WHERE mf.physical_device_name = r.BackupFile
             ORDER BY bs.backup_set_id DESC) AS s
ORDER BY r.Mode DESC;
DROP TABLE #Runs;

DataMB is what the backup read. FileMB is what it wrote. The plain file holds all 61.1 MB, and the compressed file holds 8.5 MB, about one seventh. The times are one run on a fast disk. Across four runs neither mode won every time. On this small database the times did not separate the two modes. The size ratio did not move.

Restore Without a Switch

A restore has no compression option. SQL Server reads the file header and does the right thing for either file. The next script restores each file under a new name with MOVE, so the original database stays untouched. The two RESTORE statements are the same text, with only the file name changing.

DECLARE @Folder nvarchar(260) = N'D:\data\';
DECLARE @Compress bit = 1;
DECLARE @File nvarchar(400), @Copy sysname, @Start datetime2(3), @Data nvarchar(400), @Log nvarchar(400);
CREATE TABLE #Restores (Mode nvarchar(12), RestoreMs int);
WHILE @Compress IS NOT NULL
BEGIN
    SET @File = @Folder + N'BackupModeDemo' + CASE WHEN @Compress = 1 THEN N'_compressed.bak' ELSE N'_plain.bak' END;
    SET @Copy = CASE WHEN @Compress = 1 THEN N'BackupModeCopyCompressed' ELSE N'BackupModeCopyPlain' END;
    SET @Data = @Folder + @Copy + N'.mdf';
    SET @Log = @Folder + @Copy + N'_log.ldf';
    SET @Start = SYSDATETIME();
    RESTORE DATABASE @Copy FROM DISK = @File WITH MOVE N'BackupModeDemo' TO @Data, MOVE N'BackupModeDemo_log' TO @Log, REPLACE;
    INSERT INTO #Restores VALUES (CASE WHEN @Compress = 1 THEN N'Compressed' ELSE N'Plain' END, DATEDIFF(MILLISECOND, @Start, SYSDATETIME()));
    SET @Compress = CASE WHEN @Compress = 1 THEN 0 ELSE NULL END;
END;
SELECT Mode, RestoreMs FROM #Restores ORDER BY Mode DESC;
DROP TABLE #Restores;
SELECT name, state_desc FROM sys.databases WHERE name LIKE N'BackupModeCopy%' ORDER BY name;

Both copies come back online. In this run the plain file restored faster. In the other runs the order flipped. A compressed backup reads less from disk, so it can look faster to restore. In these runs neither mode won every time. Reading less from disk helps only when the disk is the slow part.

Which File Is Which

Never rely on the file name alone. The backup history in msdb records both sizes, and a compressed backup has a smaller file size than data size. RESTORE HEADERONLY also reports it in a column named Compressed. The post SQL Server Backup Compression: How Much Space It Saves studies the size ratios. It also covers the error from appending a compressed backup to a plain file.

When to Choose NO_COMPRESSION

Choose the uncompressed backup when the server has no CPU to spare during the backup window. Choose it for data that is already compressed or encrypted, where compression saves little and still costs CPU. A database with Transparent Data Encryption compresses well only when the backup uses a transfer size above 64 KB. When in doubt, run the script on a copy and compare the two sizes.

You could argue that a switch is overkill and you should pick one mode for the whole server. For most servers that is right, and the default setting does it. The switch earns its place on the few databases that behave differently, such as one full of images.

What to Remember

Name COMPRESSION or NO_COMPRESSION in every backup script. A compressed and uncompressed backup then never get mixed up, and the mode in the file name helps. Restore with the same statement for both. Test both modes on your own data, because the ratio depends on it. When you finish, drop the demo databases, clear their history, and delete the two backup files by their exact names.

USE master;
GO
IF DB_ID(N'BackupModeCopyCompressed') IS NOT NULL DROP DATABASE BackupModeCopyCompressed;
IF DB_ID(N'BackupModeCopyPlain') IS NOT NULL DROP DATABASE BackupModeCopyPlain;
ALTER DATABASE BackupModeDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE BackupModeDemo;
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'BackupModeDemo';
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'BackupModeCopyCompressed';
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'BackupModeCopyPlain';

SQL Server never deletes backup files for you. Remove the two files with PowerShell.

Remove-Item -LiteralPath 'D:\data\BackupModeDemo_compressed.bak'
Remove-Item -LiteralPath 'D:\data\BackupModeDemo_plain.bak'

A backup is not small because it is better, it is small because you said so in the script.

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