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:If I went all common steps required to restore a db (for example, on the current primary), I would end up with this:
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.
As 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:
Now we can start evicting the Resources database from the AlwaysON Availability Group: Then 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):
Oh,…forgot to delete the Resources db on the current secondary… Now deleting it and completing the wizard:
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”):
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.