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.

Answer 1

There's a complete answer to this question over on dba.stackoverflow.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: https://dba.stackexchange.com/questions/82548/restoring-a-sql-server-2012-database-in-high-availability

---

Listen to your adviser. 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;

Answer 2

If the Microsoft SQL database is part of the Microsoft SQL 2012 AlwaysOn Availability Groups (AAG), and restoring to the original location fails, perform the following tasks: 1. Remove the database to be restored away from the Availability Groups. For more information, see http://msdn.microsoft.com/en-us/library/hh213326.aspx. 2. Use the Restore Manager to restore the database to all nodes in the AAG group. 3. Add the database back to the Availability Groups. For more information, see http://msdn.microsoft.com/en-us/library/hh213078.aspx.