Backup to Recovery

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714105

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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71160

    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: 1256

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

  • edwardwill

    SSCarpal Tunnel

    Points: 4624

    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: 8855

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

  • Jeff Moden

    SSC Guru

    Points: 993661

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • edwardwill

    SSCarpal Tunnel

    Points: 4624

    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: 993661

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Shayn Thomas

    SSCertifiable

    Points: 5353

    good to know
    thanks

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

  • George Vobr

    SSCrazy Eights

    Points: 8855

    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: 1169

    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: 5623

    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