solving sql server problems for millions of dbas and developers since 2006

SQL Server point in time recovery

By: Greg Robidoux | Read Comments (2) | Print

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

Related Tips: More

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.

Related Tips: More | Become a paid author

Last Update: 4/26/2007

Share:

Free SQL Server Learning

Comments and Feedback:

Sunday, May 13, 2012 - 5:56:54 AM - Deepak Chaurasia

Read The Tip

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

Sunday, May 20, 2012 - 6:54:56 AM - nagaraju

Read The Tip

Hii can u provide me upadates of mssql server

really its pretty good stuff in sql server central

regards

nagaraju

Sponsor Information

"SQL doctor ROCKS! As soon as I ran it, problems that have been giving me headaches were identified and cured."

Quickly and accurately deploy database changes with Red Gate's SQL Compare - the industry standard comparison and deployment tool.

SQL Servers keeping you up at night? Contact the Edgewood SQL Server DBAs for a Health Check. Prices start at $995.

Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise.

Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now.

Free web casts for DBAs and Developers on Performance Tuning, Development, Administration and more....