Background: I have a couple of relatively big servers(x64, 2 Socket processor 6 core each, 24GB RAM, Windows 2008R2 Standard, SQL 2008R2 Ent attached to a SAN) and I want to implement native Backup compression since I am having disk space shortage on this servers. In addition I have to frequently move around copy of backups to refresh test servers which takes a long time and i sometimes i zip the files using winrar to make the transfer faster. It seems they are a good candidate for compression since they are not CPU bound (they use less than 10%CPU). Here are my questions: 1. This is my first time trying to compress my backups. Is there any gotcha that i should be aware of both during backup and restore? 2. Which is better? to turn backup compression instance level using sp_configure or to modify my scripts(I use [Ola Hallengren's] awesome scripts for backup and it accepts a parameter @Compress = 'Y') Any insight is appreciated. Thanks :
The only gotcha is, once the things are compressed, you have to use the same version of the the software to uncompress them and restore them. No previous versions allowed (and yeah, I know that applies to regular backups too, but it's worth mentioning). Enterprise only in 2008. Enterprise and Standard in 2008R2, but no downward compatibility between the two. But the biggie really is, once it's compressed, you can only use SQL Server to get it back. No way to uncompress it at all. So if you have 2008 Enterprise, you compress and move it down to your 2008 Standard that's in QA, you're stuck. Personally, I like to control it at the backup level and not set it at the server. Probably a paranoia thing, but I want to be be able to pick and choose when it happens, not pick & choose when it doesn't (you can go either way). But that's not a serious concern, it's just me. I love compression. Not only does it save space, but it usually saves time too. The cost to CPU is so light that you get radical time savings because it's writing fewer pages. Love it.