0

I have been handed an issue with a database where last nights backup will not restore. Having spent all morning on this and getting nowhere, I started to wonder about other things I could do to fix my current issue, then I can go back to the backup problem without having to fend off irate users.

I have isolated the individual transaction that has caused the issue, and have its id by using sys.fn_dblog

How can I edit the records in the transaction file to undelete them?

Improve this question
4
  • If I understand your question correctly it's: Forget about the failed restore intro, just focus on how to undo a transaction by using info from the current T-log right? what did the transaction do?
    – Edward Dortland
    Commented Jan 30, 2014 at 13:29
  • transaction was an update that was run without the appropriate where clause, so more records were updated than was intended
    – Matt
    Commented Jan 30, 2014 at 13:31
  • possible duplicate of How do I get back some deleted records?
    – Hannah Vernon ♦
    Commented Feb 3, 2014 at 19:35
  • Apologies if this is slightly off topic for a DBA. I was directed here from the Main Stack overflow site. Question was about how to edit a specific record the transaction file. Edward Dortlands post below helped me to write some code to do exactly what I needed.
    – Matt
    Commented Feb 4, 2014 at 20:54

2 Answers 2

Reset to default
2

Okay, there is a reason why tools that can do this are expensive. It's by no way easy to do. However this blog post can help you.

How to create undo update statements

Since you have found the transaction, you can filter the result by using your transaction id.

Improve this answer
1
  • Thanks. Not the place to discuss cost, but I agree that difficult things come with a premium by necessity. Thanks for the link, I'll pull that apart and try to understand it before running it. Might as well make use of my overtime, being as theres almost 10k rows to repair, they know its not going to be a 5 minute fix :-)
    – Matt
    Commented Jan 30, 2014 at 13:57
0

Well, then better get used to the non pleasent task.

The fact that:

My alternative is to go back to a local copy of this database from last month

should teach you a lesson. Take regular backups AND RESTORE THEM ON A SEPARATE MACHINE. THe later one can have normally a cheap disc subsystem as there will not be any work done on it anyway except restore and database checks.

To my knowledge there is no cheap way to rip out one transaction ouf of a log. Your boss has to decide what he values more, your time of the software.

Improve this answer
1
  • 3
    Its a task I've been landed with, its not a system I normally deal with. All my db's are backed up and backups tested regularly. I've sinply got this because I know more than the person who did all this. (But thats not saying much :$). Still, thanks for confirming my suspicions that I need to book in some overtime..
    – Matt
    Commented Jan 30, 2014 at 13:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.