This topic describes how to view or configure the backup compression default server configuration option in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL. The backup compression default option determines whether the server instance creates compressed backups by default. When SQL Server is installed, the backup compression default option is off.
In This Topic
-
Before you begin:
-
To view or configure the backup compression default option, using:
-
Follow Up: After you configure the backup compression default option
Limitations and Restrictions
-
Backup compression is not available in all editions of SQL Server. For more information, see Features Supported by the Editions of SQL Server 2012.
-
By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Therefore, you might want to create low-priority compressed backups in a session whose CPU usage is limited by Resource Governor. For more information, see Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).
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.
-
Backup compression is supported for both disk backup devices and tape backup devices.
Security
Permissions
Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.
To view or configure the backup compression default option
-
In Object Explorer, right-click a server and select Properties.
-
Click the Database settings node.
-
Under Backup and restore, Compress backup shows the current setting of the backup compression default option. This setting determines the server-level default for compressing backups, as follows:
-
If the Compress backup box is blank, new backups are uncompressed by default.
-
If the Compress backup box is checked, new backups are compressed by default.
If you are a member of the sysadmin or serveradmin fixed server role, you can also change the default setting by clicking the Compress backup box.
-
To view the backup compression default option
-
Connect to the Database Engine.
-
From the Standard bar, click New Query.
-
Copy and paste the following example into the query window and click Execute. This example queries the sys.configurations catalog view to determine the value for backup compression default. A value of 0 means that backup compression is off, and a value of 1 means that backup compression is enabled.
USE AdventureWorks2012 ; GO SELECT value FROM sys.configurations WHERE name = 'backup compression default' ; GO
To configure the backup compression default option
-
Connect to the Database Engine.
-
From the Standard bar, click New Query.
-
Copy and paste the following example into the query window and click Execute. This example shows how to use sp_configure to configure the server instance to create compressed backups by default.
USE AdventureWorks2012; GO EXEC sp_configure 'backup compression default', 1 ; RECONFIGURE WITH OVERRIDE ; GO
For more information, see Server Configuration Options.
The setting takes effect immediately without restarting the server.