Link to home
Start Free TrialLog in
Avatar of peturgretars
peturgretarsFlag for Finland

asked on

User deletes all rows from a table. How to restore to a point in time without knowing the exact time of the delete?


Database is in full recovery mode. Transaction log backups are taken on every hour and Full backup nightly at 02:10.

I need to recover an SQL Server 2008 R2 database to a point in time but I am unfortunately unsure to what the point in time is? How should I restore when I am uncertain when the delete occurred?

A user deleted all rows from an important table. I must restore from backups to the point in time just before the delete was done. The issue wasn't discovered before the next day and querying the table indicates that the last inserts after the delete, was around 15:10. Looking at the size of the transaction log backups it looks like the delete was somewhere between 13:00-15:15. At least the transaction log backups at 14:00 and 15:00 were way above normal size. Specially the one that was taken at 15:00.

What should be my recovery strategy here?

Records have been added to the database and I need to keep  the database online.
Is it possible to restore in steps to see where the delete occurred and then once the delete comes in I rollback that action? What is best approach here?

Attached is what I have been thinking of but not sure about the last steps.
What are the best plans of actions here??     Restore-sample.txt
SOLUTION
Avatar of lcohan
lcohan
Flag of Canada 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
Avatar of peturgretars

ASKER

Thanks for this.

Is it possible to do the restore incrementally and see when the delete occurred and then rollback that action or do I just have to do trial and errors to get the time of the delete?

What about the syntax I had in my sample, is that ok?

Can you tell me which tools are available to read the transaction log for SQL Server 2008 R2?

Thanks!
ASKER CERTIFIED 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