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:

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