The point in time restore is a feature of SQL Server databases which 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 to 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 ran in the restore process, you will have to specify the target time or the transaction (LSN) with the same STOPAT clause.
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 restoring, you can optionally use the WITH STOPAT clause with your standard RESTORE DATABSE 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 targetting.
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 commited 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 a 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 succesful on the first run, we will get right to the steps and queries you should be running for doing the point in time restore:
- First step is to run the RESTORE DATABSE with the NORECOVERY clause.
RESTORE DATABSE testDB FROM 'Z:backupstestDB_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 RESTORE query 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 STOPATMARK options 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.
- 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:backupstestDB_diff.bak' WITH NORECOVERY
- 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:backupstestDB_log.trn' WITH FILE = 3, NORECOVERY, STOPAT = 'Apr 24, 2012 12:00 PM' RESTORE LOG testDB FROM 'Z:backupstestDB_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.
- And now, the last step is to run the query that will restore the database with RECOVERY.
RESTORE DATABASE testDB WITH RECOVERY;