Blitz Result: Backup Compression Not Default

FOR THE COST OF SOME CPU CYCLES

You can save a ton disk space on that shiny new SAN by compressing your backups. It can also save you a bunch of time moving backups across the network, because you’re not storing backups locally, right?

RIGHT?!

And compressing your backups also makes them run faster, too, simply because there’s a lot less data being written out. What’s not to love?

Backup compression has been available natively in SQL Server 2008, and was introduced in Standard Edition starting in SQL Server 2008R2. The only scenario it doesn’t work well in is if you also use TDE (Transparent Data Encryption), which randomizes data and can actually result in backups being larger in some cases. SQL Server 2016 can get compression with TDE, but read Microsoft’s posts on the huge gotchas.

HOW TO FIX THE PROBLEM

You can set this individually on each of your backup jobs, but why not set it as the default at the server level as well? Choose one of the two options on how to make this change below.

Return to sp_Blitz® or Ask Us Questions

How to Set Backup Compression as the Default Option in SQL Server

There are two ways you can make this change. To do this in SQL Server Management Studio, connect to the instance in Object Explorer, right click the instance name, and select ‘Properties’. Then click on the Database Settings tab and check off the ‘Compress Backup’ box:

If you prefer code to clicking, here’s a command that you can use to make the change: