How To Configure Database Backup Compression in SQL 2008:

Database Backup Compression is a new feature was introduced with SQL 2008 Enterprise. This option is only available in SQL 2008 enterprise and later version.

By Default this option is disabled in server. We can configure this option in 2 different ways either by T-SQL or SQL Server Management Studio (SSMS). We can set this process only on server level not by individual database.

In T-SQL, Use the sp_configure stored procedure to set the value of backup compression default and then execute the RECONFIGURE statement

Here I have explained the step by steps process to Enable Database Backup Compression thru SSMS.

Before you enable this option, i request you to take the backup of any database available in your SQL Server. This is only for compare the size of the backup size after enable this option.

For Example,

I taken a backup of my database, which holds the size of backup file is 65.5 GB

After taken the backup, just follow the steps given below,

1. Right Click on the SQL Server

2. Select Properties
3. In “Server Properties” Dialog, Go to “Database Settings”

4. Check the option “Compress Backup”, if it is empty
5. Click Ok to Close the Dialog.

After enabled this option, I have taken the backup of the same database which I taken before this process, now compressed backup file holds only 11 GB.

Wow This option saved my storage up to 83.2% ! Hope it will help you too.