Share via

  • Article

Backup Compression (SQL Server)

Backup compression was introduced in SQL Server 2008 Enterprise. This topic discusses the basics of backup compression, including the performance trade-off of compressing backups. 

Note

Creating compressed backups is supported only in SQL Server 2008 Enterprise and later, but beginning in SQL Server 2008, every edition can restore a compressed backup.

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

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.