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.
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
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.
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.
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.