Adventures in SQL Server 2008 - Compression

Today is one of those days that only comes around once in a while.  It's what I like to call a low volume of work day.  Doesn't happen that often so I'll take when it comes around;)

I decided to take this opportunity to learn about some features of SQL Server 2008 that I've been very interested in but have simply have not had the time to complete.  This will be the first in a series of brief postings.  This topic is about compression.  Backup compression, that is.

Backup compression is nothing new.  There are several companies that make some truly remarkable products.  Idera and Quest Software are two of several that exist.  These products allow you to take a backup and compress the size of the backup by 40% to 85%. 

Introduced with SQL Server 2008, SQL Server DBA's now have the ability to compress backups using native SQL code.  And it's a piece of cake too.  I have created a database on my machine called Spatial.  I'm learning about geospatial stuff too, but that's for a later post.  I've loaded about 40MB worth of data.  The bigger your database is the more noticeable the compression is.

Take take a backup I've used the following code.  No compression.

backup database [spatial]

to disk = 'd:\spatial_with_compression.bak'

with stats = 1, init

Now for the good stuff.  Compression!  Compress, baby, compress!!!

backup database [spatial]
to disk = 'd:\spatial_no_compression.bak'
with stats = 1, init, compression

All you need to do is add the keyword 'compression' and just like that you're now compressing your database backups.  Compressing my backup reduced the size of the backup by over 50%.  It went from 40MB to a little over 17MB.  I'll take that anyday.

There are a few gotchas to be aware of.

1. Compressed backups can only be restored on SQL Server 2008 or greater database server.
2. You will take a performance hit in CPU cycles when compressing the database.

Because this was a discovery process for me, I don't know if you will see an increase or decrease in time when using compression.  If anyone knows I'd love to hear your thoughts on this.

Update on Thursday, November 18, 2010 at 12:23PM by J.D. Gonzalez

@AaronBertrand just mentioned to me that backups done on SQL Server 2008 can only be restored on SQL Server 2008+.