We had the unique situation where everything in our data center was taken offline except for one of the servers that hosted the secondary replica in our Always On High Availability setup, which left all secondary databases stuck in “not synchronizing / recovery pending” mode. No matter what I tried I could not get the databases online, which makes sense since of course the machine hosting the Availability Group was offline, as was the primary replica. So even though the databases should have been able to be online, the failover could not happen because everything else was offline, thus recovery was indefinitely pending.
I thought maybe I could copy the database files and bring them back online as a different database, breaking the high availability connection, but because they were in use I couldn’t do that either. The closest forum topic I found was this: https://dba.stackexchange.com/questions/33662/removing-databases-in-recovery-pending-status-after-ag-group-was-removed
But it still didn’t offer a solution in my case because they actually ended up recreating the whole failover cluster, re-enabling HA and then recovering from there, which I was not prepared to do.
Turns out the solution was simple for me. I stopped the SQL service which released the hold on the recovery pending data files, then copied the files to a different server and attached them as a different database name, and immediately the data became accessible. Then I re-started the SQL service on the original server putting the files back into restoring mode, which in this case was fine because I had access to the data I needed on a different server and was able to leave the original databases in recovery mode where I was now able to wait as long as necessary for the original High Availability configuration will be restored.
1 thought on “Recovering secondary databases in AlwaysOn High Availability setup when Availability Group is offline”