Some facts about the Database Recovery Advisor and Restores


A new feature in SQL Server 2012 Management Studio is the Database Recovery Advisor. So why am I writing a post on a SQL Server 2012 enhancement which has been blogged already. Database Recovery Advisor There are some unique nuances that you need to be aware of while using this new utility. The Database Recovery Advisor facilitates constructing restore plans that implement optimal correct restore sequences.

Read on if you are interested in learning more about the Disaster Recovery Advisor. I will be referring to the Database Recovery Advisor as DRA in this post going forward.

The facts mentioned in this post are answers for frequently asked questions about DRA that I get from customers.

Fact 1: points out missing files from a split backup

This has already been described in the MSDN blog post. DRA will point out that you have a missing file from the backup files that you have selected.

Fact 2: Reading non native SQL database backups could be an issue

DRA uses RESTORE HEADERONLY and RESTORE FILELISTONLY commands to prepare the restore chain to assist you in your point-in-time recovery scenario. Many third-party backup solutions do not allow the execution of the RESTORE HEADERONLY and FILELISTONLY commands on their backup files due to a proprietary file header created by them. In such cases, DRA will be unable to assist unless the non-native backups are converted to native backups.

The backup files created by the SQL Server Backup Simulator which uses sqlvdi.dll allows RESTORE HEADERONLY and FILELISTONLY commands to be executed on them! So it would be incorrect to say that all VDI based backups cannot be read by DRA!

Fact 3: Can create restore chains for multiple backups

If you point DRA to a file share which has multiple backups from different databases, then it can sift out the backups associated with your database and create the restore chains for you. In Screenshot 1, I am picking backups from three different databases and storing them in a central location. Now I select all these backups from the list of backups to be evaluated by DRA before the restore. If you have a file in the share which is not a database backup file, then the restore dialog will ignore that file even if you have selected it.

image

Now when I use the database dropdown option in the recovery dialog box in Management Studio, I see the databases which can be restored from the backup (see screenshot 2).

image

After this when I click on the Timeline button for DRA, it will give me the right timeline based on the backups available.

Fact 4: Can detect missing files

If you select a point-in-time restore option and select a timestamp for which a backup is not available or a backup in the chain in missing, then DRA will report the same and prevent you from getting to a point where you have restore 50% of your backups to realize that a backup file is missing in the chain! The error message reported will be:

Unable to create restore plan due to break in the LSN chain.

The timeline view of DRA will also show you that there are missing files (see screenshot 3 below). Notice the whitespace in the timeline which denotes missing files.

image

Fact 5: Mixing and matching backups is also supported

If you point DRA to a set of backups taken from different timeframes which includes multiple types of backups for the same databases, then DRA can bifurcate between the different timestamps and LSN chains. Then it can guide you in deciding on the point-in-time restore options and the availability of the backups for the same.

image

Depending on which point-in-time restore you select in the timeline window, the right sequence of backups is selected by DRA (see screenshot 4).

Additional Reference
http://msdn.microsoft.com/en-us/library/cc645579.aspx

Advertisements

4 thoughts on “Some facts about the Database Recovery Advisor and Restores

  1. I didn’t understand the last fact “mixing and matching backup is also supported”. Can you please explain it a little more?

  2. Mansi,
    If you add multiple backup files which are taken from different times and from different servers for the same database, then DRA can build the timeline for you based on these backups. If you look at screenshot 4, you will see that there are two different full backups taken along with multiple transaction log backups. However the full backups were taken at different times. DRA is able to distinguish between the different backups and show the graphical timeline accordingly. Please let me know if you need further clarification.

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s