Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2006-06-06 : 00:06:12
|
| I expect this is common knowledge!!, but I discovered by accident that the following is valid:RESTORE DATABASE MyDatabase WITH RECOVERY(i.e. no filename specified to restore).So it is possible to do a full backup restore, followed by a series of TLogs, all of which specify NORECOVERY and then just do a final WITH RECOVERY (without having to specify a backup filename).I've had accidents where I specified RECOVERY instead of NORECOVERY and had to start-over the restore process!! so I'm thinking of changing my procedure to always use NORECOVERY and then do a final WITH RECOVERY step.Just wanted to check if anyone sees any pitfalls with this approach?ThanksKristen |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-06-06 : 03:40:46
|
| That's what I would do. Restore .. with recovery has come in handy a few times.-------Moo. :) |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-06 : 03:57:16
|
i encountered this once but never did try it out as routine, guess I wasn't sure if it was consistent or there won't be any changes to it's behaviour by succeeding patchessince it's a script to restore, i always leave out the last file in the loop then do the recovery there...  --------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-06 : 04:18:53
|
| "since it's a script to restore ..."My script generates code for the Full restore and then "optional code" for any DIFF / TLogs, and by looks like:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300so I have to make a conscious change to disable the RECOVERY on the FULL and enable it on the last Tlog.I'm now thinking to always use NORECOVERY on everything, and then do a final RESTORE ... WITH RECOVERY when I'm sure I've done all that I intended to. Actually I suppose if I just changed my script to Comment Out the RECOVERY option, and default to the NORECOVERY option I've got it covered - I can comment it in, or I can rub the RESTORE ... WITH RECOVERY as an extra step.Kristen |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-06-06 : 04:39:52
|
| ah i see it...your generator gives you each file to restore...can it detect if the last file is the last in the filelist?then you can apply the recovery there--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-06 : 04:47:59
|
| In a disaster-recovery that would be fine, but most of the time we are restoring in Dev. and want to "just restore last full" or "Up to 5 minutes ago when the damage happened!", so we hand-edit the script accordingly, and then I forget to comment out the RECOVERY stuff and waste time starting-over! (and also I have: juniors "I just ran the script" and then realised they have to start over, so a more fail-safe script seems prudent.Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-06-06 : 17:56:21
|
| That's what we do.RESTORE DATABASE ... WITH RECOVERYAfter applying a series of full + diff + log WITH NORECOVERY restores.It's always the last step.rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-06-07 : 01:46:24
|
| Thanks chaps |
|
|
|
|
|
|
|
|
RESTORE DATABASE MyDatabase WITH RECOVERY