Recently I rebuilt a development server that was a secondary in an Availability group.
What I didn’t do was remove it as a replica on the primary server prior to this.
This is good though, as these are the types of things you want to mess up in your dev environments, so you don’t make the same mistakes in production. Also you get a chance to fix something that might happen to someone else.
Anyway for whatever reason one of the databases on the primary server that was in an availability group got into a bad state of Not Synchronizing/Recovery Pending.
I thought fine I will just remove it from the group and then sort it out from there so ran the below command:
ALTER AVAILABILITY GROUP [ag-groupname] REMOVE DATABASE [dbname];
This resulted in the below error:
Availability group failed to process remove-database command. The local availability replica is not in a state that could process the command. Verify that the availability group is online and that the local availability replica is the primary replica, then retry the command.
The database has entered a challenging state. It is part of an availability group that is the primary meaning most commands will not work until it is removed. Yet you cannot remove it.
There may be easier ways to do this, and if so please drop us a note but what I had to do was:
- Remove the entire Availability group. I just did this through SSMS, right click and delete. If you need to re-add it make sure you have all the details of the group and the listener before you delete it of course..
- Run the command to recover the database.
RESTORE DATABASE [dbname] with RECOVERY
That did the job, my database was now back online and I could readd the db(s) and get on with the next task of the day.
You would of course need to recreate the availibility group and readd any databases that were part of it and synchronize them etc.