| title | View or Configure the backup compression default (server configuration option) | ||
|---|---|---|---|
| description | Find out about the backup compression default option. See how it determines whether SQL Server creates compressed backups by default, and learn how to set it. | ||
| author | rwestMSFT | ||
| ms.author | randolphwest | ||
| ms.date | 03/02/2017 | ||
| ms.service | sql | ||
| ms.subservice | configuration | ||
| ms.topic | conceptual | ||
| helpviewer_keywords |
|
[!INCLUDE SQL Server]
This topic describes how to view or configure the backup compression default server configuration option in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql]. The backup compression default option determines whether the server instance creates compressed backups by default. When [!INCLUDEssNoVersion] 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
-
Backup compression is not available in all editions of [!INCLUDEssNoVersion]. For more information, see Features Supported by the Editions of SQL Server 2016.
-
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).
-
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.
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.
-
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.
-
-
Connect to the [!INCLUDEssDE].
-
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.
SELECT value
FROM sys.configurations
WHERE name = 'backup compression default' ;
GO -
Connect to the [!INCLUDEssDE].
-
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.
EXEC sp_configure 'backup compression default', 1 ;
RECONFIGURE;
GO For more information, see Server Configuration Options (SQL Server).
The setting takes effect immediately without restarting the server.
BACKUP (Transact-SQL)
Server Configuration Options (SQL Server)
RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL)
Backup Overview (SQL Server)