January 8, 2011 at 11:26 am
What is the best approach to bring the DB into a current state w/out bringing in a specific transaction that occurred during a day?
The erroneous transaction occurred @ 10:00A.M the DB was taken offline @ 4 to ensure no additional transactions are committed. I'd like to avoid loosing all work after 10:00 A.M. Instead, "undo" that specific transaction and bring the DB to it's state @ 4.
What are some of the factors I should carefully consider and best approach?
January 8, 2011 at 12:02 pm
there are log reader tools on the market that can generate undo sql for your transaction.
However, you should also take into account what the impact will be for your system. From a technical standpoint as well as from a business standpoint.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 8, 2011 at 12:40 pm
Log reader tools can help, but do you know what the large transaction was? If so, you can write the "reversing" transaction yourself.
January 8, 2011 at 2:07 pm
Yes. The large transaction essentially updated 20000+ record by changing its status and I know exactly which transaction it is from tran log.
Though there are tools out there, I want to ensure the issue does not become more dire than it already is. Apex SQL has been recommended by few sources.
January 9, 2011 at 12:44 pm
If you write the reversing transaction, then you should be OK, but you need to be sure this is what you want.
If you can, I'd write the reversing query in a transaction, check things, then commit if they are OK. So if you updated all the status' to "P" when they should be "a", you could reverse this by
begin transaction
update Payments
set status = "a"
where ID in (list of IDs from bad query)
-- Now stop here and check the data
-- if correct, execute the next line
-- commit
-- if not correct, execute the next line
-- rollback
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply