undo the delete sql in MS Server

Posted on 2008-02-10
Medium Priority
Last Modified: 2012-05-05
I accidentally execute a sql and delete some rows of records !
is there any method that can restore the rows of records ?? it is very urgent !!
Thanks !
Question by:mmccy
LVL 19

Expert Comment

by:William Elliott
ID: 20863921
this is why you test in a 'test' environment or backup first.

If you don´t have a transaction scope defined around the command:
ROLLBACK --THis does a rollback
...you can´t. You will need to restore your data from a backup.

Two options:

1) Use a third-party tool that is able to read the transaction log
and construct undo batches from it. Two such products are
Lumigent Log Explorer and Log PI.

2) a) Make a note of when the fatal error occurred. b) Backup the
transaction log. c) Restore the last full backup with norecovery.
d) Apply transaction log dumps with a STOPAT just before the
fatal DELETE was done.
LVL 19

Accepted Solution

William Elliott earned 672 total points
ID: 20863924
LVL 23

Expert Comment

ID: 20863927
yes, there is, it will depend on how your backups are being done though...

Check your maintenance plans and see if any regular backups are scheduled.
Then load the backup into another DB and recover the rows on the relative table.

Your transaction logs, may have a clue, but I doubt it would be of any help since the row updates could have occurred anytime in the past...
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 664 total points
ID: 20863931
Hello mmccy,
u need to use third party tools like Lumigent Log explorer or Red-gate log rescue

Aneesh R
LVL 25

Assisted Solution

jogos earned 664 total points
ID: 20866981
It could be very simple if you know for certain that all the deleted rows are in your last backup.
- restore backup with other name
- test if you can find the exact missing records   between both production and restored db
  select ..... where not exists ....
- do the 'row-recovery'
insert into .... select ..... where not exists ....
when a Identity-column you will need the 'set identity_insert  tablename ON'

Don't forget to check if any cascading delete may have deleted rows in depending tables
LVL 25

Expert Comment

ID: 22447544
Both suggestions are good
- But the 'verry urgent' part off the question could be a problem with the third party tools.
- restore a backup and roll-forward to the last good transactionlog backup , you will have recovered the carelessly deleted records, but do you know wath you trow away on modifications afther that restored backup? Only if you're the only user you can be sure.

So I think my suggestion was also worthwile (even when I won't get any points out of it)

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question