August 26, 2010 at 3:06 am
Hi All,
I've just been testing my backups and thought about a problem that could occur and was wondering how to recover from it.
Database is in FULL Recovery with FULL database backup happening 24:00pm every night. Transaction log backups occur every 2 hours.
The following occures:
1. Sunday night, FULL backup occurrs. There after Log backups occur every 2hours.
2. Monday afternoon (after 4 pm) someone runs an update on a table without specifying a where clause.
3. The problem is not noticed until Tuesday morning.
To recover from the Update blunder I would restore Sunday night's FULL backup (as a different database name) and leave in Restoring state. I would then start restoring the Transaction Logs also leaving in REstoring state. I know that the blunder happened sometime after 4pm, so when I get to the log backup that occured at 4pm, I would need to restore to a point. My question is, without knowing the exact time the Update statement was run, how can I restore to a point - the last transaction that occured before the Update statement that ran and updated the entire table.
I was thinking that in that case I would need to have some software that could read the log file so that I could see the transactions and determine the time of the last transaction before the Update. However what if I don't have access to such software.
Your feedback is appreciated
Denesh
August 26, 2010 at 3:25 am
Denesh
There isn't any way of knowing, unless the update statement was king enough to include a GETDATE() value! You could trace all activity using a server-side trace in case any disasters happen, then you could go back through your trace and look for the offending statement. I really wouldn't recommend that approach, though, because of the overheads of gathering and storing such information.
One thing you could do to mitigate the risk of this happening in the first place is to write your database and application so that all access to data is via stored procedures.
John
August 26, 2010 at 3:32 am
I would start by restoring the Monday night backup to see if the update has happened by then, if it had then you will need to restore each t-log and then bring the database up and check if the updates are there.
You should only need to do this 4 times to find out when the update happened, if you script out the process it shouldn't take too long.
There are commerical log readers avaible but they are expensive.
Also make some changes to ensure this wont happen again, try and use Stored Proc as suggested.
August 26, 2010 at 3:35 am
Denesh Naidoo (8/26/2010)
My question is, without knowing the exact time the Update statement was run, how can I restore to a point - the last transaction that occured before the Update statement that ran and updated the entire table.
Restore the last log with Standby and StopAt. Check the database, see if the update has happened. If not, restore just that log again with standby and a slightly later stop at. Repeat until when you check the update has happened. Now you know what's too late, and you can do the restore again (starting from the full) and you know what the stop at time is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2010 at 4:09 am
Thanks to all for the feedback
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply