Enabling Native Backup Compression In SQL Server

One of the most commonly overlooked features of SQL Server is the ability to enable backup compression.  To be honest, I disregarded this feature for quite some time in favor of Idera SQL Safe.  And though I still love the tools Idera provides, especially with their .safe backup compression, there are many times where taking a native SQL Server backup is a better option.

If you’ve never enabled backup compression you may be in for a big surprise.  For one, the overall time it takes to perform the backup can range from moderately faster to just flat out insane.  I was able to cut the backup time on a large database from nearly 2 hours down to just over 30 minutes.  Though these results may vary a bit depending on the database size and the time of day which the backup is being performed, it’s certainly worth checking out.

The first thing you may want to check is whether you already have backup compression enabled.  To do this you can follow the steps outlined below or you can execute the following script.

The steps for enabling backup compression are simple.  Once you have SQL Server Management Studio open and are connected to your SQL Server, right-click the server and select Properties.

Once the Server Properties window is displayed select the Database Settings page. Here you will see a checkbox labeled Compress backup.

Note that since this is a server setting this will set the default backup setting to compress backups for all databases on the server.  This can easily be disabled by simply unchecking the box or running the script down below and changing the 1 to a 0.

Click OK.  You’re all done.  Backup compression will now be enabled on all your databases on the server.

As with everything else in SQL Server, there’s also a script for that.  The following script will update the value setting in the sys.configurations DMV to 1, thus enabling backup compression on the server.

If you’re just now getting around to enabling backup compression I would highly recommend doing some testing.  Take a native, non compressed backup of a database and take note of the stats and overall time it takes to complete.  Then enable backup compression and run the backup again. It’s always good to understand and visualize how a settings change will affect your system.