This topic describes the compression of SQL Server backups, including restrictions, performance trade-off of compressing backups, the configuration of backup compression, and the compression ratio. Backup compression is supported on SQL Server editions: Enterprise, Standard, and Developer. Every edition of SQL Server 2008 and later can restore a compressed backup.

Benefits

  • 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.For more information, see Performance Impact of Compressing Backups, later in this topic.

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

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 byResource Governor. For more information, see 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 Use SQL Server Objects.

Notes:

  • Disable by default. Have to set to use compress in database backup section.

Example

.bak File Size Before.bak File Size After
160 GB37 GB
420 GB102 GB

Sources:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-compression-sql-server?view=sql-server-ver15

Last modified: August 5, 2021

Author

Comments

Write a Reply or Comment