Quick Scan Report – Not Using Compressed Backups

What this check looks for

Recent backups in msdb.dbo.backupset where the compressed size equals the uncompressed size, meaning compression was not used. The message names the databases. The check is skipped on Amazon RDS.

Why it matters

Backup compression is close to a free win, and it is off by default.

Typical compression ratios are three to one or four to one on ordinary relational data, which means:

• The backup file is a quarter of the size. Less disk, less retention cost, more generations kept in the same space.
• The backup runs faster. This surprises people, because compression costs CPU. It is faster because backup is almost always bound by how fast bytes can be written to the destination, and writing a quarter as many bytes finishes in a quarter of the time. The CPU spent compressing is cheaper than the I/O saved.
• The restore is faster too, for the same reason, and that is the one that matters at three in the morning.
• Network copies are smaller, which matters when backups go to a UNC path or get shipped offsite.
• Log shipping and differentials shrink as well, since compression applies to every backup type.

The cost is CPU, and it is real but usually modest. A compressed backup uses noticeably more CPU while it runs. On an instance that is already CPU bound during the backup window, that is worth measuring; on most instances the backup window is chosen precisely because the server is quiet.

Where availability comes in:

• SQL Server 2008: Enterprise Edition only.
• SQL Server 2008 R2 and later: Standard Edition and above.
• Web Edition: not available.
• Express Edition: not available.

So on any currently supported version and a Standard or Enterprise edition, it is available and almost certainly should be on.

Two cases where compression does little or nothing, worth knowing before you expect a ratio:

• A TDE encrypted database. Encrypted data does not compress. From SQL Server 2016 you can combine them usefully by specifying MAXTRANSFERSIZE above 65536, which enables a different code path; without that, compressing a TDE backup can produce a file no smaller and occasionally larger.
• A database that is mostly already compressed data, such as heavy use of page compression, column store, or large binary content that is already compressed. The ratio will be much closer to one to one.

And one to be careful about: compressed and uncompressed backups cannot share the same media set. Appending a compressed backup to an existing uncompressed media set fails. That only matters if you append to media sets rather than writing a new file each time.

How to confirm it yourself

The setting:

SELECT [name], [value], [value_in_use], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'backup compression default';

What your recent backups actually did, and what compression would be worth:

SELECT TOP (100)
       bs.[database_name],
       bs.[type],
       bs.[backup_start_date],
       CAST(bs.[backup_size]            / 1048576.0 AS DECIMAL(12,1)) AS [uncompressed_mb],
       CAST(bs.[compressed_backup_size] / 1048576.0 AS DECIMAL(12,1)) AS [on_disk_mb],
       CAST(bs.[backup_size] * 1.0
            / NULLIF(bs.[compressed_backup_size], 0) AS DECIMAL(6,2))  AS [ratio],
       DATEDIFF(SECOND, bs.[backup_start_date], bs.[backup_finish_date]) AS [seconds]
  FROM msdb.dbo.backupset AS bs
 WHERE bs.[backup_start_date] > DATEADD(DAY, -14, GETDATE())
 ORDER BY bs.[backup_start_date] DESC;

A ratio of 1.00 means no compression was used. type is D for full, I for differential and L for log.

Per database, with the throughput, which is the number that shows the speed benefit:

SELECT bs.[database_name],
       bs.[type],
       COUNT(*)                                                       AS [backups],
       AVG(CAST(bs.[backup_size] / 1048576.0 AS DECIMAL(12,1)))       AS [avg_uncompressed_mb],
       AVG(CAST(bs.[compressed_backup_size] / 1048576.0 AS DECIMAL(12,1))) AS [avg_on_disk_mb],
       AVG(CAST(bs.[backup_size] * 1.0
                / NULLIF(bs.[compressed_backup_size], 0) AS DECIMAL(6,2)))  AS [avg_ratio],
       AVG(DATEDIFF(SECOND, bs.[backup_start_date], bs.[backup_finish_date])) AS [avg_seconds]
  FROM msdb.dbo.backupset AS bs
 WHERE bs.[backup_start_date] > DATEADD(DAY, -30, GETDATE())
 GROUP BY bs.[database_name], bs.[type]
 ORDER BY [avg_uncompressed_mb] DESC;

Whether the edition supports it:

SELECT SERVERPROPERTY('Edition')        AS [edition],
       SERVERPROPERTY('EngineEdition')  AS [engine_edition],
       SERVERPROPERTY('ProductVersion') AS [build];

And which databases are encrypted, since those are the exception:

SELECT DB_NAME([database_id]) AS [database_name], [encryption_state_desc]
  FROM sys.dm_database_encryption_keys WITH (NOLOCK);

How to fix it

Turn on the instance default, then check that nothing overrides it.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

That takes effect immediately, needs no restart, and applies to every backup that does not state otherwise.

Then check what your backup jobs actually say, because an explicit WITH NO_COMPRESSION overrides the default:

SELECT j.[name] AS [job_name], st.[step_name], st.[command]
  FROM msdb.dbo.sysjobsteps AS st
 INNER JOIN msdb.dbo.sysjobs AS j ON j.[job_id] = st.[job_id]
 WHERE st.[command] LIKE '%BACKUP%'
 ORDER BY j.[name], st.[step_id];

Look for NO_COMPRESSION and remove it. Maintenance plans have a compression setting per task that also needs checking, since the plan writes an explicit option either way.

To be explicit in your own backup statements, which is the better habit:

BACKUP DATABASE [YourDatabase]
    TO DISK = N'E:\SQLBackups\YourDatabase_full.bak'
  WITH COMPRESSION, CHECKSUM, INIT, STATS = 10;

CHECKSUM is worth adding at the same time. It verifies page checksums as the backup is read, so a corrupt page is detected while backing up rather than while restoring.

For a TDE encrypted database on SQL Server 2016 or later, set the transfer size so compression can work:

BACKUP DATABASE [EncryptedDatabase]
    TO DISK = N'E:\SQLBackups\EncryptedDatabase_full.bak'
  WITH COMPRESSION, CHECKSUM, MAXTRANSFERSIZE = 2097152, INIT;

Watch the CPU during the first compressed run, and if the backup window is genuinely CPU constrained, consider capping it. MAXDOP does not apply to backup, but Resource Governor can limit it on Enterprise Edition. In most cases this is not needed.

And measure the result. Run the per database query above a week later: the ratio column tells you what you gained, and the duration column usually shows the backup finishing faster as well.

How long it takes

About an hour, most of it reviewing the backup jobs for explicit options. The configuration change itself is immediate.

Related reports

Related checks

Frequently asked questions

Does compression slow the backup down? Usually the opposite. It costs CPU and saves I/O, and backups are almost always I/O bound, so they finish faster. The exception is an instance already saturating CPU during the backup window.

Is a compressed backup harder to restore? No. Decompression is automatic and requires no options at restore time. The restore is generally faster, because there is less to read.

Our encrypted database barely compresses. Expected. Encrypted data does not compress. On SQL Server 2016 and later, specifying MAXTRANSFERSIZE above 65536 enables a path where compression works on TDE databases.

Can I mix compressed and uncompressed backups in one file? No. Appending a compressed backup to an uncompressed media set fails. Write to a new file, which is better practice anyway.