XML FeedsI got a brand new SQL Server 2008 test server and decided to test backup compression. I picked 2 databases to do this test; the smaller database is 4.8GB in size the bigger database is about 44 GB in size.
Let's start with the smaller database.
First I backed the database up, one backup used compression while the other one did not.
Let's look at some code
- BACKUP DATABASE [SmallDB] TO DISK = N'V:\SmallDB_Compressed.BAK'
- WITH NOFORMAT, NOINIT,
- NAME = N'SmallDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,
- STATS = 2
- GO
The compressed backup took 34.313 seconds (138.881 MB/sec) to complete.
- BACKUP DATABASE [SmallDB] TO DISK = N'V:\SmallDB_UnCompressed.BAK'
- WITH NOFORMAT, NOINIT,
- NAME = N'SmallDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
- STATS = 2
- GO
The uncompressed backup took 41.716 seconds (114.235 MB/sec) to complete.
As you can see the compressed backup was a little faster. The size of the backup was 1.25 GB for the compressed backup and 4.8 GB for the uncompressed backup
After I did the backups I decided to do the restores.
- RESTORE DATABASE [SmallDB]
- FROM DISK = N'V:\SmallDB_Compressed.BAK'
- WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 1
- GO
RESTORE DATABASE successfully processed 609980 pages in 23.524 seconds (202.578 MB/sec).
- RESTORE DATABASE [SmallDB]
- FROM DISK = N'V:\SmallDB_UnCompressed.BAK'
- WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 1
- GO
RESTORE DATABASE successfully processed 609977 pages in 38.951 seconds (122.344 MB/sec).
The restores had a bigger difference in time than the backups.
After I was done with the small database I decided to take a bigger database, this database is 44 GB, I wanted to see if using a bigger database would make a bigger or a smaller difference when using compressed backups compared to uncompressed backups.
- BACKUP DATABASE MediumDB TO DISK = N'V:\MediumDB_Compressed.BAK'
- WITH NOFORMAT, NOINIT,
- NAME = N'MediumDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,
- STATS = 2
- GO
The compressed backup took 303.113 seconds (142.845 MB/sec) to complete.
- BACKUP DATABASE MediumDB TO DISK = N'V:\MediumDB_UnCompressed.BAK'
- WITH NOFORMAT, NOINIT,
- NAME = N'MediumDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
- STATS = 2
- GO
The uncompressed backup took 395.740 seconds (109.410 MB/sec) to complete.
Just as before with the small database, the compressed backup was a little faster. The size of the backup was 9.725 GB for the compressed backup and 44.338 GB for the uncompressed backup
Now let's take a look at the restore.
- RESTORE DATABASE [MediumDB]
- FROM DISK = N'V:\MediumDB_Compressed.BAK'
- WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 1
- GO
RESTORE DATABASE successfully processed 5542183 pages in 215.319 seconds (201.089 MB/sec).
- RESTORE DATABASE [MediumDB]
- FROM DISK = N'V:\MediumDB_UnCompressed.BAK'
- WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 1
- GO
RESTORE DATABASE successfully processed 5542177 pages in 456.590 seconds (94.829 MB/sec)
The restore of the compressed backup took less than half the time of the uncompressed backup
To sum it all up:
- Backups are faster if you use compression
- Restore are significantly faster if you restore from a compressed backup
- Since the files are small you can store a lot more backups on the server and if you have to move it to another server it will be much faster also.
Another good thing is that with SQL Server 2008 R2 backup compression will be available in the standard edition as well, until now backup compression was only an Enterprise Edition feature
All in all I was impressed with backup compression and I can't see a reason why you would not want to use it. It does use more CPU to backup when using compression but I am more RAM bound than CPU bound.
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Interesting...I am glad that it will be in the standard edition in the future...
Thanks Denis, never really looked at this before. I have a database that I'm constantly backing up to a USB stick so I can use it on my laptop when I'm not at home. This sped up the backups significantly, and will let me keep almost twice as much history :) 
Here is Stats from Michel Fournier posted on UT:
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.