Always ON - Restore Database to Secondary

Question

Hello,
I tried to Shrink the log but it throws me an error so I tried to remove the database from AG group and try to restore it from the latest Primary backup but due to space issue, it went into Restoring state.
My Primary is currently fine and in Synchronized state.
I need to now resume the Restore database in secondary from Primary following steps looks fine?

Question is that Do I have take Backup and restore into Primary also before I do in Secondary?

1) Connect to Primary -> Go to Availabilty groups --> Availabilty Databases and Remove Secondary Database OR T-Sql
ALTER DATABASE [WideWorldImporters] SET HADR OFF;

2) Take FULL Backup from Primary and Restore into Secondary

note I will disable my T-Log backup job for now

3) Take T-Log Backup from Primary and Restore into Secondary

USE [master];
GO

BACKUP LOG [WideWorldImporters]
TO DISK = N'C:\Backups\WWI_Log.trn'
WITH NOFORMAT,
INIT,
STATS = 10;
GO

And then restore that log backup on the Secondary Replica

USE [master];
GO

RESTORE LOG [WideWorldImporters]
FROM DISK = N'C:\Backups\WWI_Log.trn'
WITH FILE = 1,
NORECOVERY,
STATS = 5;
GO

4) join the WideWorldImporters database on the replica back to the AG

ALTER DATABASE [WideWorldImporters]
SET HADR AVAILABILITY GROUP = TestLocation;
GO

Answer 1

Hi @ps ,

>I need to now resume the Restore database in secondary from Primary following steps looks fine?

No, there is some mistake in your steps. Please follow below steps to remove and add secondary database in SQL AG;

1.Remove a Secondary Database from an Availability Group;

This task is supported only on secondary replicas. You must be connected to the server instance that hosts the secondary replica from which the database is to be removed.

Please running below T-SQL on secondary replica that remove secondary DB;

ALTER DATABASE MyDb2 SET HADR OFF;    
GO  

2.Delete the removed secondary database from secondary replica;
3.Take full and log backup on primary replica;
4.Restore full backup and log backup with No Recovery on secondary replica. This database is in restoring state.
5.Add the secondary database to SQL AG;

Run below T-SQL on secondary replica that add secondary DB;

ALTER DATABASE Db1 SET HADR AVAILABILITY GROUP = MyAG;  

You can also do this from SSMS UI. Please refer to MS document Remove a primary database from an Always On availability group and Join a secondary database to an Always On availability group to get more information.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Thank You Cathy, appreciate for your steps.

It helped me to resole the issue but I have question that Secondary database needs to be deleted or I can just restore it using FULL and Log backup of Primary DB?
Restore i can use with REPLACE?

Answer 3

Thanks Erland.