Post reply

compress backup

  • February 5, 2013 at 12:25 pm

    #268127

    We are using SQL server 2008 R2 Standard edition. Currently we are OK for disk spaces for backup.

    But I see in database settings property, I can set up default to compress backup.

    Is it good to choose it for now in the case we have enough space on the disk?

    Any advantages and disadvantages of enable that?

    Thanks

  • February 5, 2013 at 12:52 pm

    #1584566

    It's really a preference for the most part.

    If it were me, I'd use compression in my backups from the start.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • February 5, 2013 at 1:06 pm

    #1584572

    Thanks, it seems there is some downside when doing compression backup, it uses more of resources than regular backup

  • February 5, 2013 at 1:09 pm

    #1584574

    sqlfriends (2/5/2013)

    Thanks, it seems there is some downside when doing compression backup, it uses more of resources than regular backup

    Not necessarily. It reduces the disk and/or network IO, so it is likely to use fewer resources, even for CPU.

    I turn on backup compression for full, differential, and transaction log backups, unless I know there it a good reason not to.

    If you have a database with a lot of incompressible images, like .PDF files, or if you are using transparent data encryption, you should leave compression off.

  • February 5, 2013 at 1:13 pm

    #1584576

    Thanks that is good to know. I also read this http://blog.sqlauthority.com/2008/07/03/sql-server-2008-introduction-to-new-feature-of-backup-compression/

    It sounds good to enable it when I do upgrades too.

  • February 5, 2013 at 4:12 pm

    #1584653

    Unless you're in a situation where you are already CPU bound, I would absolutely enable compression on backups. Usually, most systems are IO bound. If you can reduce IO, it's a good thing.

    "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

Viewing 6 posts - 1 through 5 (of 5 total)

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