🛠️ 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