Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted April 8, 2019

Compressed Microsoft SQL Server Backups by Default

By Greg Larsen

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'

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM