SQLGardner

Lisa Gardner - SQL Sever Premier FIeld Engineer

SQL 2012 AlwaysOn and Backups – Part 3 – Restore

SQL 2012 AlwaysOn and Backups – Part 3 – Restore

Rate This
  • Comments 5

Work and family stuff delayed this blog post a bit, but we all must strive to find a balance in life! Sometimes things have to slide a little.  Anyway, let’s talk a little about how all these wonderful backup options in SQL 2012 can affect the restore process.

First and foremost, you cannot restore a database that is part of an Availability Group.  If you find you need to restore any of your replicas or your primary, you must evict it from the Availability group.  The mindset here is similar to what you are used to in a mirroring or log shipping scenario.  Since you are breaking the shared log chain, the database that you are restoring can no longer be a part of that log chain. This is the error that you would see. It is a very descriptive error message which I always like to see.

image

 

The second consideration has to do with the new ability to have a variable backup location.  If you are using the sys.fn_hadr_backup_is_preferred_replica function to determine the proper place for backups described in my previous post, then you may have a full backup on Server1, some tran log backups on Server2, etc.  Where this can be a bit of a bummer is when you use any of the GUI tools in Management Studio to do restores.  They use the backup history tables in MSDB in order to determine all the backupsets for a database.  This data is not shared or duplicated across instances. You will only see backups that were taken on that particular instance.

For example, assume that I took a full backup on the instance SQL1, but I have transaction log backups running on SQL 2.  If I go to restore the database on SQL1 in Management Studio, I will only see the full backup and not the tlog backups that were taken on SQL2.  Also to note, if I do the same in SQL2, I do not see any backups that can be restored. Now, if you look closely you will see a message, which I circled in this screenshot:

image

The break in the log chain is because there is no sign of a full backup in MSDB, which you can clearly see in the following screenshot query and result.  The transaction log backups are there, but no full backup, which makes sense since the full backup was executed on another instance.

image

 

If we REALLY wanted to restore this database, we could take the full backup file that was made on SQL1 and restore that file, then apply these 4 tran log backups taken from SQL2.  Of course we would need to remove the db from an availability group first, but typically you would be doing this restore on a different environment in order to test your restore plan, restore a copy of production data to a performance testing environment, etc. 

This leads right in to my third, and final, point of this post.  I want to reiterate the importance of using a network location for all your full, differential, and transaction log backups  that are taken against a database participating in an Availability group.  When multiple instances can be the driver of a backup file that is part of a shared log chain, having a single location for all the files is a must.  You don’t want to lose any files, and you don’t want to have to piecemeal a bunch of files from various locations when you need to do a restore.  Think if you are in a real disaster recovery scenario in a production environment.  You already have to deconstruct your Availability Group to restore your primary.  You are probably already sweating bullets as this is most likely your “last resort” to bring things back online…. make that task as easy as possible on yourself!  Always have a documented plan, so all you have to do is read the steps and follow the process! 

This concludes my 3 part series on some of the nuances of backup and recovery in Availability Groups.  I know many of you are just now beginning planning for 2012 implementations which may not occur for some time, but having a solid backup and restore scenario nailed down is often overlooked, yet extremely important.

Leave a Comment
  • Please add 4 and 2 and type the answer here:
  • Post
  • Great series Lisa! This area was much in need of some practical info and advice.

  • Hi Lisa, this is by far the best and most detailed post about AlwaysOn backup/restore.  Thanks for the good work!

    One thing I am not so sure is, how to perform full DB backup with the same strategy.  As I know full backup on secondary must use COPY_ONLY option.  Can you advise how to do it in conjunction with T-log backup?

  • Great post Lisa!  Question - How does copy_only full backup work with T-log backups?

  • Great series. I had one question about bringing the database back into the AG after the restore has completed. Does SQL take care of resynchronizing the secondary replica's after the restored database has been brought back into the AG?

  • When you restore the primary database, you need to initialize the replicas from  the same full backup.

Page 1 of 1 (5 items)