Point in Time Recovery Using New Timeline Feature in SQL Server 2012

By:   |   Comments (3)   |   Related: > Restore


Problem

SQL Server backups are key to recovering from a disaster or some type of data failure, but the real magic happens when the backup is restored.  In this tip we look at a new enhancment in SQL Server 2012 for point in time recovery using the new timeline feature.

Solution

Point in time recovery allows you to recover your database to a particular point in time right before an errounenous transaction occured.  However, to be able to use Point in Time recovery for a database the database has to use the Full or Bulk Logged recovery model.

If your database is using the Simple recovery model you will need to change the recovery model to Full or Bulk-Logged and then start taking backups. To change the recovery model for a database you can execute the below TSQL code.

-- use this to change to Bulk Logged recovery model
ALTER DATABASE databaseName SET RECOVERY BULK_LOGGED

-- or use this to change to Full recovery model
ALTER DATABASE databaseName SET RECOVERY FULL

Example

Let's go through an example to demonstrate this enhancement for a database restore using SQL Server Management Studio in SQL Server 2012.   Before I started, I checked that the AdventureWorks2008R2 recovery model was set to Full.  In this example, first we will take a full backup of the AdventureWorks2008R2 database, then we will drop the ErrorLog table and finally we will take a transaction log backup.

To achieve Point in Time Recovery for a database the DBA must have an idea when the problematic query was executed.  In this demo the problematic query of dropping the ErrorLog table in shown in the table below for reference.

Activity TSQL Code Activity Time
Take full backup of AdventueWorks2008R2 database BACKUP DATABASE ADVENTUREWORKS2008R2
TO DISK='C:\Backups\ADVENTUREWORKS2008R2.bak'
GO
@ 6:10:00 AM
Drop [ErrorLog] table in AdventureWorks2008R2 database Use AdventureWorks2008R2
GO
DROP TABLE [dbo].[ErrorLog]
GO
@ 6:12:11 AM
Perform a Transactional log backup BACKUP LOG ADVENTUREWORKS2008R2
TO DISK='C:\Backups\ADVENTUREWORKS2008R2.BAK'
WITH STATS = 10
GO
@ 6:14:00 AM

Here are the commands that were used.

sql server command to do database backups

Steps to restore a database using Timeline in SQL Server 2012

Now that we have our backups in place we will walk through the restore process.

1. In Object Explorer expand Databases and then right click AdventureWorks2008R2 database and choose Tasks > Restore > Database... as shown in the snippet below to open up database restore database dialog box.

ssms restore menu tree

2. In the General Page of Restore Database Dialog Box you need to specify the Source Database and Destination Database names. Next, you need to select all the check boxes under Restore Plan Backup sets to restore as shown in the snippet below.

ssms restore database dialog window

3. In order to restore a database to achieve point in time recovery click the Timeline... button next to the Restore to: option in the above snippet.

4. In the Backup Timeline: AdventureWorks2008R2 dialog box you need to choose the Point in Time to stop the recovery of the database as shown in the snippet below. In this demo, we know that the ErrorLog table was accidently dropped after 6:12 AM. Hence, we know that the database should be restored to a point just before the disaster struck.

sql server backup timeline dialog window

5. In the above snippet, you could see that to choose the desired point in time recovery for the database you can drag the arrow to the specific time as per your need. Once you have specified the point in time for the recovery click OK to save the settings and to get back to the Restore Database Dialog Box's General Page.

6. In the Files Page you can change the path where the database files should reside after the restore.

sql server ssms restore database window

7. In the Options Page, you will see different Restore options. You need to choose the Recovery State as RESTORE WITH RECOVERY and select the check box Tail-Log backup and Leave source database in the restoring state with norecovery and specify the Backup file path for the tail log backup of the source database. Click OK to achieve point in time recovery of the database.. Click OK to achieve point in time recovery of the database.

sql server ssms restore database window restore options

8. Once the database is restored successfully you will see the below snippet.

ssms database restore successfully dialog window

The most important thing which a database administrator needs to find out is what is the recovery point time. In some scenarios you may know the time, 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

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

View all my tips



Comments For This Article




Tuesday, February 21, 2012 - 9:27:57 PM - Jason Back To Top (16121)

The problem of using UI to do this is you are assuming msdb is available. Under disaster, the msdb might have been gone along with the storage array. You have to restore msdb first. If preferred, as I do, I generate the restore-script and save them within retention period. The user database can be restored before msdb is restored. Certainly, msdb often has important schedule job info, which is also required to be restored as the whole strategy.

Regards,

Jason        http://dbace.us

 

 


Tuesday, February 21, 2012 - 3:10:46 PM - Jason Back To Top (16118)

I have already scripted this feature in T-SQL from SQLServer2005 days. See my presentation @ SQLPASS #107, April, 21, 2012, and publication of scripts.

 

Regards,

Jason http://dbace.us


Monday, February 20, 2012 - 11:48:55 AM - mssqldude Back To Top (16096)

On my RC0 of SQL Server 2012, the OK button does not appear when using the timeline restore capability, so just hit ENTER if you see that, too.















get free sql tips
agree to terms