A very good feature for ms sql server backup software is the ability to restore database to a specified point in time.
This feature is very useful in certain circumstances.
For example ,
I have a scheduled job that performs full database backup every night at 23:00 . But today , something happened , I added some records this morning , however I deleted some usefull data accidentally at 2:00 pm.
Now the problem is :
Because I did not performed any backups during the day before 2:00 pm , the most recent backup is backup created at 23:00 last night. If I use this last night backup to restore my database, I'll lost all the data added/changed in the morning , but I want to keep work of this morning.
Thanks for ms sql server point in time recovery feature , I can restore the database to the point in time prior to 14:00 . I just need to do a transaction log backup at current moment (suppose it is 15:00) , and then I create a restore job and specify the restore point in time is 14:00 (maybe 13:59 is better) , of cause I run the restore job as soon as possbile. My work prior to 14:00 today is saved.
Although the ability to restore database to point in time is very useful , not all database can be restored to a specified point in time.
To restore a database to a point in time, the database and the backup must meet the following conditions:
1 . The database must has log files, which means that the database recovery model must be full or bulk-logged . Simple model dose not work because database has no transaction log file in this recovery model.
2 . You must have the a full backup and its continous transaction log backups , (the transaction log backups should cover the specified point in time) . Log backup files are required, if only the full backup file , the database can not be restored to a specified point in time.
3 . The specified time point should be between a full backup and it's latest transaction log creation time. In above example, the point in time you can specify is between 23 o'clock last night to 15:00 today.
Below images demonstrate how to restore database to the specified point in time by using DataBK SQL Server Backup:
In this demo, I have a full backup create on August 13, 11:49 am and a transaction log backup created on August 14 , 12:00 am , and now I want to restore database to the time point August 14 , 11:00 .
1 . In restore job configure window , select the database to be restored :
2 . Click the "Advanced" button to double check if selected database can be restore to the specified point in time (August 14 , 11:00 )
3 . As you can see ,we have two backups , a full backup created on August 13 , 11:49 am , and a latest transaction log backup created on August 14 12:00 , and the time point I want (August 14, 11:00) is in the range of two backup creatation time.
4 . The key step of restore job is specify the point in time for database restore. As following:
5 . run this restore job.