What’s the issue?
SQL Server supports an option called WITH COMPRESSION on backup commands, which compresses the backup stream as it is written. The result is a smaller backup file, faster backup duration in many cases (since less data is written to disk), and faster restore times for the same reason.
The server level configuration option backup compression default controls whether compression is applied automatically to every backup that does not explicitly specify a compression option. The default value is 0, meaning compression is not used unless explicitly requested in each backup command.
This finding identifies instances where backup compression default is set to 0, leaving compression off by default for any backup that does not specifically opt in.
Why is this a problem?
Without compression, backup files are significantly larger than they need to be, often two to four times the size of the equivalent compressed backup depending on the data. This consumes more storage on the backup target, which is multiplied across every full, differential, and log backup retained for the recovery window.
Larger backups also take longer to transfer over the network when copied to offsite storage, secondary backup targets, or disaster recovery locations. The increased transfer time affects backup windows, recovery point objectives for offsite copies, and the cost of storage and bandwidth.
Restore times are similarly affected. A larger backup file takes longer to read from disk during a restore, which extends recovery time during exactly the moments when speed matters most. Compressed backups typically restore faster than uncompressed ones because the disk read time saved by smaller file size outweighs the small CPU cost of decompression.
What should you do about this?
Enable compression as a backup default with EXEC sp_configure ‘backup compression default’, 1; RECONFIGURE;. The change takes effect immediately for all subsequent backups.
The performance impact of backup compression is a modest CPU increase during the backup operation, typically offset or exceeded by the I/O savings from writing less data. On most modern hardware the net effect is faster backups overall, but test in a non production environment first if your SQL Server host is CPU constrained or runs unusually CPU heavy workloads during backup windows.
Note that compression does not benefit databases where data is already compressed at the storage level or where the data itself does not compress well. Databases with significant amounts of TDE encrypted data, image data, or compressed columnstore indexes show smaller compression ratios than typical OLTP databases. The setting is still appropriate as a default since SQL Server only applies compression when it is beneficial.
Read more…
Backup compression (SQL Server) – SQL Server | Microsoft Learn