SQL Server Backup Compression

We are going to discuss the compression of SQL Server backups, including restrictions, performance trade-off of compressing backups and the compression ratio.

SQL Server Backup Compression
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.

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.

Calculate the Compression Ratio of a Compressed Backup

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.

3:1 compression ratio indicates that you are saving about 66% on disk space.


<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.