Backup
Compression is a new feature in SQL Server 2008, and for backward
compatibility, the default value for backup compression is 0, meaning that
backups are not compressed.
Although only Enterprise edition instances can
create a compressed backup, any edition of SQL Server 2008 can restore a
compressed backup. When Backup Compression is enabled, the compression is
performed on the server prior to writing, so it can greatly reduce the size of
the backups and the I/O required writing the backups to the external device.
The amount of space reduction depends on many factors, including the following:
The type of data in the backup For example, character data compresses more than other types
of data.
Whether the data is encrypted Encrypted data compresses signifi cantly 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 data is encrypted Encrypted data compresses signifi cantly 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.
After the backup
has been performed, you can inspect the backupset table in the msdb database to determine the compression ratio, using a
statement like the following:
SELECT
backup_size/compressed_backup_size FROM msdb..backupset;
Although compressed backups can use significantly fewer I/O
resources, it can significantly increase CPU usage when performing the
compression. This additional load can affect other operations occurring
concurrently. To minimize this impact, you can consider using the Resource
Governor to create a workload group for sessions performing backups and assign
the group to a resource pool with a limit on its maximum CPU utilization.
The configured value is the inst
ance-wide default for Backup
Compression, but it can be overridden for a particular backup operation, by
specifying WITH COMPRESSION or WITH NO_COMPRESSION. Compression can be used for
any type of backup: full, log, differential or partial (file or filegroup).
