question

DaniSQL avatar image
DaniSQL asked

Native SQL Server 2008R2 Backup Compression

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][1] awesome scripts for backup and it accepts a parameter @Compress = 'Y') Any insight is appreciated. Thanks [1]: http://ola.hallengren.com/About.html1.
sql-server-2008-r2backup-compression
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks @Grant for the wonderful advice. I am aware of the requirement to have same version to restore the compressed backup; these two servers have a corresponding dev environment running SQL Server 2008R2 Dev version. Will report back my results in a few days
0 Likes 0 ·
@Grant You mentioned "I like to control it at the backup level and not set it at the server". My scenario: My client has requested to compress the database backup files but their server does not allow the compression. How should i handle this without asking them to turn the option ON? or without making any changes on their server. Any suggestion appreciated. Thanks
0 Likes 0 ·
Usually best practice to ask your own question. You'll get more responses. This way I may be the only person who sees your question and I may not have the right answer for you. You run "BACKUP DATABASE x WITH COMPRESSION" and it doesn't work? Are you on the right version of SQL Server? Is there an error message? The server setting just turns it on for all backups. It doesn't do anything differently than running a backup using the WITH statement to get compression.
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
You can actually restore a compressed backup on an edition of the same version of SQL Server (ie restore a compressed backup from Enterprise Edition to Standard Edition) provided that you don't have Enterprise features enabled (in that situation you wouldn't be able to restore the backup anyway. See [ http://technet.microsoft.com/en-us/library/bb964719(SQL.100).aspx][1] for more information. I like to turn on backup compression by default (personal preference) as I can't think of occasions where I wouldn't use it. [1]: http://technet.microsoft.com/en-us/library/bb964719(SQL.100).aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That's good to know. I thought restoring to other versions is possible only on 2008R2 since both Ent and standard support backup compression in 2008R2.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.