Skip to content

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

Latest commit

 

History

History
111 lines (69 loc) · 5.75 KB

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

File metadata and controls

111 lines (69 loc) · 5.75 KB
title Server Configuration: backup compression default
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 08/26/2025
ms.service sql
ms.subservice configuration
ms.topic how-to
helpviewer_keywords
SQL Server Management Studio [SQL Server], backup compression default option
backup compression [SQL Server], backup compression default Option

Server configuration: backup compression default

[!INCLUDE SQL Server]

This article describes how to view or configure the backup compression default server configuration option in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql].

The backup compression default option determines whether the server instance creates compressed backups.

Limitations

Backup compression isn't available in all editions of [!INCLUDE ssNoVersion]. For more information, see Editions and supported features of SQL Server 2022.

By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely affect 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.

Recommendations

When you're 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.

The backup compression default option accepts the following values:

  • 0 (off) - The server instance takes uncompressed backups. (Default)
  • 1 (on) - The server instance takes compressed backups.

If you enable backup compression default, you can then use backup compression algorithm to specify the compression algorithm used for backups.

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.

Use SQL Server Management Studio

View or configure the backup compression default option

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

  2. Select 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're a member of the sysadmin or serveradmin fixed server role, you can also change the default setting by selecting the Compress backup box.

Use Transact-SQL

View the backup compression default option

  1. Connect to the [!INCLUDE ssDE].

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select 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

Configure the backup compression default option

  1. Connect to the [!INCLUDE ssDE].

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example shows how to use sp_configure to configure the server instance to create compressed backups by default.

    EXECUTE sp_configure 'backup compression default', 1;

RECONFIGURE;
GO

For more information, see Server configuration options.

Follow up: After you configure the backup compression default option

The setting takes effect immediately without restarting the server.

Related content