Backing up database is a routine DBA job. This easy task become a headache when the machine runs out of disk space. We need to cleanup the disk or attach a new one to do the backup. Fortunately, SQL Server 2008 comes with built in compression feature of backup set. We can do backup and compress at the same time. What does it mean?
- Save disk space, the compressed size is only around 10% of normal backup.
- Faster backup time.
- Faster restore time (surprise)!.
This is very convenience approach instead of doing normal backup and compress it with Winzip. According to benchmark I did, Winzip compression works slower than backup with compress on the fly. Not mention that we also have to unzip the file to restore from backup.
I am also surprise because restoring compressed backup is faster than non compressed. Logically, it needs to decompress and restore so it should be slower than restoring normal one. But my benchmark showed the opposite. I modified the AdventureWorks2008 sample database and insert additional data to make it bigger.
Below is my benchmark results with 900 MB size.
- Backup with no compress: 115 seconds
- Backup with compress: 49 seconds
- Restore from non compressed backup : 123 seconds
- Restore from compressed backup : 113 seconds
So, are you convinced? Here are the scripts to do compressed backup.
Specify with compression when doing a backup:
BACKUP DATABASE AdventureWorks2008
TO DISK = 'C:backupdbadvworksbackupcompress.bak'
The result is a compressed backup file, it’s smaller size then non compressed:
It’s reduced from 900+ MB to only 118 MB, not bad right?
Restoring from compressed backup is straight forward. No additional option required. Just normal restore syntax from the name of backup set.
RESTORE DATABASE AdventureWorks2008
FROM DISK = 'C:backupdbadvworksbackupcompress.bak'
I use with REPLACE option to override existing AdventureWorks2008 database.