July 6, 2009 at 1:46 pm
Hello. I have an SQL database in full backup mode. I made an edit to a table that I wanted to undo, so I tried to do a "restore to point in time", using the database as both the source and destination, using 1pm today as the point in time. Instead, it reverted everything back to 1 week ago, erasing all of my work from the last week. Unfortunately I didn't say WITH STANDBY or to restore it to another database. Is there any way I can undo my undo, or is all of that just gone?
July 6, 2009 at 1:58 pm
Do you have a more recent backup than one week ago? If so, what do you have?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 6, 2009 at 2:11 pm
The most recent backup is around a week ago, at the time that it restored to. The transaction log is now only 0.44MB long, so I guess it just undid everything in the entire transaction log, ignoring the "restore to" time I specified, and then deleted the whole transaction log, so everything is completely gone now.
July 6, 2009 at 2:31 pm
Were you doing log backups? Or just full backups?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 6, 2009 at 2:38 pm
The last backup was a full backup. Nevertheless, the database is in full recovery mode, which means that it had a transaction log, and ought to have been able to restore to any point since the last backup, am I correct? Maybe it just didn't work properly because source and destination are the same.
July 6, 2009 at 2:48 pm
yesso (7/6/2009)
The last backup was a full backup. Nevertheless, the database is in full recovery mode, which means that it had a transaction log, and ought to have been able to restore to any point since the last backup, am I correct? Maybe it just didn't work properly because source and destination are the same.
True, IF you had performed a transaction log backup prior to accomplishing the restore. You can't restore from the transaction log itself as that will be overwritten when you restore the full backup with the norecovery option.
July 6, 2009 at 2:59 pm
Oh, I see. Well, thank you both for your responses. I'm just going ahead and redoing everything from the last backup, which is annoying, but not too bad. I documented everything I did so I just have to run those statements again. It's just that it takes up to 2 hours per statement. :doze:
July 6, 2009 at 3:06 pm
You're welcome. I wish we could help more, but I'm glad that you can at least recover from this. I see far too many similar situations where there's just plain no recovering from it at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 6, 2009 at 3:08 pm
At least the data's recoverable. May I suggest that you play around a bit with point-in-time recovery (and check books online).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply