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).

Point in time restore

When you write data to your database, at first it will be written in the transaction log and afterward, the data will be moved to the data file, after the transaction has been completed successfully.

When you do a restore of your transaction log, SQL Server will cover once more all of the transactions that are in the transaction log and will roll back or roll forward all of the transactions that it will need in order to get to a usable state it was at the time you specified.

All transactions have a specific number by which they are identified, LSN which stands for the logical sequence number, and a timestamp in order to uniquely identify each transaction both in time and in order of execution. This will obviously help you when you want to restore to a specific point in time, so SQL Server will know at what transaction to stop when doing a restore to a specific time.

But, be aware that if your database is under the BULK-LOGGED model for recovery and there is a minimally logged transaction, like a bulk insert, in the log then you will not be able to make a point in time restore using that log file.

But, in case you have a different backup of your transaction log that was taken later and this one does not have a minimally logged operation, then you can use this log file in order to do your point in time recovery, but the time you are going to reference has to be in this second log file, as the transaction log gets truncated after each backup.

How to do a point-in-time restore

In this next part, we will be going over the options available in SSMS (SQL Server Management Studio) in order to do our point-in-time restore operation.

We will then follow up with how you can do the same thing with T-SQL code.

Using SSMS

Let’s get started with the steps you need to make in SSMS in order to do your point-in-time restore.

• After you’ve connected to your instance server and expanded the Databases folder select the database for which you want to make the restore by going to Tasks-> Restore and choosing either Database (for the entire database to be changed) or Files and Filegroups in order just to change some part of the data in the database.
  
  
• Depending on if the database you are trying to restore is already in RESTORING state, then you can select Transaction Logs.
• On the General page, which should be opened by default, in the To Database field is the name of the database to where the restore is going to be made. But if you want to restore to a new database, just enter its name there.
  
• The location of the restore depends on the fact that you are either restoring data backups or just the transaction log.
• By default, the point in time that the database will be restored to is the Most recent possible.
• In order to restore to a point in time, just press the browsing button and a window like the one below will appear.
  
• Here you should select the date and time to which you want to restore your database.
• In order to set the source and the location of the backup sets which to restore, choose one of the two options:
  • From database: Add a database name in the list box.
  • From device: Click the browsing button and in the Specify Backup box, pick one of the types of devices in the Backup media drop-down.
    
• After you select the time you want to restore to be made to, only backups that are required to restore to that time will be displayed in the grid (Select the backup sets to restore), on the General page.
  
• The next step should be to go to the Options page to set up the advanced options, which is optional, but it is recommended to at least review them.
• If you choose to go to the Options page, then there are options to:
  • Overwrite the existing database (using WITH REPLACE)
  • Preserver the replication settings (using WITH KEEP_REPLICATION)
  • Prompt before restoring each backup
  • Restrict access to the restored database (using WITH RESTRICTED_USER)
    
• Other options available on this page give you the possibility to restore to a new location just by setting a new restore destination for all of the files in the following grid.
• And in the last section of the Options page, the Recovery state section you can determine the state the database will be in after the restore operation has finished.
• Just as clarifications, by selecting the first option you should be restoring all of the necessary backups for this operation on this restore.

As we said, we will go over two options, one detailing how you can restore to a point in time with SSMS and one with T-SQL.

We have a separate article for that as we consider it is best to keep things clear and grouped. You can find the article here.