SQL Server point in time recovery

By:   |   Updated: 2022-12-19   |   Comments (6)   |   Related: > Restore


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.

recoverymodel

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:

Msg 4341, Level 16, State 1, Line 4
This log backup contains bulk-logged changes. It cannot be used to stop at an arbitrary point in time.
Msg 4338, Level 16, State 1, Line 4
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
Msg 3013, Level 16, State 1, Line 4
RESTORE LOG is terminating abnormally.

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.

fullrestore2

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

fullrestore2

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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2022-12-19

Comments For This Article




Tuesday, March 31, 2015 - 2:49:13 PM - Srini Back To Top (36783)

Thank you very much for your valuable article. It's very very useful to all DBAs. Once again Thanks for your valuable time that you spent for delivering useful articles.

 

Srini

DBA

Sunday, July 20, 2014 - 2:06:28 PM - Bipin Singh Back To Top (32796)

Thanks for the excellent post. I was trying this since a long time but your post khelped me a lot.

Sunday, October 6, 2013 - 3:19:48 PM - Narasimha Rao Back To Top (27056)

Dear Mr. Greg,

This article is very nice. I feel you can help me to get my data back. Kindly help me.

I have a database into which I have downloaded employees attendance data into "CHECKINOUT" table (production equivalent temporary db in my laptop) from fingerprint machines using the default software provided by vendor. That software shown 741 records were downloaded. This happened at 05-Oct-2013 13:06:07. I made sure the number of records in the database are also same using the select statment. So, I have deleted the data from fingerprint machines using the same software at at 05-Oct-2013 13:07:22. I left the PC as it is and went away for 5 mins. What triggered the later transactions I do not know but at 05-Oct-2013 13:09:50, automatically "CHECKINOUT" table was altered, all 741 records were updated, another few of the tables in database were altered, "CHECKINOUT" table was dropped and recreated. All these happened

I was not aware of all these happened in my absense. I took backup of database copied to pendrive (to use it on production to appened all above 741 records to production database) When I restored this backup on production server, I realised there is no data in "CHECKINOUT" table. I have observed there is no data in my laptop as well.

When I run trial version of APEX SQL Log on db in my laptop, I came to know about all above transactions that happened automatically in my absense. But unfortunately APEX is giving the information of only commands executed, but cannot show any data for all above INSERT and UPDATE statments.

APEX SQL Recover trial version is recovering data, but I neither I can afford to spend $1500 nor convince my manager to buy real version.

I do not have any transactional backups of this database.

Is there any way to get the deleted data back that caused by drop table command.

Thank you very much for your help in advance.

Thursday, August 22, 2013 - 9:53:26 AM - Rupesh Mishra Back To Top (26432)

Hi Deepak,

Tail log backup may be required in case if the database went down between two log backup schedules. In that case, we will need to restore the logs sequentially of course with no recovery and at last will restore the tail log backup with recovery that will bring the database in good state till the last committed transaction. Hope this helps.

Thank you

Rupesh

Sunday, May 20, 2012 - 6:54:56 AM - nagaraju Back To Top (17556)

Hii can u provide me upadates of mssql server

really its pretty good stuff in sql server central

 

 

regards

nagaraju

Sunday, May 13, 2012 - 5:56:54 AM - Deepak Chaurasia Back To Top (17445)

hi Greg

what about " tail log back up" , what i believe is we must have take tail log backup first and leave the database in restoring mode. Then proceed for full,diff and tran log.. Could you confirm if this is the best practice?

It would be greate if you give more details about tail log backup and the order we should proceed as best practice.

Thanks,

Deepak














get free sql tips
agree to terms