Backup to Recovery

Question

Backup to Recovery

Steve Jones - SSC Editor

SSC Guru

Points: 736494
More actions
November 7, 2018 at 9:23 pm

#77290

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

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

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

Stewart "Arturius" Campbell SSC Guru Points: 72634 More actions · Answer 1

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 Default port Points: 1424 More actions · Answer 2

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

edwardwill SSCertifiable Points: 5856 More actions · Answer 3

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 SSChampion Points: 10010 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 5

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.

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

edwardwill SSCertifiable Points: 5856 More actions · Answer 6

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: 1004410 More actions · Answer 7

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.

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

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 8

good to know
thanks

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

George Vobr SSChampion Points: 10010 More actions · Answer 9

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 Default port Points: 1487 More actions · Answer 10

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: 5637 More actions · Answer 11

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