How to Set Recovered DB back to restoring mode
-
Hi, I am in the middle of restoring a DB. I restored the full backup and started restoring TRN files. I made a mistake and selected WITH RECOVERY option when restoring a transaction log, but I still have more transaction logs to recover. Is there a way to use TSQL to set the DB back to a Restoring status and continue on? OR, is there a way to use the gui to continue restoring transaction logs? I used the gui instead of TSQL because it seemed safer (!).
My idea is use gui, DB > tasks > restore > select the database choice > don't overwrite > options change to NO RECOVERY option and try it. I'm afraid to move ahead without seeing if that would mess up everything. Alternatively, I can go back and just start over again. But it is a big DB (at least it seems big to me 35 gig), and I was hoping to avoid that.
We have a hardware failure on our production system, and I'm recovering to a test server. I am learning the ropes, have a consultant to call for help, have called them and they weren't sure either. I've done searching on this site, google, BOL. At this point, I probably could have just started over, but thought I would ask first. Thanks in advance for ideas.
LP
-
You will need to restart your restore from the beginning. This means overwriting what you have already done.
-
Gotcha. (and thanks) Am doing so now. Another question.
While we were trying to recover from hardware failure, we thought all was well for a while. I took another full backup, and a couple of transaction log backups at different points. I am able to copy the recent transaction logs over from the not fully functioning server, but copying the most recent full backup over from bad server to good place failed in two attempts. What I in the process of doing now is
Restore Full using last full before hardware failure started, Restore with NO RECOVERY (think I learned my lesson) in order oldest to newest for a number of Transaction logs. But I'm uncertain if I can restore any of the most recent transaction logs that occurred AFTER the full backup which I don't have access to right now.
Do you know? thanks in advance.
LP
-
I went ahead and tried it and it did not work. In summary, you can't leap frog over a full backup to capture more transaction logs after starting from an earlier Full backup.
I thought it might be an option because I was sure no data was changed between the time of the last transaction log, a full backup and then another transaction log. But hey, that's what learning is all about.
Thanks for help. We are still dealing with the issue, and probably will have a read only version of the DB for people to view this afternoon.
LP
-
Just an FYI, you cannot restore transaction logs after a different full backup than the particular full backup you are using for your restore.
The reason is because of the Transaction Log Chain. All Transaction Logs are "tagged" in SQL Server with the relevant Full Backup "number" that they occur after.
Say you have 2 full backups, F1 and F2. F1 occurred on the 6th of the month and F2 occurred on the 7th. You also have five TransLogs (TL1-5) which occurred after F1 but before F2. And five TransLogs (TLA-F) which occurred after F2.
If you restore F1 with NO RECOVERY, the only translogs available to you are 1-5. You cannot restore A-F because they are linked directly to the backup F2. And because of this log chain issue, if you lose TransLog 4, you're pretty much screwed if you need TransLog 5. The best you can do is restore F1 and Logs 1-3, and suck up the data loss.
Unless, of course, you bit the bullet, restore F2 and then delete what data you don't want (which is almost always a Bad Idea).
Does this explanation help?
-
Hi,
I assure you you can restore tran logs 'through' a full backup without restoring the intervening full backup itself, otherwise how would log shipping work?
Try it on a test database.
Something must have happened before your second full backup which is not captured in the tran log sequence, thus breaking the chain (possibly the HW failure itself?)
---------------------------------------------------------------------
-
LP,
What is the error message when you try to restore the transaction log in question? Probably somehow you missed some trans as George pointed out. This case you get an error msg like 'The backup with LSN XXXX is too late for this database, try an older backup which contains LSN YYYYYY'.
Brandie,
You were writing about differential backup, not transaction log backup. Transaction log backups aren't based on database backups, they are based on LSN (Logical Serial Number or what), that is transaction log number. The current LSN is included in full backups as well, this way SQL Server can determine if a particular log backup can be applied to it.
-- Erik http://blog.rollback.hu
-
It doesn't make sense to be able to 'skip' the db backup and still restore everything. The only way that makes sense is if the log backup (after the db backup) behaves as if there was no db backup. Is that how it works? If not, I am definitely missing something...
-
Paul,
as I said, give it a try, you will see. As eriK says all the log cares about is the LSN nos are in sequence, and remember a full backup does not truncate the log so this is preserved.
Don't know details of intternals but all a full backup does log wise is put a marker in the log saying it occurred which contains the LSN at that point in time.
---------------------------------------------------------------------
-
A full and differential backup is consistant as of the end of the backup. Both backup enough of the transaction log to ensure the consistancy of the backup for restore purposes.
-
Ok, I read the BOL again, and yes, they say that it does work. They even give an example of it. http://msdn2.microsoft.com/en-us/library/aa173551(SQL.80).aspx
Btw, it works very simple: when you create a full/diff db backup, at the start the current LSN is written, and at the end, the tranlog created during the backup will be appended to the backed up data. So in fact, you can restore tranlog from a full backup - it's quite sick, but works. Give it a try - after you tried that you can restore tranlogs independently of full backups
The tranlog backups also contain the LSNs, and they should continuously cover an interval to give you a chance of the log restore. So you have a log backup from LSN 1000 to 2000 and a full db backup, which btw contains the log from LSN 1435 to 1442. The full db backup doesn't affect the transaction log.Huh, I hope it makes sense...
And no one mentioned, so I do now: there's a restore option: WITH STANDBY. Check it in BOL!
-- Erik http://blog.rollback.hu
-
Leaves the database available for read-only access and so ou can apply future t-log backups.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply