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