Backup Compression

Backup compression
Its a new feature in SQL Server 2008 Enterprise Edition.
Its support to compress a backup , for which we had to use third party tools earlier.
This can help us to reduce backup size, which is a big problem for growing databases.
Since it compress backup while taking backup it puts additional overhead on the CPU.
Though using resource manager we can control use of CPU by the current session.


If we want to comress backup by default the we have to set this option at server level.
Here it shows how to set this at server level.



How ever we can also specify at database backup level while taking backup




We can get compression ration using this query in MSDB

img 3

SELECT backup_size / compressed_backup_size,

       backup_size,

       compressed_backup_size

FROM   msdb..backupset

WHERE  database_name = 'amish_test'

Here the ratio is at 10.26 %, which shows backup is compressed 90%. :-)




Here are considertions that we have to take care while taking backup
1. char  data is compressed  more than any other data.
2.If tables are compressed than backup compression will be less or not so much
3.It data is encrypted then also backup compression will be less or not so much

For more details please look into BOL .