Startup recovery failed for a suspect database so it is unavailable. Check the state, open the error log and restore from a good backup. Emergency state gives administrators view only access so you can copy data out. Repair options are the last resort because they can delete data.
You open SQL Server Management Studio and a database shows the word Suspect. Applications that use it stop working at once.
This guide explains what the suspect state means and shows the safest order of steps. All commands are untested and follow the official pages.
What is SQL Server Suspect Mode?
Microsoft states that in the suspect state at least the primary filegroup is suspect and can be damaged. Startup recovery failed and the database is unavailable. See database states.
Microsoft also states that a recovering database becomes suspect when its recovery fails. That page asks for further action from you and lists no steps.
How do You Confirm the Database State?
SELECT name, state_desc FROM sys.databases
WHERE state_desc IN ('SUSPECT', 'EMERGENCY')Next open the SQL Server error log. Entries before the failure can name the file or the page that failed. This step comes from general SQL Server practice.
Which Recovery Path should You Choose?
| Path | Use When | Data Loss Risk |
|---|---|---|
| 1. Restore from backup | A good backup exists | Lowest |
| 2. Emergency state and copy out | You need data that no backup contains | None for data you copy |
| 3. Repair option | Nothing else works | Possible |
Copy the database files before you try any step. Copies give you a way back when a step fails.
How do You Restore a Suspect Database From Backup?
Microsoft always recommends a restore from the last known good backup as the primary method to recover from integrity errors. See DBCC CHECKDB.
- Take a tail log backup if the log is readable.
- Restore the last full backup with NORECOVERY.
- Restore later differential and log backups with NORECOVERY.
- Finish with RECOVERY and check the database state again.
When only a few pages are damaged see how to resolve SQL Server page level corruption.
How do You Use Emergency State to Copy Data?
Microsoft states that EMERGENCY is mainly for troubleshooting. You can set a suspect database to EMERGENCY, which gives sysadmin members view only access. Logging is disabled in this state.
ALTER DATABASE SuspectDemoDB SET EMERGENCYCopy the data you need to a new database. Treat emergency state as temporary because the database is view only and unlogged.
Should You Repair a Suspect Database?
Only when no backup exists. Microsoft states that REPAIR_ALLOW_DATA_LOSS can cause more data loss than a restore and needs single user mode. Create physical copies of the files first.
For repair options and their limits see the guide to DBCC CHECKDB alternatives.
Frequently Asked Questions
What does Suspect Mode Mean in SQL Server?
Microsoft states that the primary filegroup can be damaged and that startup recovery failed. It is unavailable.
Can I Open a Suspect Database?
No. Set it to EMERGENCY state to get view only access as a sysadmin.
What is the Safest Fix?
Restore from the last known good backup. Microsoft recommends this first.
Does Emergency State Allow Writes?
No. Emergency state is view only and logging is disabled.
Should I Use REPAIR_ALLOW_DATA_LOSS?
No unless no backup exists. Microsoft calls it an emergency last resort.
Key Takeaways
- Suspect state means startup recovery failed for the database.
- Restoring from a good backup is the safest fix.
- Emergency state gives sysadmin members view only access to copy data.
- Copy the database files before any repair step.
- Repair options can delete data and come last.
Sources and References
This guide follows Microsoft documentation. Commands are untested.
- Database States (Microsoft Learn) learn.microsoft.com
- DBCC CHECKDB (Microsoft Learn) learn.microsoft.com