Will changing the recovery model on the fly prevent ability to restore properly

  • We have a very large import job on a critical database. At the start of the job we set the recovery model to simple and at the end of the job we set it back to full. Will this prevent us from being able to restore the database if a failure happends prior to the next full backup?

  • Yes. Either switch to Bulk Logged instead of Simple, or take a full backup as soon as you switch back to Full.

    John

  • Thank you for your quick response John. I want to be sure I understand. If the database is set to Full recovery model (or Simple) and we change it to Bulk Logged prior to running our import then back to Full (or Simple)...we would need to perform a full backup to ensure recovery integrity? Or did you mean if I was just going from Simple to Bulk Logged then back to Simple it would be fine?

  • If Simple is involved anywhere then your backup log chain is going to be broken.

    Full -> Simple -> Full: take full backup immediately after reverting to Full

    Full -> Bulk Logged -> Full : no extra backup required. Note that certain operations may make point-in-time recovery during the time you're in Bulk Logged mode impossible.

    Search this site (or the internet) for articles on "Managing Transaction Logs" for more information.

    John

  • Thanks again!

  • Oops - double post!

  • The mentioned article: http://www.sqlservercentral.com/articles/Administration/64582/

    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
  • John Mitchell-245523 (4/1/2014)


    ...

    Full -> Simple -> Full: take full backup immediately after reverting to Full

    ...

    John

    IIRC a differential backup after switching back to Full Recovery model works as well.

  • Lynn Pettis (4/1/2014)


    IIRC a differential backup after switching back to Full Recovery model works as well.

    Yes, good point. Thanks Lynn.

    John

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

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