?
Solved

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

Posted on 2011-03-11
4
Medium Priority
?
461 Views
Last Modified: 2012-05-11

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
0
Comment
Question by:peturgretars
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 800 total points
ID: 35112616
I would definitely restore the db to ANOTHER server or at least another DB name prior to 14:00 if possible as you already notice growth in the t-log at 14:00 so it's likely the log has deletes already and then import data from that server.database into your live one.

Another way would be to try get Log Explorer or other tools that may help recover deleted but logged data.
0
 
LVL 1

Author Comment

by:peturgretars
ID: 35115871
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!
0
 
LVL 2

Accepted Solution

by:
Umesh_Madap earned 1200 total points
ID: 35115966
Hi ...

please do the things on 1 st priority.

1) copy all the backup's to some location so that i will not be deleted or overwritten.

2) if you have the test server then restore the old backup(full bacckup) on the test server and try to restore the t-log backups once by one by using the script, if u know at what time the records has been deleted then use the stop at option

ex:-
RESTORE LOG DBUtil
FROM DISK = 'C:\Backup\dbutil_log_3.trn'
WITH RECOVERY,
STOPAT = 'Apr 23, 2007 05:31:00 PM'

once u have confirmed that all the recodes are available u can export the deleted records  by import /export option.

Note:-

 before u start any activities on prod server please backup the production DB and keep in some safe location.


please this it will defiantly workout for u.

good luck...
 
0
 
LVL 9

Expert Comment

by:mimran18
ID: 37052104
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

762 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