Roll back a delete query

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

I executed a delete query without including in a transaction. now i want to undo the deletion what shoul i do?
please help me!!

Posted 3-May-10 19:41pm

Wanlambok

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sandeep Mewara · Answer 1 · 2010-05-03T20:24:00

Solution 1

Wanlambok wrote:
I executed a delete query without including in a transaction.

I doubt it can be retrieved anymore! Without transaction it is done and gone... If SQL log files or history table in place, you might get some info related to it but not the whole record!

Posted 3-May-10 20:24pm

Sandeep Mewara

Abhinav S · Answer 2 · 2010-05-03T21:07:00

Solution 2

If you take regular backups of the database, you can get your data back using this backup.

Posted 3-May-10 21:07pm

Abhinav S

Comments

Ankur^\m/ 4-May-10 3:13am

Yes, that would be the best option. But in case you don't have the backups, you may need to buy some third party tools to do this.

Ankur^\m/ · Answer 3 · 2010-05-03T21:11:00

Solution 3

There are third-party tools to do this. The tool should be able to go into the transaction logs and view the log entries so you can see what happened. There is one by Red-Gate: SQL Log Rescue[^].
Also check this post: MSDN -undo delete statement...is it possible?[^]

Posted 3-May-10 21:11pm

Ankur^\m/

mimran18 · Answer 4 · 2011-11-22T22:03:00

Server keeps log for each deleted records.You can query these logs via 'fn_dblog' sql server function with the table name "Employee" and it will give you all the deleted records from the log.

SQL

Select [RowLog Contents 0] FROM   sys.fn_dblog(NULL, NULL) WHERE  AllocUnitName = 'dbo.TableName'        AND Context IN ( 'LCX_MARK_AS_GHOST', 'LCX_HEAP' )        AND Operation in ( 'LOP_DELETE_ROWS' )

But this log is in Hex format. and you need to convert this Hex format to your actual data.

Given below is the article will help you to recover the deleted records in the same way defined above.

http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

Muhammad Imran