Always On - Restore a node from a Veeam backup

Question

I have a cluster with 2 nodes, and Veeam is backing up databases every day and logs every 5 minutes. This works great and I can easily recover a single database to any point in time.

Then I was wondering what will happen if I completely lose a node. Let's say, worst case scenario, OS is gone. The AG will handle the failover and databases will still be accessible.

How can I restore the lost node?

I went into testing. To avoid a full reinstall of the node I took a backup of the VM. But obviously databases contained in the backup are older than those on the primary node of the cluster. So when I tried this I end up being stuck with databases out of sync.

First thing I did after the backup was restored was to restart the machine to kill. Still out of sync. As far as I understand, the backup I used was not a SQL database backup, and databases were in use (not that good) and the second error the databases on the primary and the broken secondary took different paths.

I found a really inefficient way to do it: remove databases from the AG, delete them on the secondary, then re-add them to the AG and let the secondary node sync.

Is it possible to resync databases more efficiently? Particularly, without losing the primary (meaning no restoration on the primary)?

Answer 1

You will want to keep the databases in the availability group so that users/applications can continue to use the databases. To accomplish this, remove the replica (server that was restored) from the availability group, not the databases. Then when you add it back you can choose the method of synchronizing. If you're using SQL Server 2016 or later, you can use the automatic seeding method, which will allow your Veeam backups to continue uninterrupted.

If you choose the backup/restore method, you may have to take a new full backup in Veeam immediately thereafter in order to preserve its log chain. I'm not sure if Veeam will continue to take log backups if it doesn't have the last full backup, but I know some other backup software won't.

Answer 2

It does not look like WITH NORECOVERY functionality will be included with Veeam until a future update, per this thread found over at the official Veeam forums. It looks like you'll need to use native features (as you've already identified) until then to get your AG back in sync.