Backup compression was introduced in SQL Server 2008 Enterprise, which reduces the size of backups and the time that it takes to create them.
Using backup compression is a simple process. Transact-SQL—WITH COMPRESSION in the BACKUP statement,
BACKUP DATABASE AdventureWorks2008
TO DISK = ‘C:\AWBackupCompressed.bak’ WITH COMPRESSION
By default, no backups are compressed. You can change this default setting by using sp_configure to set the value of the backup_compression_default setting as shown in the following code example. Alternatively, you can change the default setting by selecting the option in the Server Properties dialog box.
EXEC sys.sp_configure N’backup compression default’, N’1’
RECONFIGURE WITH OVERRIDE
Although compressed backups require less disk space to store them and less time to create, there is a tradeoff. The compression is a CPU-intensive operation that can impact other operations that occur on your servers. To avoid such contention, you can use Resource Governor to limit the CPU usage for a backup operation.
- Compression and transparent data encryption are mutually exclusive.
- Creating a backup with compression is supported only in SQL Server 2008 Enterprise Edition; all SQL Server 2008 editions can restore a compressed backup.
Video Tutorial for Backup Compression