There have been a few scenarios where I have had to create a fresh backup of a database and restore the secondary nodes in an Availability Group. Normally the route cause has been when a secondary node has stopped syncing due to hardware failure, excessive log growth, low bandwidth or high IO. To restore a secondary node, you have to take the database out of the availability group. This can be fairly pain free with small databases but can be laborious process for larger ones, especially as you have no high availability when performing the restore.
In my most recent scenario, one of my databases on my Async non-readable secondary had suspended data movement. During this time, the log files had got larger and larger as the Primary was waiting for this secondary to start receiving data again. It also didn’t help that there was some rather larger batch processes running in the application as well as scheduled index maintenance (which failed, funnily enough). There were no hardware failures so my best guess (as I was on holiday at the time of the event) is that a perfect storm of long running queries coupled with ongoing index maintenance, high CPU and low bandwidth caused the suspension of data movement. When data movement was resumed, the secondary database went into Suspect mode. The database had been out of sync for so long that there was no chance it was going to catch up.
So to the restore process. This should look something like this:
- Take the database out of the AG.
- Perform a Full Backup
- Perform a Log Backup
- Add the database back to the AG
USE MASTER GO ALTER AVAILABILITY GROUP [AGGroup] REMOVE DATABASE [DBName]; BACKUP DATABASE [DBName] TO DISK = '\\storage02\Backup\DBName.BAK' -- Make sure this is accessible to the secondary nodes WITH Name='DBName.bak', STATS=10, DESCRIPTION='Give Reason for Backup', INIT GO BACKUP LOG [DBName] TO DISK = '\\storage02\Backup\DBName.TRN' WITH INIT GO ALTER AVAILABILITY GROUP [AGGroup] ADD DATABASE [DBName]; GO
- Restore Database from full backup with NORECOVERY
- Restore Log from log back up with NORECOVERY
- Add database back to AG Group.
- Ensure data movement has resumed and monitor the AG via the dashboard.
- Repeat on any other secondary node.
USE MASTER GO RESTORE DATABASE [DBName] FROM DISK = N'\\storage02\Backup\DBName.BAK' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5, NORECOVERY GO RESTORE LOG [DBName] FROM DISK = '\\storage02\Backup\DBName.TRN' WITH FILE=1, REPLACE, NORECOVERY GO --Set owner to the SA account ALTER AUTHORIZATION on DATABASE::DBName to sa --or whatever you SA account is called. -- YOU WILL THEN NEED TO RUN the same restore process on both Secondary Nodes again before adding the databases back to the Availability Group -- Re-join database to availability group ALTER DATABASE [DBName] SET HADR AVAILABILITY GROUP = [AGGroup]; ALTER DATABASE [DBName] SET HADR RESUME;
The only other thing left to do is document the whole process and give it to your other DBAs so they know what to do the next time something like this happens when you are on holiday.