page contents

About the Post

Author Information

Introduction to SQL Server Recovery Advisor

Backup and more importantly Restore operations are essential database maintenance tasks. I think Buck Woody said it best; we do not have a backup strategy as much as a restore strategy that defines our worth to an organization. You can watch his comment here Session1: Blitz! SQL Server Takeovers

Having a good backup and restore plan in place helps Database professionals to restore from any failure in a very efficient way. In order to recover from a failure, you will need to restore a set of backups in a logically correct and meaningful sequence. (I will also state an old wise tale… practice makes perfect, so it is recommended that you test your restore strategy as much as possible) SQL server provides a bunch of backup types (Full, Differential, Copy only, etc.), creating a correct recovery sequence for a given point in time can be a tricky task depending on your level of alertness. (i.e. Being woke up at 2 or 3 AM with the database on fire is not a very pleasant experience) Add to this the complexity of having multiple fork scenarios or the fact that there can be multiple correct recovery plans with vastly different runtime costs. The task of creating an optimal and correct recovery sequence can get very complicated real fast.

In order to address this problem, SQL Server Management Studio (SSMS) in SQL 2012 has designed a tool called the “Recovery Advisor“. Recovery Advisor helps in creating a correct and optimal restore sequence. I will highlight the features of recovery advisor that are going to make life easier during database restores.

In order to play around with recovery advisor, let us first create a database and then take some backups of this new database. The following script creates a database and then takes a full back up, then a differential backup and then a transaction log backup. It then restores the database to differential backup and then takes a transaction log back up again.

–Create Database

create database test_recovery_fork 

GO

–Create a Table in the Database

use test_recovery_fork 

GO

create table t (c int) 

GO

–Back up Database (dif)

BACKUP DATABASE [test_recovery_fork] 

TO DISK = N’C:\Backup\Test_recovery_fork\test_recovery_fork.bak’

WITH NOFORMAT, NAME = N’test_recovery_fork-Full Database Backup’, 
SKIP, NOREWIND, NOUNLOAD, STATS = 10 

GO

–Insert changes

insert into t values (1) 

–Back up Database 

BACKUP DATABASE [test_recovery_fork] 

TO DISK = N’C:\Backup\Test_recovery_fork\test_recovery_fork.dif’

WITH DIFFERENTIAL, NOFORMAT, NAME = N’test_recovery_fork-Differential Database Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10 

GO 

–Insert more changes 

insert into t values (2) 

–Wait for a minute and then back up the log  

BACKUP LOG [test_recovery_fork] 

TO DISK = N’C:\Backup\Test_recovery_fork\test_recovery_fork.trn’

WITH NOFORMAT, NAME = N’backup_ test_recovery_fork -Transaction Log Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

–Recover to the differential backup.

USE [master]  

ALTER DATABASE [test_recovery_fork] SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

GO

RESTORE DATABASE [test_recovery_fork] FROM DISK = N’C:\Backup\Test_recovery_fork\test_recovery_fork.bak’

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 

GO

RESTORE DATABASE [test_recovery_fork] 

FROM DISK = N’C:\Backup\Test_recovery_fork\test_recovery_fork_dif.bak’

WITH FILE = 1, NOUNLOAD, STATS = 5  

GO


ALTER DATABASE [test_recovery_fork] SET MULTI_USER

GO 

–Insert more changes

use test_recovery_fork  

GO

insert into t values (3)

–Backup Log with new changes

BACKUP LOG [test_recovery_fork] 

TO DISK = N’C:\Backup\Test_recovery_fork\test_recovery_fork2.trn’ 

WITH NOFORMAT, NOINIT, NAME = N’test_recovery_fork-Transaction Log Backup’,

SKIP, NOREWIND, NOUNLOAD, STATS = 10 

GO

At this point, we now have 4 backups (1 Full, 1 Differential and 2 Log backups) for “fork scenario.” These backups form a basic fork configuration (where we have 2 possible point in time restores) as noted with the 2 different .trn transaction log backups.

To open recovery advisor, go to the database node “test_recovery_fork” in the Object Explorer, you can hit F7 or go to View and Select Object Explorer Details (as Seen Below)

Now in Object Explorer, you can right click, then go to tasks, go to restore and click on database. (See below)

This will open the Restore Database dialog:

By default, the recovery advisor tries to restore the database to the last backup taken. In this case it’s the second log backup. Look closely to the restore plan generated, which is to restore the full backup then the differential backup and finally a log backup.

Observe that the time of log backup (as seen below) is 1:41, i.e. this is the second log backup as the first log backup occurred at 1:39. (Seen above with the 4 backups taken) Thus, when we want to restore ‘test_recovery_fork‘ recovery advisor is (by default) giving us a correct restore sequence to the last backup taken (taking into consideration the restore that we had performed earlier).

In most scenarios, that might be the correct plan of action, but what if we do not want to restore to the last backup, but want to do a point in time restore? Well that’s exactly what the new ‘Timeline‘ feature will allow you to perform.

For example, let’s say that you want to restore to a specific point in time, like just after the differential backup, but before any of the log backups.

Click the timeline button of the restore dialog and it will open up the backup timeline dialog. The time line will show the different backups taken so far. In order to, give a specific time for database restore, check the ‘Specific date and time‘ radio button.

This will enable the date and time input boxes, along with the slider given below the timeline.

We can either directly choose the date/time that we want or we can use the slider to choose a location relative to the backups taken. Drag the slider to a point just after the differential backup (but before the first log backup).

Click OK.

Now we have a different restore plan that only involves the differential backup plan and nothing else. In this case, the transaction log backups were already included as a part of differential backup recovery and were ignored.

All in all, I found that the recovery advisor is a handy tool that is able to create optimal recovery plans for a given point in time. It makes the task of restoring a database less painful.

Tags: , , , , ,

One Response to “Introduction to SQL Server Recovery Advisor”

  1. I have also written an article on this topic with example, you can check it from
    http://sql-server-recovery.blogspot.in/2012/03/complete-overview-on-sql-database.html

    September 11, 2012 at 8:11 PM
Copy Protected by Chetan's WP-Copyprotect.