Backup Compression – SQL Server 2008 (Video Tutorial)

Leave a comment (0) Go to comments

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.

USE master
EXEC sys.sp_configure N’backup compression default’, N’1’

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.


  1. Compression and transparent data encryption are mutually exclusive.
  2. 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

EOF - Backup Compression – SQL Server 2008 (Video Tutorial), SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

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


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.