Normally when you add a database to an Availability Group (AG) you need to start with the database on the primary node. You restore backups With Norecovery on the secondary (either manually or with the GUI), add the database to the AG on the primary, alter the database on the secondary to reference the AG, and your job is done.

Sometimes after all the preparation you discover the wrong node is the AG primary, and you can't justify failing everything over just to add one new database in. But if you can tolerate a short outage to the database you're adding, then you can add it to the AG "in reverse" using a tail-log backup!

Here's my test setup.

  • Node 1 with the Lonesome_Database I want to add to the AG.
  • Node 2 as the primary of the AG.

Here's a simplified demonstration using SSMS in SQLCMD mode (in a real situation you would restore more backups and get the databases closer in sync before a switchover instead of doing it all at once):

:Connect W08R2S14C1N1\AOAG
Backup Database Lonesome_Database To Disk = '\\W08R2DC\Fileshare\Adhoc\Lonesome_Database.bak'
Backup Log Lonesome_Database To Disk = '\\W08R2DC\Fileshare\Adhoc\Lonesome_Database.bak' With Norecovery
Go

:Connect W08R2S14C1N2\AOAG
Restore Database Lonesome_Database From Disk = '\\W08R2DC\Fileshare\Adhoc\Lonesome_Database.bak' With File = 1, Norecovery
Restore Database Lonesome_Database From Disk = '\\W08R2DC\Fileshare\Adhoc\Lonesome_Database.bak' With File = 2

Alter Availability Group AOAG_Group Add Database Lonesome_Database
Go

:Connect W08R2S14C1N1\AOAG
Alter Database Lonesome_Database Set Hadr Availability Group = AOAG_Group
Go

In the first set of commands I've taken a full backup, and a tail-log backup (a log backup With Norecovery). This sets the database into a restoring state, which is the reason you need a short outage. But it doesn't break the log chain.

In the second set of commands I'm restoring the database as per normal which will bring it online. After that I add the database to the AG as per normal. Because the log chain isn't broken there is no problem with the two syncing up.

It just works and the proof is in the pudding.

I didn't know it could be done this way until I tried it. Usually I only hear about tail-log backups in the context of corrupt databases.