SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup to Recovery


Backup to Recovery

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (612K reputation)SSC Guru (612K reputation)SSC Guru (612K reputation)SSC Guru (612K reputation)SSC Guru (612K reputation)SSC Guru (612K reputation)SSC Guru (612K reputation)SSC Guru (612K reputation)

Group: Administrators
Points: 612075 Visits: 21189
Comments posted to this topic are about the item Backup to Recovery

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62478 Visits: 8174
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
Jakub.Janda
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1146 Visits: 501
I concur, that was an interesting question.
Even though I answered it wrong :-)
edwardwill
edwardwill
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3873 Visits: 542
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
George Vobr
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7795 Visits: 3857
Thanks Steve for this interesting question, but your explanation de facto confirms the correctness of answer number 4.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)

Group: General Forum Members
Points: 885057 Visits: 47970
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
edwardwill
edwardwill
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3873 Visits: 542
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
Jeff Moden
SSC Guru
SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)SSC Guru (885K reputation)

Group: General Forum Members
Points: 885057 Visits: 47970
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. Wink

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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Shayn Thomas
Shayn Thomas
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4020 Visits: 782
good to know
thanks

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
George Vobr
George Vobr
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7795 Visits: 3857
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. Wink

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search