Now, in terms of configurating this feature, during 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 are able to 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.
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.
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:
- 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:
- Start by connecting to the Database Engine and pressing the New Query button to create the script file.
- To configure the backup compression default option at server-level so that backups are created 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 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;