Compressed Microsoft SQL Server Backups by Default

If you compress your database backups you can save a ton of disk space. But by default compressed backups are not created. In order to create a compress SQL Server database backup when your default is not compressed you need to add the “WITH COMPRESSION” option to your BACKUP command. Not a big deal to do, but if you want to always create a compressed backup it might be better to change the default backup type to be compressed. By doing that all our backup command that forget to add the “WITH COMPRESSION” option will be compressed by default.

Your default backup compression option is determined by a system configuration. You can determine how your system configuration is set for the default compression by running the following command:

EXEC sys.sp_configure N'backup compression default';

When you run the above command you will get some output that might look like this:

DB Compression

Here you can see the configured and run value for my backup compression default setting. Note that currently my backup compression default is set to zero (0). This means by default my backups are not compressed. Therefore when a BACKUP command is run that doesn’t have the WITH COMPRESSION option defined I will get an uncompressed backup.

To change the default backup compression options to be compressed you just need to run the following two statements:

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

By running these two command I have changed the default “backup compression default” value to 1. This is the value that will enable compressed backups by default.

# # #

See All Articles by Columnist Gregory A. Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles