asked
How to recover to a specific point in time?
I’ve a basic concept question with regards to Sql server backups for the FULL recovery model. I want to be able to restore to a point in time right up to the next FULL database backup. If my last transaction log backup occurs at 6:30 pm which is then followed by a Full DB backup at 7:00 pm can I restore to a point in time between 6:30 pm and 7:00 pm using the Full DB backup and stop-at? If not how is this accomplished in sql server with a Full recovery model? Many thanks.
take a log backup immediately before the full backup and then immediately after, these may then be used with all the other previous log backups and the previous full to restore the database to any point in time up to and through the full backup window Regards Perry
You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
You won't be able to restore to a point between the full backup and the last log backup. You'd need to take another log backup. A restore of a full backup restores everything in that backup. There is no stopat (although you can issue the command without error, it's ignored). To get a restore before the full, you'd have to go the last full (plus any differentials) and then apply log backups. If you don't have one for the time period covered, take an additional log backup before you start the restore process, or, the data is lost. I wrote an article on this over at [SQL Server Central][1]. It's a little old, but still accurate. [1]:
http://www.sqlservercentral.com/articles/1914/
Unfortunately the answer to your question is no, and it has to do with the way that database and T-Log dumps occur. Starting with the Database dump, once the command is issued to perform a dump the first thing to happen is that all of the uncommitted transactions to commit, forcing a checkpoint writing all of the data to the database that it can. The database dump is then performed and after all transactions that have been written to the T-Log since the start of the dump is then written to the database dump. In this way all of the data for the dump is there. This also voids out all previous T-Log dumps. (T-Log dumps belong to a DB dump. Now you start your T-Log dumps by initializing a new T-Log dump. Then, lets say that you are doing T-Log dumps every 15 minutes. You performed your DB dump at 6:00 PM and it finished and initialized a new T-Log dump at 7 PM. By 8 PM you should have 4 T-Log dumps performed (one every 15 minutes). At 8:10 you discover that there is a problem with the database, and you need to restore but you don't want to lose any data. You restore the database and the 4 T-Log dumps which cover you up to 8:00 PM. You have lost everything from 8 - 8:10 as the T-Log dumps occur every 15 minutes. Based upon this, one thing you should do is to determine how much data that your company can afford to lose and to adjust your T-Log dumps accordingly. However, doing a T-Log dump every minute could be a disaster, using up resources and overburdening the server. You need to play with this. We are a grocery store chain and we do our T-Log dumps every 15 minutes. Many banks perform T-Log dumps between 5 and 10 minutes. Data isn't really lost as there are usually alternative ways to recover. We do 15 minute dumps to make life easier on the occasion that there is a catastrophe.
21 People are following this question.
