SQL Server point in time recovery

Problem

At some point, a detrimental command will probably be issued against one of your databases and you will need to recover the lost data. There are several actions that you might be able to take to recover the lost data, but what option makes the most sense. One option that SQL Server offers is the ability to do point in time restores of your data in order to restore your database back to the point right before that detrimental command was issued. So what are the steps in order to do a point in time recovery?

Solution

The ability to restore to any point in time is key when you want to recover your data prior to the transaction that caused the problem. In order to for this functionality to work there are a few things that need to be done.

Recovery Model

To have the ability to restore to a point in time, your database needs to be in either the Full or Bulk-Logged recovery model. If your database is in the Simple recovery model this functionality will not work. To set your database to the Full or Bulk-Logged recovery model you can do this via the GUI or using a query window:

T-SQL commands

Issue one of these command to change the recovery model for your database.

• ALTER DATABASE databaseName SET RECOVERY BULK_LOGGED
• ALTER DATABASE databaseName SET RECOVERY FULL

SQL Server Management Studio

To set the recovery model for your database via the GUI, right click on the database name and select Properties.

If the database is set to the Full recovery model you have the ability to restore to a point in time for all of your transaction log backups. For the Bulk-Logged recovery model if you have any bulk logged operations in the particular transaction log you are trying to use for a point in time recovery the restore process will fail and you will get an error such as the one below. This is because bulk logged transactions such as create index and bulk insert operations, may be minimally logged and since all of the data is not in the transaction log for these operations a point in time recovery is not possible.

If you try to use point in time recovery and there are bulk logged operations you will get these error messages:

Restore Process

To restore the database you can either do this from a query window or from the SQL Server management tools.

These are the commands to restore the database and one transaction log to Dec 19, 2022 11:26:05 AM. The first full backup restore is done using the NORECOVERY option, so the database stays in a loading state and additional backups can be restored. The second restore command restores the transaction log to the point in time = ‘Dec 19, 2022 11:26:05 AM’. In addition, we are using the RECOVERY option to put the database back into a useable state after the restore of the transaction log..

RESTORE DATABASE DBUtil FROM DISK = 'C:\Backup\dbutil.bak' WITH NORECOVERY 
RESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log.trn' WITH RECOVERY, STOPAT = 'Dec 19, 2022 11:26:05 AM' 

If we needed to restore additional transaction log backups this can be done by using the NORECOVERY option for each of the other transaction logs and the STOPAT parameter would only be used for the very last file to be restored.

RESTORE DATABASE DBUtil FROM DISK = 'C:\Backup\dbutil.bak' WITH NORECOVERY 
RESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log_1.trn' WITH NORECOVERY 
RESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log_2.trn' WITH NORECOVERY
RESTORE LOG DBUtil FROM DISK = 'C:\Backup\dbutil_log_3.trn' WITH RECOVERY, STOPAT = 'Dec 19, 2022 11:26:05 AM' 

To do this using the SQL Server Management Studio GUI you would do this as shown below. You first need to select all of the backup files that you want to restore and then use the “Timeline” option to restore to a particular point in time.

On this screen you can pick the point in time for the restore.

That is pretty much all there is to do for a point in time restore. The only other thing you need to figure out is what time to restore the transaction log to. In some instances you may know the exact time something went wrong, but in other cases you may not know. This is where transaction log reader tools come in handy. These tools will allow you to see all of the transactions and the time they occurred, so you can determine the exact time to recover your database.

Next Steps

• If you want to use point in time recovery make sure your database recovery model is set to Full or Bulk-Logged
• If you use the Bulk-Logged recovery model, remember that you can not do a point in time restore if there are any bulk logged operations in the transaction log you are trying to restore.
• In order to find the exact point in time to recover you can use a log reading tool to determine exactly when the transaction occurred.
  • ApexSQL’s SQL Log