September 26, 2007 at 10:31 am
I do full back every night around 10PM, and Trans log backup every 4 hour: 12AM, 4AM and so on...
Here’s the scenario
I came to know that corruption has occurred at 1PM, the last Trans log backup I’ve from 12PM and next backup is at 4PM, and I cannot wait till 4PM, is it possible to restore in this case?
September 26, 2007 at 10:53 am
If you can back up the log, you should be able to apply the last full backup and then successive log backups up to the point in time right before corruption including the most recent log backup. Look at point-in-time restores in books online to get the proper syntax.
If you can't backup the log you may not be able to get any further than 12PM.
September 26, 2007 at 11:22 am
But If the user db .mdf/ldf files gets corrupted at 1PM, is it possible in this case (point in time)?
September 27, 2007 at 10:05 am
As long as your transaction log is not corrupted, you should be able to do a point in time restore to just before the corruption occurs.
In a nutshell, here's what to do:
1) take a backup of the transaction log so you've got a backup that's completely up-to-date
2) restore everything to 12pm, using WITH STOPAT and WITH STANDBY in the RESTORE command
3) slowly move forward through your final transaction log, say 30 seconds at a time (using WITH STOPAT) until you find the 30 second window where corruption occurs
4) start again - restoring everything to the 30 second interval before the corruption occurs
5) repeat with smaller and smaller increments through the log until you get to the point just before the corruption occurs (if you really need all possible amounts of data recovered)
6) when you've found the point at which you're happy to finish restoring, just do a RESTORE DATABASE blah WITH RECOVERY and it will finish
There's a bit more to it than that but it's not a process I can completely describe in a forum post to you. You may want to call Product Support for some help. Checkout this article my wife wrote for a more complete description - http://www.sqlmag.com/Articles/Index.cfm?ArticleID=39657&pg=5
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
October 6, 2007 at 9:38 am
yes even if the database corrupted the point in time restore is possible.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply