Backup Compression (SQL Server)

Restrictions

The following restrictions apply to compressed backups:

• Compressed and uncompressed backups cannot co-exist in a media set.

• Previous versions of SQL Server cannot read compressed backups.

• NTbackups cannot share a tape with compressed SQL Server backups.

Performance Impact of Compressing Backups

Because a compressed backup is smaller than an uncompressed backup of the same data, compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly.

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

To obtain a good picture of your backup I/O performance, you can isolate the backup I/O to or from devices by evaluating the following sorts of performance counters:

• Windows I/O performance counters, such as the physical-disk counters

• The Device Throughput Bytes/sec counter of the SQLServer:Backup Device object

• The Backup/Restore Throughput/sec counter of the SQLServer:Databases object

For information about Windows counters, see Windows help. For information about how to work with SQL Server counters, see Using SQL Server Objects.

Configuration

At installation, backup compression is off by default. The default behavior for backup compression is defined by the backup compression default Optionserver-level configuration option. You can override the server-level default when creating a single backup or scheduling a series of routine backups.

To change the server-level default

• Transact-SQL

  Use the sp_configure stored procedure to set the value of backup compression default and then execute the RECONFIGURE statement

• SQL Server Management Studio

  Use the Database Settings page of the Server Properties dialog. For more information, see How to: View or Change the backup compression default Option (SQL Server Management Studio).

To override the backup compression default

You can change the backup compression behavior for an individual backup, backup job, or log shipping configuration.

• Transact-SQL 

  For a given backup, you can use either WITH NO_COMPRESSION or WITH COMPRESSION in a BACKUP statement.

  For a log shipping configuration, you can control the backup compression behavior of log backups by using sp_add_log_shipping_primary_databasesp_change_log_shipping_primary_database (Transact-SQL).

• SQL Server Management Studio 

  You can override the server backup compression default by specifying Compress backup or Do not compress backup in any of the following dialog boxes:

  • Back Up Database (Options Page)

    When backing up a database, you can control backup compression for an individual database, file, or log backup.

  • Maintenance Plan Wizard

    The Maintenance Plan Wizard enables you to control backup compression for each set full or differential database backups or log backups that you schedule.

  • SQL Server 2008 Integration Services (SSIS) Back Up Database task

    You can control the backup compression behavior when creating a package for backing up a single database or multiple databases.

  • Log Shipping Transaction Log Backup Settings

    You can control the backup compression behavior of log backups.

Compression Ratio

To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table, as follows:

backup_size:compressed_backup_size

For example, a 3:1 compression ratio indicates that you are saving about 66% on disk space. To query on these columns, you can use the following Transact-SQL statement:

SELECT backup_size/compressed_backup_size FROM msdb..backupset;

The compression ratio of a compressed backup depends on the data that has been compressed. A variety of factors can impact the compression ratio obtained. Major factors include:

• The type of data.

  Character data compresses more than other types of data.

• The consistency of the data among rows on a page.

  Typically, if a page contains several rows in which a field contains the same value, significant compression might occur for that value. In contrast, for a database that contains random data or that contains only one large row per page, a compressed backup would be almost as large as an uncompressed backup.

• Whether the data is encrypted.

  Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all.

• Whether the database is compressed.

  If the database is compressed, compressing backups might not reduce their size by much, if at all.

See Also

Tasks

Reference

Concepts