Backup to Recovery

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

    Comments posted to this topic are about the item Backup to Recovery

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71858

    Really interesting question, thanks Steve
    haven't had to do this type of operation in quite some some, so good refresher...

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • Jakub.Janda

    Ten Centuries

    Points: 1352

    I concur, that was an interesting question.
    Even though I answered it wrong 🙂

  • edwardwill

    SSCertifiable

    Points: 5132

    I'm interested to know what real-world scenario this would accommodate (as opposed, for example, to any other possible approaches).

    PS - like the new profile pic!

  • George Vobr

    SSCrazy Eights

    Points: 9220

    Thanks Steve for this interesting question, but your explanation de facto confirms the correctness of answer number 4.

  • Jeff Moden

    SSC Guru

    Points: 996832

    edwardwill - Thursday, November 8, 2018 3:55 AM

    I'm interested to know what real-world scenario this would accommodate (as opposed, for example, to any other possible approaches).

    PS - like the new profile pic!

    There are two situations where I've used this.
    1. Migrations.  We have some fairly large databases and the plan was to keep the old system online for as long as possible.  So.... we did a full backup while everyone was online and, about an hour before the migration time slot was to begin, we took DIF backups.  At the appointed time, we took the Tail Log backups which prevents anyone from using the database and captures all of the latest data in the log file. Then we did our restores.  Saved a huge amount of time doing the backups and saved us from having to do "online merges" of data, which are always a pain.
    2. The MDF failed but the log file is still viable.  It's standard operating procedure that if a database MDF/NDF file goes corruption, you should do a Tail Log backup to capture as much data as possible and take the ailing database into a restoring mode so no one can use it until you've done a proper restore.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • edwardwill

    SSCertifiable

    Points: 5132

    Jeff Moden - Thursday, November 8, 2018 8:03 AM

    edwardwill - Thursday, November 8, 2018 3:55 AM

    I'm interested to know what real-world scenario this would accommodate (as opposed, for example, to any other possible approaches).

    PS - like the new profile pic!

    There are two situations where I've used this.
    1. Migrations.  We have some fairly large databases and the plan was to keep the old system online for as long as possible.  So.... we did a full backup while everyone was online and, about an hour before the migration time slot was to begin, we took DIF backups.  At the appointed time, we took the Tail Log backups which prevents anyone from using the database and captures all of the latest data in the log file. Then we did our restores.  Saved a huge amount of time doing the backups and saved us from having to do "online merges" of data, which are always a pain.
    2. The MDF failed but the log file is still viable.  It's standard operating procedure that if a database MDF/NDF file goes corruption, you should do a Tail Log backup to capture as much data as possible and take the ailing database into a restoring mode so no one can use it until you've done a proper restore.

    Thanks Jeff.  I think I get the explanations, but as a dev rather than a full DBA I've never faced either scenario.

  • Jeff Moden

    SSC Guru

    Points: 996832

    George Vobr - Thursday, November 8, 2018 8:02 AM

    Thanks Steve for this interesting question, but your explanation de facto confirms the correctness of answer number 4.

    The way answer 4 is written, I have to agree even though the question clearly states that you don't want to do a restore from a backup.  You DO have to do a restore but it only requires WITH RECOVERY instead of a restore from a backup and answer 4 is worded in such a fashion as to be correct if you only consider the words in the answer.

    It's also a good example of why I hate taking tests. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Shayn Thomas

    SSCertifiable

    Points: 5626

    good to know
    thanks

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • George Vobr

    SSCrazy Eights

    Points: 9220

    Jeff Moden - Thursday, November 8, 2018 8:47 AM

    George Vobr - Thursday, November 8, 2018 8:02 AM

    Thanks Steve for this interesting question, but your explanation de facto confirms the correctness of answer number 4.

    The way answer 4 is written, I have to agree even though the question clearly states that you don't want to do a restore from a backup.  You DO have to do a restore but it only requires WITH RECOVERY instead of a restore from a backup and answer 4 is worded in such a fashion as to be correct if you only consider the words in the answer.

    It's also a good example of why I hate taking tests. 😉

    Thanks Jeff for this explanation. I also realized it later. Even the QotD Title"Backup to Recovery" suggests it. I consider this question useful, for example,
    in troubleshooting the database problems that you mentioned in the discussion above. See also "Back Up the Transaction Log When the Database Is Damaged (SQL Server)".

  • t.ovod-everett

    Ten Centuries

    Points: 1389

    Jeff Moden - Thursday, November 8, 2018 8:03 AM

    edwardwill - Thursday, November 8, 2018 3:55 AM

    I'm interested to know what real-world scenario this would accommodate (as opposed, for example, to any other possible approaches).

    PS - like the new profile pic!

    There are two situations where I've used this.
    1. Migrations.  We have some fairly large databases and the plan was to keep the old system online for as long as possible.  So.... we did a full backup while everyone was online and, about an hour before the migration time slot was to begin, we took DIF backups.  At the appointed time, we took the Tail Log backups which prevents anyone from using the database and captures all of the latest data in the log file. Then we did our restores.  Saved a huge amount of time doing the backups and saved us from having to do "online merges" of data, which are always a pain.
    2. The MDF failed but the log file is still viable.  It's standard operating procedure that if a database MDF/NDF file goes corruption, you should do a Tail Log backup to capture as much data as possible and take the ailing database into a restoring mode so no one can use it until you've done a proper restore.

    Another scenario is failing over log shipped databases - you take a tail log backup on the current primary, copy it to the current secondary, and restore it on the current secondary with recovery, thus turning the current secondary into the new primary.  Disable log shipping from the old primary/new secondary to the old secondary/new primary.  Then stand up log shipping in the reverse direction.

  • kapfundestanley

    SSCertifiable

    Points: 5632

    Did not know interesting to know.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

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

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