A Guide To Recovering High Availability Group Databases

🛠️ Guide: Recovering SQL Server Databases from `Recovery Pending` or `Suspect` State (with AG Involvement)

🔍 Scenario

A database in an Always On Availability Group (AG) enters a Recovery Pending or Suspect state due to corruption, power failure, or log file issues. This guide walks through the recovery and repair steps.

✅ Prerequisites

Ensure you have sysadmin rights on SQL Server.
Confirm no recent valid backups are available before using destructive repair.
This guide assumes the database is part of an Availability Group.

🪜 Step-by-Step Recovery Process

🔹 Step 1: Remove the Database from the Availability Group (on Primary)

This step is mandatory before you can repair the DB.

ALTER AVAILABILITY GROUP [YourAGName] REMOVE DATABASE [YourDB];

🔸 This action detaches the database from the AG but doesn’t delete it.

🔹 Step 2: Set the Database to EMERGENCY Mode

Emergency mode allows access to a broken or inaccessible database.

ALTER DATABASE [YourDB] SET EMERGENCY;

🔹 Step 3: Switch to SINGLE_USER Mode (with rollback)

Prevents any other connections and ensures exclusive repair access.

ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

🔹 Step 4: Run DBCC CHECKDB (to assess damage)

This scans the database for corruption.

DBCC CHECKDB ([YourDB]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

📝 Check output to see if REPAIR_ALLOW_DATA_LOSS is required.

🔹 Step 5: Run DBCC CHECKDB with REPAIR

Only if CHECKDB recommends REPAIR_ALLOW_DATA_LOSS, run:

DBCC CHECKDB ([YourDB], REPAIR_ALLOW_DATA_LOSS);

🔹 Step 6: Attempt to Bring the Database Online

ALTER DATABASE [YourDB] SET ONLINE;

✅ If successful, proceed to the next step.

🔹 Step 7: Restore MULTI_USER Access

ALTER DATABASE [YourDB] SET MULTI_USER;

🔹 Step 8: Take a Full Backup

Always back up immediately after recovery.

BACKUP DATABASE [YourDB] TO DISK = 'C:\Backups\YourDB_recovered.bak' WITH INIT, COMPRESSION;

🔁 Optional: Re-Add to Availability Group

If desired, re-add the database to the AG using automatic seeding or backup/restore method.

⚠️ Important Notes

REPAIR_ALLOW_DATA_LOSS is irreversible. Use only as a last resort.
If log file corruption persists (Msg 9004), consider rebuilding the log:
- Detach the DB
- Remove the .ldf
- Reattach using FOR ATTACH_REBUILD_LOG

✅ Summary

Step	Description
Remove from AG	Required before any repair
Set EMERGENCY + SINGLE_USER	Enables exclusive repair access
Run CHECKDB	Identify and assess corruption
Repair if needed	Use `REPAIR_ALLOW_DATA_LOSS` if no backup
Bring online & backup	Return to service with a new backup
Rejoin AG (optional)	After full recovery and validation

Need help with a failed database? Our engineers in Kogarah and Wollongong recover SQL Server and line of business databases for businesses across Southern Sydney and the Illawarra. Call 02 9052 0353 or see our backup and disaster recovery services.

A Guide To Recovering High Availability Group Databases