SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Backup Compression in SQL Server 2008 – The Complete Presentation

From the PASS 2010 Summit Survey: 29% of people want a presentation on backup compression. OK, here it is.

Turn it on:


Turn it off:


That’s it. If you want to script it, go here.

I saw Brent Ozar’s (blog, twitter) interpretation of the PASS Summit Survey and he had a great comment on more people asking for Backup Compression than Filestream, TDE, or Spatial. He noted: “ If I was the product manager for those last three features, I’d be dunking my head in the toilet right now, because that’s just depressing.”

I’m not sure I’d feel that way. Maybe I’d feel more like banging my own head against the wall, or perhaps wandering over to the marketing section of the Microsoft campus and banging someone else’s head against a wall. Or two.

I’m being a little silly. There could be more of a presentation on backup compression. Someone could do a “shoot out” of all the products offering compression. Someone could present some data (quickly, like in 10-15 minutes) on how compression changes the resource usage on some of their real databases. Someone could go into details on effects of different data types and the level of compression reached (10 minutes), and how it works with TDE (5 minutes).

There, we’re up to 25 minutes. Maybe 27 with a slow demo.

Or maybe, someone could compress that into 15 minutes of a talk that includes data compression, and show some real world examples of how compression impacted things, and how you might change designs to use it better.

Seriously, if you are asking for a presentation on how backup compression works, you’re in one of two situations. Either you are smart enough to delve into details and maybe you should go work for Microsoft (or Red Gate or Quest), or you need to just crack open BOL. Look, I’ll give you a bookmark.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Dukagjin Maloku on 28 April 2010

Nice presentation, Steve!

Posted by Barnaby Self on 28 April 2010

Classic! Loving it :)

Posted by Anonymous on 28 April 2010

Pingback from  Twitter Trackbacks for                 The complete presentation for Backup Compression [blog], all you need to know (thanks to         [sqlservercentral.com]        on Topsy.com

Posted by Glenn Berry on 28 April 2010

Actually, someone could do an interesting presentation on well backup compression works in terms of disk space saved, IO pressure reduced, backup and restore time reduced, vs. added CPU for different types of data in a database. They could also look at how well backup compression works when data compression is also being used.

They could show queries on how to check the compression ratio of your compressed backups.

Posted by Steve Jones on 28 April 2010

I was semi-kidding, Glenn, but I did mention those. There could be some general backup compression session, but I still think you'd be stretching to try and get it to a full hour. If you include data compression, and I would love to see sample schemas, and analysis on that, you could get a great presentation.

Posted by Andy Warren on 28 April 2010

I rarely disagree with you, but think you're off course on this one.

Not everyone is on the same (advanced) point on the road. I can see people wanting to know should I use it? What are the downsides? Should I look at 3rd party options? Maybe it is a beginner level session, but that doesnt mean it wouldn't be useful.

In theory we can all research all of our questions, removing the need for presentations altogether!

Seriously, we're never as far along in the industry as we'd like. SQL injection still happens, I still explain it to people all the time. Let's not make fun of the answers to the questions, let's work on raising the bar.

Posted by Steve Jones on 28 April 2010

I'll disagree again. I am being a little silly, but it's not that much to think about. It's really an on/off switch. It has little impact, and it can easily be turned off.

Data compression is something else. Don't confuse the two and somehow think there's more to backup compression than there is. You turn it on, you get less disk space used.

Posted by Paul White on 1 May 2010

So Steve, you're essentially saying that 29% of the people at PASS 2010 are idiots? ;c)

Glenn is right - it would be perfectly possible to fill an interesting hour based on backup compression.  There's clearly a demand for it, so I would imagine it could be quite popular.

Anyone struggling to see part of what might fill that hour can check out the excellent two-part article on SQLCAT:



There's a lot more that could be covered too - especially comparisons with popular third-party products, as Andy mentioned.

I know the blog entry was intended to be largely humourous or silly, but there is a serious point here too.


Posted by Steve Jones on 1 May 2010

It was humorous, but not too far off the truth. For the most part you turn it on if you have it. If it works, that's it. If it doesn't, you turn it off, or you could investigate in there. If someone has had issues, or improved performance, they could make a talk out of this.

However I'm surprised that 20% of the people checked this. It's an on/off feature, and outside of a few very, very high end people (like 1% of DBAs), no one I've met tunes backups. They run them. They move schedules. That's it.

I think that people see "compression" and they think there's something magical here. Data compression could be an interesting talk, but I just don't see backup compression as being something that is worth the time.

That's me. Feel free to disagree.

Leave a Comment

Please register or log in to leave a comment.