Restoring a SQL Server 2012 database in high availability

Question

I have a database which is in always-on high availability mode synchronized with another database on a different instance. How can I restore from a .bak file into the primary database using T-SQL?

I’m new to high availability and I’ve been advised that I need to take the database out of high availability before I can do a restore and then put it back in high availability again but I’m not sure.

I’m hoping I can just restore straight into the primary while the AlwaysOn is still enabled and it will auto sync with the secondary.

asked 2014-11-12 by Stew

Answer

There’s a complete answer to this question over on dba.stackexchange.com.

Cross-posting the answer here, so that it can be found from both versions of the question:
Copied verbatim from John M’s answer:

By restoring a backup, you are essentially replacing the database schema and data. You will need to turn synchronization off, remove the DB from HA and perform the restore on the primary and replica, leaving the replica version in a restoring state by using WITH NORECOVERY. Once your backup is in place, put the DB back into HA and start synchronization again.

HA is very similar to mirroring and uses similar technology, just not nearly as finicky. You will want to treat your HA DBs similarly as well.

Code would be similar to the following:

–on primary
    ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE AdventureWorks2012;
–on primary
    RESTORE DATABASE AdventureWorks2012
       FROM AdventureWorksBackups
       WITH NORECOVERY, 
          MOVE 'AdventureWorks2012_Data' TO 
    'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf', 
          MOVE 'AdventureWorks2012_Log' 
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
    RESTORE LOG AdventureWorks2012
       FROM AdventureWorksBackups
       WITH RECOVERY;
–on secondary
    RESTORE DATABASE AdventureWorks2012
       FROM AdventureWorksBackups
       WITH NORECOVERY, 
          MOVE 'AdventureWorks2012_Data' TO 
    'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.mdf', 
          MOVE 'AdventureWorks2012_Log' 
    TO 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\NewAdvWorks.ldf';
    RESTORE LOG AdventureWorks2012
       FROM AdventureWorksBackups
       WITH NORECOVERY;
–on secondary
    ALTER DATABASE AdventureWorks2012 SET HADR AVAILABILITY GROUP = MyAG;