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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.