Compression backup

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Compression backup

Compression backup

Rate Topic

Display Mode

Topic Options

Author

Message

sqlfriends

Posted Monday, February 11, 2013 10:34 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 1,612, Visits: 2,796

Any pros and cons for using compression backup?

I know the obvious one that could save up a lot of space on backup drive.

Other that what other pros and cons, is it a good practice for DBA to enable it on server level?

Post #1418550

Bill (fluffydeadangel)

Posted Monday, February 11, 2013 10:39 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:59 PM
Points: 70, Visits: 223

Restores are faster, less Disk I/O.
Cost of storage on tapes or san or where ever you store (less space needed)
I honestly cannot think of any CONS for backup compression...

Higher CPU IO possibly? If you're fighting with 100% CPU at all times... well I'd fix that first, but compression may slow down? Since it's done off hours, I can't think of any functional down sides.

Post #1418555

sqlfriends

Posted Monday, February 11, 2013 11:07 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 1,612, Visits: 2,796

Thank you, I am concerned a little bit if compression backup is reliable or not since it is compressed, any chance it compressed in error?

Thanks

Post #1418576

Bill (fluffydeadangel)

Posted Monday, February 11, 2013 12:28 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:59 PM
Points: 70, Visits: 223

Everything we can use a compressed backup on, we do. I've not seen any instances that there was an increased chance of corruption due to compression. Again, that's personal use. We use compressed backups on around 450 databases across 100+ servers. We haven't had a corrupt backup yet (knocks on wood). We test backups randomly to verify they're good.

Post #1418599

sqlfriends

Posted Monday, February 11, 2013 12:54 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 1,612, Visits: 2,796

Thanks much, that is good news. Glad to know that.

Post #1418611

opc.three

Posted Monday, February 11, 2013 7:49 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 6,737, Visits: 11,791

Here is a recent thread with more information from some very trusted resources:

http://www.sqlservercentral.com/Forums/Topic1416031-1550-1.aspx

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1418715

Bill (fluffydeadangel)

Posted Monday, February 11, 2013 8:59 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:59 PM
Points: 70, Visits: 223

notice it's the same person asking. ha.

Post #1418731

Grant Fritchey

Posted Tuesday, February 12, 2013 4:35 AM

SSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:41 AM
Points: 13,383, Visits: 25,189

I've been using backup compression for years across a wide range of systems, both native and third party. Compression has been extremely reliable and overall a net positive. I say that without any reservation at all. As was already noted, if you're on a system that is already severely stressed with CPU issues, then compression will add to that stress. But since most of us are running on systems where the CPU is practically asleep, especially at night when the main backup would occur, there's no reason to not use it.

The only down-side I've run into is one of knowledge. If you have a compressed backup, be sure that's communicated to others in some way so that people don't try to restore a compressed file to a system that is the wrong version or doesn't have the correct software.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software

Post #1418871

sqlfriends

Posted Tuesday, February 12, 2013 9:48 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 1,612, Visits: 2,796

Thanks much,
I also read somewhere if using BLOBS, and Data encryption, it is not recommended to use compression backup, anything else?

Thanks

Post #1419053

opc.three

Posted Tuesday, February 12, 2013 9:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:26 AM
Points: 6,737, Visits: 11,791

You can use compression when using either but at least with TDE you won't get much of a compression ratio. If you're encrypting many columns or are using lots of BLOBs you will lose some compression ratio but that is not to say that if you have some of those elements in your database that you should completely abandon the idea of compressing your backups. Test and see if you gain any benefit from using compression before deciding.

By the way, one point of clarification in your earlier comment:

Other that what other pros and cons, is it a good practice for DBA to enable it on server level?

You do not need to enable it at the server-level per se. What you do is set the default behavior of the BACKUP command via sp_configure. The server-level default can always be overridden one way or the other using the COMPRESSION or NO_COMPRESSION options of the BACKUP command.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419062

« Prev Topic | Next Topic »

Permissions