Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server point in time recovery


By:   |   Read Comments (6)   |   Related Tips: More > Restore

Attend these FREE MSSQLTips webcasts >> click to register


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

Enterprise Manager or 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:

Error from query analyzer

Error from Enterprise Manager

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 Apr 23, 2007 05:31:00 PM.  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 = 'Apr 23, 2007 05:31:00 PM'.  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 = 'Apr 23, 2007 05:31:00 PM'

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 = 'Apr 23, 2007 05:31:00 PM'

To do this using the GUI tools 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 "Point in time restore" option to restore to a particular point in time.

SQL Server 2000 - Enterprise Manager

SQL Server 2005 - Management Studio

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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

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


Sunday, October 06, 2013 - 3:19:48 PM - Narasimha Rao Back To Top

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

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

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

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


Learn more about SQL Server tools