Backup Compression in SQL Server 2008
Hi Friends, Yes I know this is very well known and something too old to post about; unless I received a mail today asking me about this. So here is something about Backup COmpression for people who are new to SQL Server 2008 or do not know about it.
Backup Compression is an Enterprise Edition feature only. You can get huge disk space savings upto 80%. However, compression ratio depends on multiple factors including but limited to type of data, consistency of data, whether the data is encrypted or not, etc..
Compressing a backup reduces I/O considerably but increases CPU usage significantly.
Take a normal full backup of Adventureworks and observe the backup size.
Then take another backup with compression (you can use the following code) and observe the size.
-- BACKUP DATABASE [AdventureWorksDW] TO DISK = N’C:\AD-DW.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorksDW-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO --
when you take backups in the both the scenarios, try to use Performance monitor to observer the I/O counter and the CPU counter to observe the perforamnce tradeoff (IO vs CPU)
