May 7, 2013 at 11:44 am
We have a production database with size of about 40 GB, it is in full recovery model, and we regualarly make dialy full + diff + log backups.
What happened recently to it is that some table was unexpectedly updated, and I was told to restore this db to another server. They needed to restore it to a point prior to that table update. However, they did not know exact datetime or exact statement that has updated it. What they knew is that values for some records should not be 0.
What I did is very inefficient way for restore. First, I restored just full backup. With recovery. We copied this table to another db with some suffix like _01. Since I restored it with recovery, I can't apply further log backups. I had to restore full again, with replace, + 1 log with recovery. Again we copied it, and I restored full + 2 log with the last one with recovery. If I don't do recovery, I can't query the database. So I repeated this process 4 times, until we got to 0 values.
Is there more efficient way to do it ? Not to repeat same thing multiple times?
Thanks.
May 7, 2013 at 12:04 pm
Look at WITH STANDBY. This will allow you to bring the database into a read only mode which should allow you to read the data from the table into a table in another database and then allow you to restore the next log backup.
Sort of like how log shipping is used for a reporting database.
May 7, 2013 at 12:06 pm
Lynn is correct, use with Standby.
May 7, 2013 at 12:37 pm
Thanks a lot Lynn and Steve. Standby is what I needed. I did not know about that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply