By: Ashish Kumar Mehta | 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.
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.
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.
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.
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.
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.
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.
8. Once the database is restored successfully you will see the below snippet.
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
- Read SQL Server point in time recovery to see steps to achieve Point in Time recovery in older versions of SQL Server.
- 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 cannot 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.
- Stay tuned for more tips on SQL Server 2012 and check out these related tips:
About the author
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
