Enable Backup Compression as Default in SQL Server 2008 R2

Of all the features added to SQL Server over the past few years I was particularly pleased with the addition of backup compression, there have been third-party tools around for a while that have done this (such as Quest’s excellent Litespeed) but third-party tools always add an extra layer of admin and maintenance that as a DBA I could do without. In SQL Server 2008 it was made available for Enterprise licences only but with the launch of SQL Server 2008 R2 backup compression is now available with a Standard license, though in both cases the feature is turned off as a default – so how do you use it?

Well, if you’re making a backup from Management Studio you can specify manually on the Options page of the “Back Up Database” page (right-click on DB >> Tasks >> Backup), like so…

Alternatively, if you’re one of the “Real DBAs don’t use GUIs” crowd you can add the “WITH COMPRESSION” option to the BACKUP command..

BACKUP DATABASE reportserver TO disk = ‘E:\ReportServer.bak’
WITH COMPRESSION

Or, conversely you can specify not to use compression…

BACKUP DATABASE reportserver TO disk = ‘E:\ReportServer_no_compression.bak’
WITH NO_COMPRESSION

But what if you want to use compression but don’t want to have to think about it every time – or more importantly, don’t want your users to have to think about it? To enable backup compression as a server-wide default you will need to use sp_configure…

USE MASTER;
GO
EXEC sp_configure ‘backup compression default’,’1′;
RECONFIGURE WITH OVERRIDE;

Now all you need to do is tell your storage admin that he/she owes you a drink!