Backup Compression
It’s been a while, since I posted and as SQL 2008 has been out a while now, I figured it should make an appearance on my blog..
There have been utilities around for a while now that enable you to compress backup files – which is always a good thing, especially if you have to move the backup files between servers.
This facility has now been added to the Enterprise Endition of SQL Server 2008. The following, very basic script will backup the AdventureWorks database with compression.
BACKUP DATABASE AdventureWorks TO DISK = 'd:\backup\AdventureWorks.bak' WITH stats, compression
When using backup compression (in my environment) the backup file size was 25% of the size of the standard SQL backup, and it took half the amount of time. Another advantage of using compression is that a checksum is automatically done on the backup which should identify media corruptions.
Compression does have a CPU hit, but on testing, I never noticed it going above 20%.
