Suspect Database in SQL Server? Fix It in 5 Ways

What Does a Suspect Database Mean in SQL Server?

SQL Server sets the SUSPECT state when at least the primary filegroup is suspect and the database cannot recover during startup. Error 3414 reports the failure and says that an error during recovery prevents the database from restarting.

Suspect databases are unavailable until you act. Suspect tempdb shuts down the SQL Server instance.

What Causes a Database to Go Suspect?

Five causes explain most suspect databases.

• Bad Checksum Pages. Error 824 reports a bad checksum and appears in the log before error 3414.
• Log and Data Mismatch. Error 9003 reports an invalid LSN from corrupt data or a log file that does not match the data file.
• Startup Condition. Microsoft names a transient condition during startup as one cause of error 3414.
• Permanent Failure. Damaged files and failing disks make the recovery failure permanent.
• Primary Filegroup Failure. SQL Server sets the SUSPECT state when recovery of the primary filegroup fails.

How to Find a Suspect Database in SSMS?

Paste the query below into a New Query window and execute it. Databases with the SUSPECT value in the state_desc column need the fixes in this guide.

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

Check the SQL Server error log next. In Object Explorer open Management, right click SQL Server Logs and choose View then SQL Server Log.

What to Check Before the Repair?

Protect the evidence first.

• Files. Stop the SQL Server service and save duplicates of all database files (MDF, NDF and LDF) on a separate disk.
• Log Lines. Save the error log lines around error 3414.
• Permissions. Sign in with a login from the sysadmin role.
• Backups. Find the newest full backup and the log backups made after it.
• Space. Confirm that the disk with the files has free space for a restore.

Which Free Methods Repair a Suspect Database?

Four free methods fix the status. Methods below start with the lowest risk and end with the highest risk.

Fix the Cause and Recover Again (Free)

Microsoft separates correctable errors from permanent failures. Correctable errors need the cause fixed and a new recovery attempt.

1. Find the first error before error 3414 in the log.
2. Fix the cause. Free disk space, restore a missing file or give the service account permission to the file.
3. Execute the command below. SQL Server tries the recovery again.

ALTER DATABASE [CustomerDB] SET ONLINE;

4. Query sys.databases again to confirm the state.

Move to the next method when the state returns to SUSPECT.

Restore the Last Good Backup (Free, Safest)

Microsoft lists a restore from a known good backup as the preferred action for error 3414.

1. Open Object Explorer, right click Databases and choose Restore Database.
2. Select the Device source type and use the browse button.
3. Set the media type to File, click Add and choose your BAK file.
4. Click OK and tick the backups to restore in the grid.
5. On the Options page enable Overwrite the existing database (WITH REPLACE) and confirm RESTORE WITH RECOVERY as the recovery state.
6. Click OK.

Use the T-SQL version below for the same restore.

RESTORE DATABASE [CustomerDB]
FROM DISK = N'D:\SQLBackups\CustomerDB.bak'
WITH REPLACE, RECOVERY;

Restoring brings back data only up to the time of the last backup you apply.

Copy the Data Out in Emergency Mode (Free)

Microsoft suggests emergency mode when no backup exists. Set the database to EMERGENCY and copy as much data as possible into another database.

1. Create an empty database for the rescued data.
2. Set the suspect database to emergency mode.

CREATE DATABASE [CustomerDB_Rescue];
ALTER DATABASE [CustomerDB] SET EMERGENCY;

3. Copy a table with a command like the one below. Copies contain rows but no indexes or constraints.

SELECT * INTO [CustomerDB_Rescue].dbo.Orders
FROM [CustomerDB].dbo.Orders;

Emergency mode sets the database to READ_ONLY with logging disabled and restricts access to sysadmin members.

Repair the Database in Emergency Mode (Free, Risks Data Loss)

Use the repair when you cannot restore a backup and cannot copy the tables. Microsoft states that the repair option is a last resort and cannot replace a restore.

1. Copy the database files first.
2. Execute the commands below. Repair options need single user mode.

ALTER DATABASE [CustomerDB] SET EMERGENCY;
ALTER DATABASE [CustomerDB] SET SINGLE_USER;
DBCC CHECKDB (N'CustomerDB', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
ALTER DATABASE [CustomerDB] SET MULTI_USER;

3. Execute DBCC CHECKCONSTRAINTS on the database and take a full backup.

DBCC CHECKDB treats pages with I/O or checksum errors as if no error occurred in this mode and attempts a normal log based recovery. Microsoft says SQL Server rebuilds the log when recovery fails because of log corruption and the rebuild can break transactional consistency.

Check the lower repair levels in the Integrity Check Failed Fix guide.

How to Fix a Suspect Database With EmailDoctor Software?

Choose EmailDoctor SQL Database Recovery after a failed restore and a repair that removes too many rows. Scanning the MDF file of the suspect database rebuilds tables, views, stored procedures, functions, triggers and rules in a database you choose.

Free demo versions preview every recovered object. Demo exports cap at 25 records for a table and 5 for other database objects.

You need .NET Framework 4.5 or higher and 100 MB of disk space on Windows 7, 8, 10 or 11. Microsoft SQL Server files from version 2000 to 2019 work and 4 GB of RAM is recommended.

Create an empty database named CustomerDB_Rescue in SSMS before you start. Step 9 exports into that database.

Scan the MDF File

1. Find the recovery software in the Windows Start menu and open it.
2. Click Open, browse to the MDF file copy and select it.
3. Select Quick Scan and tick Auto detect for the SQL Server version. Choose the version from the list when auto detect fails.
4. On the NDF Options tab choose between auto detect and manual addition with Add Files or Add Folder.
5. Wait for the scan report and press OK on the Quick Scan note.

Preview the Recovered Objects

6. Select any table in the left panel and check its records in the preview.

Export to the Empty Database

7. Click Export and choose the SQL Server database format.
8. Type the SQL Server name or choose one from the list. Then sign in with Windows Authentication or SQL Server Authentication.
9. Choose Export to Existing Database as the destination.
10. Select the empty rescue database in the database list.
11. Tick the tables and other objects you need.
12. Choose With Schema & Data and then click Export.
13. Press OK on the Export Completed message and close the status window.
14. Refresh Databases in SSMS and open the rescue database to check your tables.

    Choose Advanced Scan when Quick Scan lists fewer tables than you expect. Our MDF File Repair Methods guide compares both scan modes.

How to Choose Between the Five Methods?

Compare the five methods before you start.

Start with the cause and the backup. Try the emergency repair only after the copy fails.

Which Snags Slow Down a Suspect Database Fix?

Watch for these snags during the fix.

• ALTER DATABASE Fails. Connect with a sysadmin login because the command needs high permissions on a suspect database.
• Repair Option Refused. SQL Server rejects REPAIR options until the database is in SINGLE_USER mode.
• Status Returns After a Restart. Fix the disk or storage fault that error 824 reports.
• Log File Is Missing. Emergency repair rebuilds the log file but the rebuild breaks transactional consistency.
• Restore Refuses to Overwrite. Tick the WITH REPLACE option on the Options page.
• Copy Lacks Indexes. Create the indexes again in the rescue database with scripts from your source control.

Frequently Asked Questions

Final Words

Suspect status means SQL Server failed to recover your database during startup. Find the first error before error 3414 because it names the cause.

Save duplicates of the files before any repair and restore a good backup when one exists. Treat the emergency repair as your final free option.

EmailDoctor software opens damaged MDF files and stores the recovered objects in a database of your choice.