SQL 2014: Restoring a database that is part of an AlwaysOn availability group

In my previous blog post I showed how to set up a backup for a database participating in an AlwaysOn Availability group. Once we have backup jobs running there might be the need to restore the “AlwaysON database” and in this post I’d like to demonstrate how we can do it.

Suppose we need to restore the full backup (Resources_backup_2015…_5920418.bak) created on 17/02/2015 at 2:20 PM:P5-P4-33If I went all common steps required to restore a db (for example, on the current primary), I would end up with this:P5-8

P5-9

This is because it’s not possible to restore a database participating in an Availability group – first we must evict a database from an AG, restore the db and then re-add the db to the AG. Let’s see how this can be done.

Before we start restoring the database I’d like to point out that when the database is being replicated to multiple server instances the number of backup sets available for restoring may be different on each server instance (for example, if the preferred backup method is set to “Prefer secondary”, as in this test), depending on which particular server was used for runnung a backup job.

P5-15-1P5-15-2As you can see SQL2\msdb\backupset table does not contain the record for the most recent backup taken on 17/02/2015 at 2:20AM – the most recent backup here is the backup taken on 17/02/2015 at 2:12AM – this is because after running the backup job on SQL2 (while it was secondary) at 2:12AM I made the test switchover and took one more backup against the new secondary – SQL1 – at 2:20AM. Here is this backup:P5-SQL1

Now we can start evicting the Resources database from the AlwaysON Availability Group:P5-11 P5-12 P5-13Then I restore the most recent backup taken at 2:20AM on SQL1 – in the SSMS run on SQL1 the Restore database dialog will display it as the most recent backup (on the contrary, SSMS run on SQL2 will display the backup taken at 2:12 as the most recent one):

P5-14P5-15

P5-18 P5-19The next step is to re-add the database to the Availability Group once again:P5-21 P5-22

P5-23

P5-24 P5-25 P5-26

Oh,…forgot to delete the Resources db on the current secondary… Now deleting it and completing the wizard:

p5-31

P5-32

P5-33

P5-34

P5-LastAO P5-LastAO2

P5-37

And the final step: I’ll take the new database and log backups to start a new .bak/.trn backup sequence (on SQL2 as it’s my current secondary and my preferred backup method is “Prefer secondary”):

P5-51-2

Summary:

In this blog post we’ve restored a database that’s part of the AlwaysON Availability Group and started the new database and log backup chain for future restores.

22 responses

  1. Hi,
    How transaction log backup happened as you chosen only log backup.

  2. Sorry, you chosen only full backup

    1. Hi Syed,
      No problem, – you’re welcome! Please feel free to post any questions you have.
      Regards,
      Michael

  3. I am adding database to alwayon availability group by selecting full to DR and there is The database is 100 GB and it takes too long to complete adding database to alwayson.one job running on PROD which take hourly log backup. will the process complete successful ? or it throws any error because while adding database to alwayson the job on primary is taking another transaction backup .what is the solution ? thanks in advance …

    1. Hello Kom,
      If I get you right you can’t add a db to an AlwaysOn group because initial synchronization (= backing up a db + restoring a db) takes too long. In this case please try to restore the db on you secondary PRIOR TO adding the db to the AlwaysOn group and then choose “Join only” on the “Select Initial data synchronization” window.
      Please eleborate if I get you wrong.

      Regards,
      Michael

  4. I like how you leave the error in the instructions! You know people will hit that error, and now they know how to handle it.

    1. Peter, thank you so much for your words – I really appreciate it!
      My goal is to show all possible difficulties to the readers that they may be facing, not only the final result of the article.

      Regards,
      Michael

  5. This is not good! Why would MS make a simple backup so difficult? Always on has been in existence for 4+ years?

    1. Hm, there’re much much more strange things that MS does in its products 🙂
      What really bothers me is that the more complicated MS’s software become the less comprehensive documentation becomes on the corresponding software… That’s why in my articles I’m trying to illustrate the most important procedures as much as possible.

  6. very nice thanks – I need to automate this whole process which is fairly simple except for the part where you run the HA wizard – do you perhaps know how the tsql or SQL CMD will look for the restoration wizard to be run on a scheduled?

    Thanks,

    1. Thank you for you comment, raymod!
      Frankly speaking I didn’t try to automate the restoration process – there was no need to do it. I think the most important point here is that you should first include in your script the commands thats would evict your AlwaysON database from the availability group, and then use the standard RESTORE database command: “ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE Db6
      https://msdn.microsoft.com/en-us/library/hh213326.aspx#TsqlProcedure

  7. Hello Michael,
    Great read and very helpful.
    Do have a question though.

    Lets say the primary database server (server 1) goes down and with the help of the availability group, the secondary (server 2) becomes the primary database.
    When we bring up the original primary (server 1), what would happen? Will data be re-synced from the (current) primary (server 2) to the original primary (server 1) which would then act as a secondary?

    Or will there be a need of manual intervention and the above steps you mentioned need to be conducted to remove the server from the availability group and re-add it.

    1. Hello Nikhil,
      Thank you so much for your words!
      “When we bring up the original primary (server 1), what would happen?” – this former primary database replica will become the secondary one (server1) while the new primary – server2 – will continue to be primary. You do NOT have to do anything once the original server is back online if it does not matter to you what server is primary and what is secondary.
      For example, the servers I was writing about in my articles sit in my testing cluster and they often need to be restarted or/and shut down many times in a day. This results in my Always-ON database to “move” (become active) from one server to another whilst I even may not know what server now hosts the database – but it does NOT prevent clients from connecting to the database that is a part of the Always-On group whatever server is hosting the database at present. Of course, this “automatic” client reconnections are only available if clients connect to the availability group listener that may exist for the AlwaysON group.
      If you want, for instance, the server1 to always be your primary server, you can initiate db “move” from server2 to server1.

      Please feel free to post any other questions should you have ones.

      Regards,
      Michael

      1. Hello Michael,

        Thank you for the amazingly fast response. Which server is primary and which is secondary wouldn’t matter to us as long as they both are available to do synchronous commits (primary to secondary).

        When you say we don’t have to do anything once the original primary (server 1) comes back online, say after an hour we bring that machine back up.
        Will the current primary (server 2) sync all records with the former primary (server 1)?

        So to sum up, we basically won’t need to do any manual backup/restore tasks and it’ll all be managed by SQL Server itself?

      2. Hello Nikhil,
        …awfully sorry for the delay… – either did not noticed your second comment or there was some error on the site…
        “When you say we don’t have to do anything once the original primary (server 1) comes back online, say after an hour we bring that machine back up. Will the current primary (server 2) sync all records with the former primary (server 1)? “ – it is the current secondary that needs to synchronize with the current primary, not vice versa. If I understand you correctly you describe the situation when the Server1 (current primary) goes down – in this case your Server2 automatically becomes current primary, and when your previous primary (Server1) comes back online it would be curent secondary and it is Server which would need syncing with Server2 (current primary). If you want your Server1 to become primary again you must do it manually!

        “So to sum up, we basically won’t need to do any manual backup/restore tasks and it’ll all be managed by SQL Server itself?” – as long as you have at least one server with the read/write db replica you must not do anything else. You can manually restore a db in case you need to revert to the earlier data or when the db itself is corrupt.

        Please excuse me for the late response once again.

  8. thanks a lot

    1. Srikanth, you’re welcome!

  9. Hi,

    Thanks for your article, it describes the process nicely !

    However, I am having a question for you for a specific situation I have.

    The backup product I use (NetApp Snapmanager for SQL) makes snapshots on the volumes of my secondary (this is the preferred backup server). Take the situation that I have corruption in my database and have to remove it from the AG and delete it on the primary and secondary. I can now restore my database on the secondary server (I don’t seem to be able to restore it on the primary because backup was taken on the secondary).

    Is there a way I can reseed this database back from the secondary to the primary and add it to the AG again ? Add to AG only seems to work if the database is present on the primary. Or is there another way I can restore the database back to the AG from secondary (if it is not known in the AG anaymore).

    Thanks for you help already.

    Grtz,
    Tom

    1. Hi Tom,
      First of all, thank you very much for your kind words!

      Regarding the question: as far as I understand the most easy way to accomplish this is to switchover to the secondary (making it the new primary) right before the removing the database from the both servers: in this case you’ll be able to restore the db backup taken on the previous secondary (which is now a new primary) because it is the msdb database that contains the information on your existing backup sets and it does not matter what role you server currently plays in terms of AG – secondary or primary! In my article I’ve taken two backups and have made a couple of switchovers and it did not prevent me from restoring the db on the NEW PRIMARY although the backup had been taken while it was SECONDARY.
      Please let me know whether my suggestion is appropriate – if I missed out anything we’ll eleborate another solution!

      Regards,
      Michael

  10. Hi Michael,

    I also thought about doing a switchover yesterday after writing the previous comment.

    You are right that if I do a switchover before restoring my database to the node it was backed-up from, which is now the primary node, that I can again add this database to the AG and sync it back to the other node. However, I don’t really like the idea that I have to do a switchover and switchback, just to be able to restore a database to my AG.

    I chose the easier solution and I am now just making my backups on the primary node 😉

    Thanks for your suggestion.

    Grtz,
    Tom

    1. Hi Tom,
      Of course you can do it (I agree that it is the more easier solution), but why you don’t want to make a planned switchover – at least if there’s only one db in the AG it will not do any harm to your servers…? And the planned switchovers are very usefull for the testing purposes…

      Regards,
      Michael

  11. Well it is a restore of one database, but the server will be running dozens of databases. I am happy with the backup on the primary as the backup will be running outside business hours.

    Grtz,
    Tom

Leave a Reply

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

%d bloggers like this: