13

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.

15

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.

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 primary

ALTER AVAILABILITY GROUP MyAG ADD DATABASE AdventureWorks2012;

--on secondary

ALTER DATABASE AdventureWorks2012 SET HADR AVAILABILITY GROUP = MyAG;
  • Question regarding the answer above ... In the code supplied, the PRIMARY was restored with NoRecovery. It was my thought that only the Replica would be done with NoRecovery. – Truett Oct 18 '17 at 14:44
  • Correct, but keep reading. Log restores were coded and then finally WITH RECOVERY to make it available for use. – Steve Mangiameli Oct 18 '17 at 18:14
  • If one is unable to rejoin the availability group on the secondary, then restoring a transaction log may be needed. This source helped resolve the following error: The mirror database, "dbname", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. – Hans Vonn Nov 3 '17 at 3:59
  • Hence the log restores. See comment above. – Steve Mangiameli Nov 3 '17 at 11:23
  • @SteveMangiameli thank you for putting this up. It has come in handy. I understand now you are restoring from a logical backup device instead of from file. When I restore from file I use separate .bak and .trn files. – Hans Vonn Jan 10 '18 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.