How to use a TRANSACTION log to restore a table or to reverse a transaction - which ever is more practicle

Greetings,

I have a little bit of a problem.  An UPDATE was done on a particular field which affected an entire table instead of designated rows.

My questions:

1)  How can I restore this table to the way it was without doing an entire restore to the database?
2)  Can I use the transaction log to do a roll back on that particular transaction?

Thanks!
John500Asked:
Who is Participating?
 
John500Author Commented:
Apprecitate the feedback.

Once comment in the 2nd article says:

>>  Some have to be installed and running before you need them. I have never used any of them

Do you have any input here?  Is it possible to just search the log file using notepad?  The fundemental question I have is - what is it about the transaction log file that enables a transaction to be reversed?  Since I know exactly what transaction was issued, why can't I just use some sort of logic to go back in time - yes/no ?

0
 
chapmandewCommented:
No, you'd have to use a 3rd party tool to do this. No other way. Apexsql has some really good tools for sucha thing.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
John500Author Commented:
Yes, but do these tools have to be in place before the fact?  Was Apexsql mentioned in that first article you provided or where?
0
 
chapmandewCommented:
I didn't provide the links so I don't know what's in them. I can almost promise you that any tool is going to need to make use of trans log backups to be any use to you though. Apexsql does do some fancy things with recovery, but would be hard if the pages had already been rewritten to.
0
 
John500Author Commented:
Ok, so then - what would be the most logical solution to restoring a tables contents
0
 
chapmandewCommented:
Buy one of the tools.
0
 
grayeCommented:
Yes, it's true... you can NOT restore just a single table.   But, that just means it takes a few more steps!
I'd recommend that you just use the built-in backup feature of SQL Server to perform a restore of the entire database "on another instance".  After the database restore is completed, you can use the log restores to bring the database back to a point in time immediately before the table got whacked.
Then it's be simple to just "copy and paste" the contents of the restored table (on the other instance) to the real database.
When you're done, you can just whack the other instance completely.
0
 
John500Author Commented:
This solution involves a lot of whacking I see - lol

On the serious side, I imagine the Export feature could be used to restore just one table from database to database.  It would be nice if the Export feature could handle just one field of a particular table (can it?).  Maybe the bulk copy command would work to export one field and then do an import on that data.



0
 
grayeCommented:
Well, you can write an update query that updates a single field based  upon the two versions of the table.
... that's the whack!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.