How to recover a SUSPECT SQL Server database

how-to-recover-suspect-sql-server-database.md

How to recover a SUSPECT SQL Server database

If your SQL Server database (for example, semantic_pos_db) is marked as SUSPECT, this guide will walk you through the steps to bring it back online safely.

---

1. Identify the problem

Run the following in SQL Server Management Studio (SSMS):

SELECT name, state_desc
FROM sys.databases
WHERE name = 'semantic_pos_db';

If state_desc shows SUSPECT, proceed to the next step.

---

2. Set the database to EMERGENCY mode

This allows read‐only access so you can investigate.

ALTER DATABASE semantic_pos_db SET EMERGENCY;
GO

---

3. Run a consistency check

DBCC CHECKDB('semantic_pos_db');
GO

Review any errors reported. These will tell you if there's corruption, missing pages, or other issues.

---

4. Repair the database (if no good backup exists)

⚠️ Note: This can result in data loss. Use only when you cannot restore from a good backup.

ALTER DATABASE semantic_pos_db
  SET SINGLE_USER
  WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB('semantic_pos_db', REPAIR_ALLOW_DATA_LOSS);
GO

ALTER DATABASE semantic_pos_db
  SET MULTI_USER;
GO

---

5. Bring the database back online

ALTER DATABASE semantic_pos_db SET ONLINE;
GO

Then verify:

SELECT name, state_desc
FROM sys.databases
WHERE name = 'semantic_pos_db';

You should see ONLINE.

---

6. Restore from a backup (if available)

If you have a valid recent backup, prefer this route:

RESTORE DATABASE semantic_pos_db
FROM DISK = 'C:\Backups\semantic_pos_db.bak'
WITH REPLACE;
GO

---

7. Prevent future issues

• Ensure that you have a scheduled sql server database backup to prevent future issues

---

Summary:
Converting the database to EMERGENCY mode, running DBCC CHECKDB, repairing (if needed), and bringing it back online will usually recover a SUSPECT database. However, restoring from backup remains the safest option.

---

Feel free to share this guide with others and fork or update for your own environment.