Now that we have previously discussed about backup compression, it is time we offer details about how to configure compression for your backups.

Just as a short reminder, backup compression is a powerful feature offered by SQL Server (versions 2008 and newer) that allows you to shrink the amount of storage space used by your backup files while at the same time increasing the speed of recovery, which goes hand in hand with lower CPU usage when restoring the database from your backup.

Now, in terms of configuring this feature, during the installation of SQL Server, backup compression is set off by default. The default state for backup compression is set by the backup compression default option, which is a configuration option set at server-level.

However, you can override the default setting when you are creating a single backup or when you set up a schedule for a series of routine backups.

Before deciding to change these settings, you have to be aware that compression significantly increases CPU usage and that the additional CPU which is used by the compression process might have a negative effect on other concurrent operations.

Therefore, you might want to consider creating a low-priority compressed backup in a session whose CPU usage is limited by Resource Governor.

Recommendations

When you are creating an individual backup, configuring a log shipping configuration, or creating a maintenance plan, you can override the server-level default.

Also, backup compression is supported for both disk backup devices and also for tape backup devices.

Security permissions

By default, execute permissions on sp_configure, which you will need, with no parameters or with just the first parameter is allowed for all users.  In order to execute sp_configure with both of the available parameters which will change the configuration option or to be able to run the RECONFIGURE statement, a user must have permissions at server-level to run the ALTER SETTINGS. The ALTER SETTINGS permission is allowed by default to members of the sysadmin and serveradmin fixed server roles.

Now, in order to change the default setting you have two choices, one involves running a query and the other using SSMS (SQL Server Management Studio) to change the backup compression option.

The first option which we will try is using the graphical interface of SSMS where you can follow these steps:

1. In the Object Explorer window, right-click the database server for which you want compressed backups to be created and select Properties.
   

   

2. Click the Database Settings page in the left side menu
   

   

3. Under Backup and restore, the Compress backup tickbox shows the current setting for this option.
   

   

4. • • If the tickbox is unchecked, new backups are created uncompressed by default.
     • If the tickbox is checked, new backups are compressed by default.

If you are a member of the syadmin or serveradmin fixed server role, you can also change the default setting by checking the tickbox.

As said above, there is a second option, using T-SQL:

1. Start by connecting to the Database Engine and pressing the New Query button to create the script file.
2. To configure the backup compression default option at the server level so that backups are created and compressed by default, run the following query:

   EXEC sp_configure 'backup compression default', 1; 
   RECONFIGURE WITH OVERRIDE; 
   GO

   These settings take effect immediately, so no server restart is required.

If you would just like to see the status of backup compression without using the SSMS interface, you can run the following T-SQL query:

SELECT value 
FROM sys.configurations 
WHERE NAME = 'backup compression default';

There is also the option of overriding the server backup-compression default when creating a backup with T-SQL by using either WITH NO_COMPRESSION or WITH COMPRESSION option in your BACKUP statement.

An example statement of this can be seen below:

BACKUP DATABASE AdventureWorks2012 TO DISK = 'Z:\AdvWorksData.bak' WITH FORMAT, COMPRESSION;