• Article

How to: View or Change the backup compression default Option (SQL Server Management Studio)

This topic explains how to view and configure the backup compression default server-level configuration option by using SQL Server Management Studio. This option controls whether backup compression is used by default when backups are created.

Note

Backup compression is supported only by SQL Server 2008 Enterprise and later.

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.

    Note

    When creating an individual backup, configuring a log shipping configuration, or creating a maintenance plan, you can override the server-level 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.

    Important

    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 How to: Use Resource Governor to Limit CPU Usage by Backup Compression (Transact-SQL).