How to restore Always On Availability Group databases

19 March,2021 by Jack Vamvas

Steps to restore a database from a backup device that was part of an Always On Availability Group, and now needs to be restored 

 

Recovery Scenario : Requesting an older database copy previously backed up 

Name of Always on Availability Group = MyAG1

Name of Always On Availability Group db = MyAGDB1

Note: this is a workflow - and there may be some slight variations depending your Availability Group set up 

Steps to Restore database 

--Remove the database from the AG Group from the PRIMARY 

ALTER AVAILABILITY GROUP [MyAG1] REMOVE DATABASE [MyAGDB1];

--Restore the database to PRIMARY from the backup device in RECOVERY mode 

Method will depend you're using RESTORE from disk  or might be using a console for an Enterprise backup system


--Restore the same database to SECONDARY in NORECOVERY mode. Some sample Restore statements ,

RESTORE DATABASE MyAGDB1 from disk='\\mynode1\Backups\MyAGDB1.bak' WITH NORECOVERY
RESTORE LOG MyAGDB1 from disk='\\node1\Backups\MyAGBDB1.trn' WITH NORECOVERY

--Add the database on PRIMARY back to the Availability Group

ALTER AVAILABILITY GROUP [MyAG1] ADD DATABASE [MyAGDB1];

--Join the database on the secondary replica back to the Availability Group

ALTER DATABASE MyAGDB1 SET HADR AVAILABILITY GROUP = MyAG1

 

Read more on Always On Availability Groups

Quick access to SQL Server AlwaysOn Availability cheatsheet

How to fix Database might contain bulk logged changes that have not been backed up for Always On




Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to restore Always On Availability Group databases


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer