Post reply

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

  • April 1, 2014 at 7:44 am

    #285045

    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?

  • April 1, 2014 at 7:48 am

    #1702036

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

    John

  • April 1, 2014 at 7:56 am

    #1702045

    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?

  • April 1, 2014 at 8:02 am

    #1702051

    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

  • April 1, 2014 at 8:05 am

    #1702056

    Thanks again!

  • April 1, 2014 at 8:12 am

    #1702061

    Oops - double post!

  • April 1, 2014 at 10:27 am

    #1702132

    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

  • April 1, 2014 at 11:06 am

    #1702154

    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.

  • April 2, 2014 at 2:04 am

    #1702353

    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