AlwaysOn Restore Revisited

In my last post I talked about restoring a database that is part of an Availability Group which may have backups taken from multiple instances. The problem is that MSDB is only aware of backups taken from that instance.  This means that the Database Recovery Advisor can’t really build the nice pretty timeline if you have backups taken from multiple instances.  I have found a way around that problem.  When I say “I”, what I really mean is my fellow PFE Denzil found it. Since I mentioned the limitation in my previous post, I wanted to make sure and outline the workaround here.  Here is an example scenario:

SQL1 and SQL2 host the AdventureWorks2012 database in an Availability Group.  Both full and TLOG backups are set to backup on a secondary replica.  You will tell by reading from the backupset table in MSDB that the database failed over from SQL1 to SQL2 and back again.

MSDB on SQL2 – You can see we have a full backup early in the morning followed by regular TLOG  backups.

…. I will spare you from scrolling

Switch to MSDB on SQL1  - The AG was failed over therefore backups are now happening on the new secondary replica (SQL1)

Switch back to SQL2 – The AG was then failed over back to SQL1 once again.  Backups are now back at SQL2 which is once again a secondary replica

 

Recall from my last post that if you try to restore the database on either instance the GUI immediately gives you an error at the top of the window…

Dang!  Now I can’t use that cool timeline feature. Well guess what!  There is a way to that you can get  the recovery advisor to recognize all the backups.  If you followed my suggestions in my past post about ensuring that you have all your backups go to the same  shared file location, this is super easy.  You simply select that you want to backup from a database device, and select all the full and tlog backup files in the directory.  If you have a lot of files, it can take a few minutes to process.

Now you can click on the Timeline button and use the nice timeline feature of the database recovery advisor.  This is one of my favorite manageability enhancements for SQL 2012.

Note that this little workaround won’t just benefit you in an AlwaysOn Availability Group scenario, but also when you are copying backup files to a different environment.  Many times I have done a point in time restore of production data to a non-production environment for load testing, researching data issues within an application, etc.  In that case, MSDB of the non production instance will not have backup information for the prod db either.

Thanks again to my buddy Denzil for mentioning this workaround to  me, The greatest benefit of working in PFE at Microsoft is the vast experience of all my fellow PFEs and our culture of sharing knowledge with one another.