view-or-configure-the-backup-compression-default-server-configuration-option.md

title	View or Configure the backup compression default Server Configuration Option | Microsoft Docs
ms.custom
ms.date	03/02/2017
ms.prod	sql
ms.prod_service	high-availability
ms.reviewer
ms.technology	configuration
ms.topic	conceptual
helpviewer_keywords	SQL Server Management Studio [SQL Server], backup compression default option backup compression [SQL Server], backup compression default Option
ms.assetid	23029395-3e93-4c29-b7d6-e5a47a3526ff
author	MikeRayMSFT
ms.author	mikeray
manager	jroth

View or Configure the backup compression default Server Configuration Option

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md]

This topic describes how to view or configure the backup compression default server configuration option in [!INCLUDEssCurrent] 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:

  Limitations and Restrictions

  Recommendations

  Security

• To view or configure the backup compression default option, using:

  SQL Server Management Studio

  Transact-SQL

• Follow Up: After you configure the backup compression default option

Before You Begin

Limitations and Restrictions

• 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).

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.

Using SQL Server Management Studio

To view or configure the backup compression default option

1. In Object Explorer, right-click a server and select Properties.

2. Click the Database settings node.

3. 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.

Using Transact-SQL

To view the backup compression default option

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. 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  

To configure the backup compression default option

1. Connect to the [!INCLUDEssDE].

2. From the Standard bar, click New Query.

3. 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).

Follow Up: After you configure the backup compression default option

The setting takes effect immediately without restarting the server.

See Also

BACKUP (Transact-SQL)
Server Configuration Options (SQL Server)
RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL)
Backup Overview (SQL Server)