SQL Server 2008 Database Backup compression Example

In the previous version of sql server, whenever you take backups that consume lots of space on disk based on the database size. Every time you have to clear backup files to create space for new backups. This issue has been resolved by introducing backup compression option in sql server 2008.

SQL Server 2008 comes up with compression backup concept while performing backup which will compress the backup files up to 80% of total size.  It takes less time to backup the database and reduce hardware and improve performance.

How to take backups in SQL Server 2008 with compression mode?

By default compression backup is not enabled in SQL Server 2008 to enable the same execute the below query

USE master

EXEC sp_configure ‘backup compression default’, ‘1’


Now execute the below query to take backup of “compressdb” database in c:\ drive, this statement will generate backup in compress mode using sql server 2008.

 Backup database compressdb To disk = ‘C: \test.bak’  with compression

 include WITH COMPRESSION option in the above query.



Leave a Reply




+ 7 = 8