Link to home
Start Free TrialLog in
Avatar of mmccy
mmccy

asked on

undo the delete sql in MS Server

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 !
Avatar of William Elliott
William Elliott
Flag of United States of America image

this is why you test in a 'test' environment or backup first.

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

or
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.
ASKER CERTIFIED SOLUTION
Avatar of William Elliott
William Elliott
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)