Home / server / Questions / 113657
In Process
Saif Khan
Asked: 2010-02-17 18:48:53 +0800 CST

Recover deleted rows from SQL Server transaction log file

  • 772

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?

sql-server data-recovery

1 Answers

  1. 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.

    • 3