SQL Server: Database Recovery Advisor December 19th, 2012

Vinod Kumar

We have talked about two concepts earlier in this blog when it comes to backup in the recent past. First is around Page Restore Dialog and other is around how to find the Sequence of backup taken. Both these are very interesting and very important concept in my opinion and a frequently asked question. In this blog post, we will discuss the concept of Database Recovery Advisor (DRA) of SQL Server 2012. DRA is a new SQL Server 2012 feature to make it easier for SQL Server Management Studio (SSMS) users to do point-in-time database restore and Database Page restores. Although this feature is called Database Recovery Advisor, the end-user will only see these improvements via the Restore Database dialog and new Page Restore dialog. In DRA the users will be able to figure out which data pages are corrupt directly from the dialog and will be able to do Page Restores from the backups and this has been explained in our Page Restore Dialog post mentioned earlier.

What has DRA given us in the long term with SQL Server 2012.

  1. Redesigned the Database Restore Dialog in SSMS to support consistent restore plan creation and verification.
  2. Backup History Time-line dialog, this visually shows the backups on a time-line and lets user select a possible point-in-time to restore.
  3. Page Restore dialog in SSMS.
  4. Have support for these restore plan creation and verification even in SMO.
  5. Enabling the SSMS restore dialog to generate automated restore plan for all the scenarios.
  6. Show the Database backup history when exists in the msdb in the restore.
  7. When the msdb does not have the backup history of the Database, it needs to be constructed from the backup header info when pointed.
  8. Feature allows users to check the integrity of the backup files before restoring directly from the Restore Dialog.
  9. Allowing the user to restore to the point-in-time, even if it in the tail of the log, by automatically taking a Tail-Log backup.
  10. Page Restore Dialog allows the user to find out the corrupt pages in the Database.
  11. Page Restore Dialog automatically generate Restore Plan for Page-Restore from the backup history of the database.

Restore Correctness

Correctness of the Restore Plan is checked every time it is modified. If the sequence of the backups is not correct. Error message will be shown in the top message bar. For example:

The page header will give some interesting warnings making the dialog really complete and makes sure the DBA doesn’t miss a step.

  1. No Full backup Selected.
  2. Differential backup not compatible with the full backup.
  3. Some of the T-Log is not compatible with the Full or Diff backup set selected.
  4. The T-Log backup sequence is broken.

These some of the most common warnings that come up !!!

The checkboxes in the grid lets users exclude some backup sets from the Restore Plan as required. The Dialog handles various scenarios intelligently: for example, if a backup set is excluded from the plan, all the backup sets that are dependent on it will be unselected. The selection of the backup sets in the grid is always consistent to be restored.

Verify Backup Media: Checks the integrity of the selected backup sets. This will call RESTORE VERIFYONLY on the selected backup sets. This can be a long running operation, and its progress can be tracked and cancelled by the Progress Monitor on the Dialog Framework. The top information bar also shows the progress of the operation. We can Stop it too from the top bar.

image

RESTORE VERIFYONLY performs the following checks:

  1. Makes sure we are able to read all the backup set and all volumes.
  2. Some header fields of database pages, such as the page ID are accessible.
  3. Checks for Checksum correctness (if present on the media).
  4. Checks for sufficient space on destination devices.

Recovery time Range – Timeline Dialog

In the above figure we can see the timeline button and that opens up a nice dialog like below. This is really a powerful dialog that cannot be missed. This backup/restore functionality provides us the ability to restore back the state of the database to a previous point-in-time.

image

Last Backup taken: This selects the time of the last backup taken for that database that can be restored. (Selecting this disables the slider in the timeline.) This is the default setting.

Specific date and time: Allows the user to select a point-in-time in the timeline to restore the database to. (Selecting this enables the slider in the timeline.)

Interval: This combo box lets you select the span of the view in the timeline.
  1. Hour
  2. 6 Hour
  3. Day (Default)
  4. Week
Graphical Timeline: This timeline visually represents the backups of the database.
  1. The red line represents the selected point-in-time, this red-line will not go outside the feasible time span in which the database can be restored. Default location is the end of the last feasible restore point.
  2. The slider lets the user select a point-in-time restore location by moving the red line.
  3. The left-right button lets the user navigate on the timeline.
  4. Markers:
  1. The Markers represent the Full (bigger) and the Diff (smaller) backups.
  2. The green bar represents the T-Log backup coverage, while the light green bar represents the tail of the log, which has not been backed up.

Scripting Options

The best strategy to learn is always using the “Script to” option. There are four scripting options:

  1. Send RESTORE DATABASE script to a "New Query Editor Windows".
  2. Send to a T-SQL File.
  3. Copy text to Clipboard
  4. Create an Agent Job that can be run later.

We will highly recommend to get it to a T-SQL file because this can we can learn from what script SQL spits out and we can use the same script for later use if needbe.

Limitation of DRA

  1. Support for File and FileGroup Restore is not supported and there is a separate dialog to do that, but that will not have Recovery Advisor features.
  2. DRA will not support the recovery of system databases.
  3. DRA will not support DBCC Repairs.
  4. DRA does not support piecemeal restores: Files and Filegroups Restore dialog should be used for that.
  5. DRA will not support Page restores of Database Boot pages, PSF, GAM, SGAM etc.

As we described in the previous post, the Page Restore Dialog can be read from that post. I highly recommend you to read that post too.

Tags: , , , , , , ,

This entry was posted on Wednesday, December 19th, 2012 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply