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.
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.
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.
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
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.
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:
When backing up a database, you can control backup compression for an individual database, file, or log backup.
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.
You can control the backup compression behavior of log backups.
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:
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.