Post reply

Compression backup

  • February 11, 2013 at 10:34 am

    #268482

    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?

  • February 11, 2013 at 10:39 am

    #1586676

    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.

    .

  • February 11, 2013 at 11:07 am

    #1586693

    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

  • February 11, 2013 at 12:28 pm

    #1586711

    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.

    .

  • February 11, 2013 at 12:54 pm

    #1586721

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

  • February 11, 2013 at 7:49 pm

    #1586807

    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

  • February 11, 2013 at 8:59 pm

    #1586821

    notice it's the same person asking. ha.

    .

  • February 12, 2013 at 4:35 am

    #1586932

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 12, 2013 at 9:48 am

    #1587086

    Thanks much,

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

    Thanks

  • February 12, 2013 at 9:56 am

    #1587094

    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

  • May 9, 2016 at 9:59 am

    #1877526

    Hello, Bill

    I have some queries regarding backup and restore strategies.

    I want to take Backup and Restore of Large Database approx. 60 to 70 GB and it is critical database

    Backup schedule approx. i decided,

    - Full Backup every day

    - Differential Backup every 2 or 3 hour

    - Transaction-log Backup every 15 minute

    Is this correct approach? if not then please suggest better option?

    Which Recovery model is best suits ? (Simple, Full, Bulk-logged)

    In case any of backup file(full backup file, differential backup file or log backup) has corrupted then what are the options to recover database quickly with minimum data loss?

    THANKS in Advance...:-)

  • May 10, 2016 at 7:20 pm

    #1878022

    Why did you post in an old, unrelated thread, rather than start a new thread on your topic ??

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply