T-SQL Tuesday #85 – Backup Compression

T-SQL Tuesday time again. First one I’ve taken part in for over a year – oops. This time, Kenneth Fisher (blog|@sqlstudent144) is hosting a blog party about backups.

So here’s a quick post about Backup Compression.

Backup Compression

Told you.
Available since SQL Server 2008 Enterprise edition, or SQL Server 2008 R2 in other editions, this neat little bit of functionality allows you to compress your backups as they are made, but it needs to be enabled at the server level. And, by default, it’s not enabled. Thanks, Microsoft.

I’m not going to talk about the details of enabling backup compression here. Just one of the side-effects of enabling it. It’s OK, it’s a good one.

What does it do?

SQL Server backup compression – does what it says on the tin. Instead of SQL Server taking a backup by reading pages / extents of data from the database file and writing them out to the backup file, it compresses the data before it writes. It’s not the best compression you’ll get, as it won’t read the entire file before compressing it; however, it’s good enough that on OLTP databases that contain normal varchar / numeric data you could see savings of over 75% – indeed, I have some databases that save 90% of disk space. And that’s made my storage guys happy. Well, less unhappy.

You mentioned a side-effect…

Yes…

As well as the obvious not-taking-up-so-much-disk-space, there’s another little benefit – the backup jobs, now they don’t have to write out so much data, are rather quicker.

How much quicker?

Well, it depends. (Sorry.)

Remember that thing I did a while ago about pulling SQL Server job history into an Outlook calendar? Well, here’s what our backups looked like a couple of weeks ago – at least, some of ’em…

This is what the backups looked like a few weeks ago, before we enabled compression globally. Yes, I know, all the jobs (well, a lot of ’em) start at the same time, but that’s kinda the point – hammering the storage…

These are the timings for Monday-Wednesday, 21st-23rd November:

And here, two weeks later, after compression was enabled everywhere, Monday-Wednesday, 5th-7th December:

Spot the difference.