Recovery Advisor: An Introduction
Vatsalya Agrawal is a colleague of mine, in the test team at SQL Server Manageability. This is part 1 of a two part series that he has written, on Recovery Advisor. This is a new tool, that we have introduced to make restoring databases easier for users. Read part 1 here.
--
Backup Restore is an essential database maintenance tasks. Having a good backup plan in place helps DBAs to restore from any failure in a very efficient way. In order to recover from a failure, DBAs have to restore a set of backups in a logically correct and meaningful sequence. As the SQL server provides a bunch of backup types (wherein each has a different set of properties), creating a correct recovery sequence for a given point in time is a tricky business. Add to this the complexity of multiple fork scenarios and the fact that there can be multiple correct recovery plans with vastly different runtime costs and the task of creating an optimal and correct recovery sequence get very complicated.
In order to address this problem, SSMS has designed a tool “Recovery Advisor”. Recovery Advisor helps DBAs in creating a correct and optimal restore sequence. In this blog I will highlight the features of recovery advisor that are going to make life of DBA easier during database restores.
In order to play around with recovery advisor, let us first create a database and take some backups on the same. The following script creates a database and then takes a full backup, then a differential backup and then a transaction log backup. It then restores the database to differential backup and then takes a transaction log backup again.
create database fork_scenario GO use fork_scenario GO create table t (c int) GO BACKUP DATABASE [fork_scenario] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\fork_scenario.bak' WITH NOFORMAT, NOINIT, NAME = N'fork_scenario-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO insert into t values (1) BACKUP DATABASE [fork_scenario] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\fork_scenario.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'fork_scenario-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO insert into t values (2) --wait for a minute BACKUP LOG [fork_scenario] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\fork_scenario.bak' WITH NOFORMAT, NOINIT, NAME = N'backup_fork-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO --Recover to the differential backup. USE [master] ALTER DATABASE [fork_scenario] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO RESTORE DATABASE [fork_scenario] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\fork_scenario.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 5 GO RESTORE DATABASE [fork_scenario] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\fork_scenario.bak' WITH FILE = 2, NOUNLOAD, STATS = 5 GO ALTER DATABASE [fork_scenario] SET MULTI_USER GO use fork_scenario GO insert into t values (3) BACKUP LOG [fork_scenario] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\fork_scenario.bak' WITH NOFORMAT, NOINIT, NAME = N'fork_scenario-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO |
There are now 4 backups (1 Full, 1 Differential and 2 Log backups) for ‘fork_scenario’. These backups form a basic fork configuration (Figure 1).
![clip_image001[6]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/00/62/51/metablogapi/4403.clip_image0016_54BDAA93.png "clip_image001[6]")
Figure 1: Basic Fork Scenario.
To open recovery advisor, go to the database node ‘fork_scenario’ in the Object Explorer, right click and go to tasks, and further on to restore. Click database. This should open the dialog in Figure 2.
![clip_image003[9]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/00/62/51/metablogapi/1777.clip_image0039_47772180.jpg "clip_image003[9]")
Figure 2: Default restore plan to restore to last backup taken.
By default, 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, the plan is to restore full backup then differential backup and finally a log backup. Observe that the position of log backup is 4, i.e. this is the second log backup (L2, the last backup taken) as the first log backup (L1) will have position 3. Thus, when we want to restore ‘fork_scenario’ recovery advisor is by default is giving us a correct restore sequence to the last backup taken (taking into consideration the restore that we had performed earlier).
So far so good, but want if we do not want to restore to the last backup taken, but want to do a point in time restore? Well that’s exactly what ‘Timeline’ feature does. Let’s see how. Suppose I want to restore to a point in time just after the differential backup (before any of the log backups).
Click the timeline button of the restore dialog. This opens up the backup timeline dialog (Figure 3). The time line in here shows different backup taken so far. In order to, give a specific time for database restore, check the ‘Specific date and time’ radio button. This enables date and time input boxes along with the slider given below the timeline. We can either directly enter the date/time 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 log backup L1). Click OK.
![clip_image005[8]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/00/62/51/metablogapi/7127.clip_image0058_74C89183.jpg "clip_image005[8]")
Figure 3: Point in time restore, backup timeline dialog.
As a new restore time is now entered, recovery advisor again looks up available database backups and reconstructs a new restore sequence for us (Figure 4). This new restore plan again restores a full backup then a differential backup and finally a log backup, but look closely at the positions, the position of the log backup is now 3. That is, it’s the first log backup L1. Recovery advisor has correctly picked up the different recovery path corresponding to the new restore time given.
![clip_image007[8]](https://msdntnarchive.blob.core.windows.net/media/MSDNBlogsFS/prod.evol.blogs.msdn.com/CommunityServer.Blogs.Components.WeblogFiles/00/00/00/62/51/metablogapi/3324.clip_image0078_7669378A.jpg "clip_image007[8]")
Figure 4: Restore plan for given point in time.
We have seen that recovery advisor does create a correct restore plan, but what about optimality of the plan? Notice that, recovery plan with a full backup and then only a log backup (positions 1 and 3) would also have been a correct plan. This plan would have been more expensive then the plan created by the recovery advisor, because restoring a set of transactions from a differential backup is cheaper than restoring them from a log backup. In this case, the transactions already covered as a part of differential backup recovery will be ignored (if they are also covered by the log backup) when the log backup is restored.
All in all, recovery advisor is a handy tool able to create optimal recovery plans for a given point in time. It makes the task of restoring a database less painful. Happy restoring.
Vatsalya Agrawal can be reached at vaagrawa@microsoft.com.
Comments
Anonymous
March 30, 2013
Hi Vatsalya, sorry to be overly critical, but if you use a batch terminator after your ALTER DATABASE statement setting the database in single user mode, this will cause errors for anyone in a real-world scenario as another process could obtain that single-user connection rendering you without a connection to perform the restoreAnonymous
May 09, 2013
The comment has been removed