The point-in-time restore is a feature of SQL Server databases that allows you to recover an exact state of your database that is specific to an exact point in time.

This option is available ever since the logging for this action has been activated (transaction log).

This article is a continuation of a previous article where we demonstrated how to do such a restore using SSMS.

Before we begin, you should remember that a specific time is always restored from a transaction log backup. Thus, in all RESTORE LOG statements that will be run in the restore process, you will have to specify the target time or the transaction (LSN) with the same STOPATclause.

Like we’ve said in our previous article, point-in-time restore operations are available only for databases that are under the FULL recovery model.

But just before you do the restore to a point in time, you will have to restore the FULL database backup, which has been created earlier than the point in time you want to restore to.

That FULL backup can be as old as you like, provided that you will restore all of the subsequent transactional logs since that backup to and including the transaction log that has the point in time you want to restore to.

In order to identify which database backup you should be restored, you can optionally use the WITH STOPAT clause with your standard RESTORE DATABASE command in order to raise an error if the backup is too recent for the time you are trying to restore to.

Complete data is always restored from your backup even if the file does contain the time you are targeting.

Now, to refresh our memories regarding the format of the query we should use:

RESTORE LOG name_of_database 
FROM -backup_device- 
WITH STOPAT = 'time', RECOVERY

The point of recovery is the last committed transaction that happened at the time or before the time you specified in the STOPAT clause.

In order to restore any modifications that have been made before a specific time, use the STOPAT = 'time' clause for each backup file you are going to restore. This is how you will make sure that you won’t ever go past the target time during your restore process.

How to make a point in time restore with T-SQL

Now that we have an overview of what happens and what we should do, so it is successful on the first run, we will get right to the steps and queries you should be running for doing the point-in-time restore:

1. The first step is to run the RESTORE DATABASEwith the NORECOVERY clause.

   RESTORE DATABSE testDB
   FROM 'Z:\backups\testDB_FULL.bak'
   WITH NORECOVERY

   In case there are partial restores in a sequence that exclude any filegroups, then the point-in-time restore is not going to be supported.

   Still, you have the option of forcing the restore process to continue, but the FILESTREAM filegroups that are not present in your RESTOREquery will never be restored.

   If you do decide to continue even though errors are encountered, you should use the CONTINUE_AFTER_ERROR option along with the STOPAT, STOPBEFOREMARK or STOPATMARKoptions which you have to use in your next RESTORE LOG queries.

   Also, if you set the CONTINUE_AFTER_ERROR statement, then the partial restore sequence will succeed and the FILESTREAM filegroup will not be recoverable.

2. The second step is to restore the last differential backup of the database if there are any, and while doing so using the NORECOVERY option.

   RESTORE DATABASE testDB
   FROM 'Z:\backups\testDB_diff.bak'
   WITH NORECOVERY

3. The third step is to restore all of the transaction log backups in the same order as they were created.

   RESTORE LOG testDB
   FROM 'Z:\backups\testDB_log.trn'
   WITH FILE = 3, NORECOVERY, STOPAT = 'Apr 24, 2012 12:00 PM'
   
   RESTORE LOG testDB
   FROM 'Z:\backups\testDB_log.trn'
   WITH FILE = 4, NORECOVERY, STOPAT = 'Apr 24, 2012 12:00 PM'

   If the transaction log does not have the target time, a warning will be prompted and the database will remain unrecovered.

4. And now, the last step is to run the query that will restore the database with RECOVERY.

   RESTORE DATABASE testDB WITH RECOVERY;