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.

Improve this question
0

1 Answer 1

Reset to default
16

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;
Improve this answer
6
  • 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, 2017 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, 2017 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, 2017 at 3:59
  • Hence the log restores. See comment above.
    – Steve Mangiameli
    Nov 3, 2017 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, 2018 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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