Backup to Recovery

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

  • 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”

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

  • 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!

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • good to know
    thanks

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

  • 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)".

  • 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.

  • 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