Confused about backups and recovery models

  • I've been reading several articles trying to wrap my mind around things like...

    What does setting a recovery model actually DO? Does it just affect the transaction logs (truncated in simple, retained until backed up in full, etc.)?

    Are transaction logs included in a backup? (.bak file)

    I know this is DBA 101, and I'm trying to self-server by reading posted articles, but I'm having trouble wrapping my mind around it. Any lifeline would be appreciated!

  • Very briefly:

    Full -

    allows you to restore to a point in time - say 4 seconds past 5 this afternoon when someone accidentally truncated the X table in live instead of Dev.

    if you're taking your t-log backups remotely (remotely can mean to dedicated backup storage or a remote datacentre) you can define a Recovery Point Objective. Ie, this is the Max amount of minutes of data I can lose if I lose a box / database. If you take them every 5 minutes, in principle you're talking about a max loss of 5 minutes worth of data

    This, you'd generally want for OLTP

    Simple

    no point in time restore - only from the last full backup. All changes since then are lost.

    Common use-case would be reporting databases after your nightly ETL process

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • Here's a good article by Gail Shaw that discusses Recovery Model internals and has some really good links about what's going on under the hood (if that's what you're looking for).

    Recovery Models[/url]

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Thank you both for the replies. I will check out that link.

  • andrew gothard (8/17/2016)


    Very briefly:

    Full -

    allows you to restore to a point in time - say 4 seconds past 5 this afternoon when someone accidentally truncated the X table in live instead of Dev.

    if you're taking your t-log backups remotely (remotely can mean to dedicated backup storage or a remote datacentre) you can define a Recovery Point Objective. Ie, this is the Max amount of minutes of data I can lose if I lose a box / database. If you take them every 5 minutes, in principle you're talking about a max loss of 5 minutes worth of data

    This, you'd generally want for OLTP

    Simple

    no point in time restore - only from the last full or differential backup. All changes since then are lost.

    Common use-case would be reporting databases after your nightly ETL process

    I just added something you missed.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • You can also take the help of this link to know more about recovery models: http://sqltechtips.blogspot.com/2015/11/-recovery-models.html

  • That blog post is wrong in a few places.

    This, for example, is wrong.

    Bulk logged recovery model performs much better than simple logged recovery model because it uses minimal log space and minimal logging is required.

    So is this

    Main draw back of the full recovery model is that, you have to setup a regular transaction log backup to avoid the growth of transaction log files, otherwise the log files keep growing until the next full backup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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