SQLTeam.com | Weblogs | Forums

Always ON - Restore Database to Secondary

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

  1. 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

  1. join the WideWorldImporters database on the replica back to the AG

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

why are you shrinking the logs in the first place? If they grew that large, obviously the space was needed. How often do you do full backup and transaction log backup? If the log is filling up, maybe you need to schedule more frequent transaction log backups. As far as getting the secondary setup, remove the db from AG, stop transaction log backups if there are any. Then from primary, add the db to the secondary allowing it to do full backup. Both servers need access to the backup file. Then, when completed, re-enable any transaction log backups.

Thanks Mike.
Logs are somehow growing up as looks like it's not catching up from primary to Secondary not sure why.
Whenever we add the disk space, it's logs are filling up again and disk space becoming full.
We are taking log backup every 15 minutes and full backup daily on Primary.
We have already Secondary Replica/database set up.
I need to restore secondary database but looking into some google docs, confused that I have to take backup and restore into first Primary also even though it's already running and synchronized or I can just directly restore into secondary?

You need to figure out why the logs on not truncating - removing the database from the secondary isn't going to help if the problem is caused by an open transaction on the primary.

To simplify your process - look at setting the AG to automatic seeding. If you switch to automatic seeding (and enable the trace flag 9567), then all you need to do is add the database to the AG and SQL Server takes care of everything else.

See: SQL Server 2016 Availability Group Automatic Seeding

To repeat - find out why the logs are not truncating and fix that issue first.

Thanks Jeff.
Appreciate your feedback.
I am agreed with you that I need to find it out the Open Transaction to check why Logs are not truncating.
I keep adding disk space, resume the DB in Secondary and again disk space filling up on both the servers, tried to troubleshoot the cause what's going on but no luck, Now data center is in warnoing stage if i have to expand drive space.
I believe it's easy to move the file or add into another disk for Always ON, right?
It's BIG puzzle for me as I couldn't find any Active or Open Transaction which filling up the logs.
I couldn't even finding any blocking session.
Any Idea?

Look at sys.databases and find the log reuse - that will tell you what is preventing the log from truncating for that database.