August 7, 2012 at 7:04 am
Hi all
I am trying to find out the exact time some data was deleted from a table such that I can do a point in time recovery. I know roughly what time it was but would like to know exactly so I can do the restore as close as possible to the correct time.
Could anyone advise on the best way to find when the data was deleted?
Thanks
August 7, 2012 at 7:25 am
There is no way to know exactly, unless a delete trigger was used to record the deletion.
August 7, 2012 at 7:27 am
Unless you have an audit table or a trace running, you wont be able to find the exact time.
The best thing to do would be to restore to atleast 10 minutes before you know roughly the data was deleted, then issue RESTORE LOG WITH STANDBY, which will allow you to query the DB, but then also allow you to apply the log again in say 1 minute increments.
SO in pseudo it would go something like
RESTORE DATABASE WITH NORECOVERY
RESTORE LOG WITH STANDBY
Check for data
RESTORE LOG WITH STANDBY
Check for data
Repeat until data deleted
Record time data deleted.
RESTORE DATABSE WITH NORECOVERY
RESTORE LOG WITH STANDBY, STOPAT timedatadeleted
August 7, 2012 at 7:28 am
ok cool thanks, that is what I was roughly going to do so thanks for confirming.
does the same go for updates?
August 7, 2012 at 7:29 am
Yes, unless you have some form of auditing, you wont be able to find out the exact time anything happened.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply