I need to recover deleted data from an SQL Server 2000 transaction log file. The Log file is 3.3GB. I tried the usual SQL Log Rescue but it keeps crashing with outOfMemory exception. I also tried it on different machines with the same error.

I tried ApexSQL Recover, seems to work, but it's a trial so only every 10th row is shown. I may have no choice but to shell out 999.00 for this tool, if I cannot find a cheaper solution.

Any ideas?

EDIT - If I try to restore from backup and then replay my log file, can I restore the backup to a different database (new) on the same server?

  • Sorry but I think you're SOL. Had a similar scenario, but because we used Log Shipping at 15-minute intervals, we were able to recover the deleted rows within that 15 minute window. – Mark Henderson Feb 17 '10 at 4:22
  • 1
    And this is a perfect example of why SQL should have a freakin standard where it won't execute a DELETE command unless a WHERE clause is executed (even if it's WHERE 'a'='a', because at least that forces the user to admit they're being destructive) – Mark Henderson Feb 17 '10 at 4:23
  • 4
    @Farseeker: I'm sorry, but I don't agree with you. I think the standard should be in the SQL script writer, not in the language. If anyone writes a DELETE statement without a WHERE clause more than once, it's their own fault. – user3914 Feb 17 '10 at 11:25
  • @Randolph, and if someone falls asleep at their car and runs off the road, it's also their fault if they cross onto the other side of the road and kill someone coming the other way. That doesn't stop councils from (rightly) putting in road barriers to limit the damage done in high speed areas (motorways) when something like this happens. What if the cat walks in the keyboard and presses F5 before you've finished writing your query? Accidents happen. Regularly. – Mark Henderson Feb 17 '10 at 20:48
  • @Farseeker MS protects users in this way when it needs to. i.e. application protection in the GUI for end users. SQL Server is not for end users. If someone shouldn't be deleting records DENY that user delete permission & make them use a sproc with parameters – Nick Kavadias Feb 17 '10 at 23:49

When did the deletion occur? do you have log backups for the period? Can you restore the entire database to a known restore point? Have other transactions occurred in the database that you need?

If other transactions have occurred (which is a bit more complicated) restore your last full back of the db to a new location & apply the transaction logs in order. Use the STOPAT clause to the point before the data was deleted and bring the database online.

You should now the deleted data in your db. You can now generate insert statements to insert missing records back into your live database. If the schema is complicated (i.e. more then a few isolated tables) a tool like RedGate's Data Compare will be useful. There's a full functioning trial you can install to do a one-off compare between your recovered db and production.

EDIT: Yes, restore the database with a different database name and use the MOVE option to specifiy a different physical location for data files if using T-SQL. In EM in the options tab change the restore as filenames.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.