A SQL Server database marked suspect failed to recover on startup, so some damage is likely, usually in the primary filegroup or the transaction log. Read the SQL Server error log first to find the cause. Restore from a recent backup if you have one, since that loses nothing. With no backup, set the database to emergency mode, run DBCC CHECKDB to see the recommended repair level, and use the no data loss option before the one that can discard rows. When the file is corrupt and no backup exists, the Univik SQL Database Recovery tool reads the MDF directly to salvage the data.
A database that comes up marked Suspect is more serious than most of the states you will meet, and the advice online often makes it worse. The common recipe, reset the suspect flag then run the repair that can delete data, skips the two steps that actually protect your data. Fixing a suspect database in SQL Server is mostly a matter of order, running the steps that save the most data first, and being honest about where a database in suspect mode can still lose rows.
We build SQL recovery tools at Univik and have salvaged data from suspect databases since 2013, the ones with no backup and a file SQL Server would not touch. Suspect mode means recovery started and failed, so unlike a database merely stuck in recovery, real damage is on the table here. That changes the order of what you do, and it makes the backup matter more than any repair command.
What Suspect Mode Actually Means
SQL Server suspect mode is the state the engine sets when it tried to recover a database and could not finish. Recovery is the startup process that rolls committed transactions forward and uncommitted ones back to leave the database consistent. When that process hits damage it cannot work around, in the primary data file or the transaction log, it stops and marks the database Suspect rather than bring a possibly corrupt database online.
The word suspect is literal. SQL Server suspects the data is damaged, and at least the primary filegroup may be. That is the difference that shapes everything below. Where a recovery pending database is usually intact and only blocked, a suspect database has already failed its consistency check, so the odds of real corruption are higher and the backup becomes your best friend. Our guide to the MDF file format covers how the data and log files relate if you want that background.
Suspect or Recovery Pending
These two states get treated as the same problem and they are not. The fix that suits one can waste time or risk data on the other, so confirm which you have before you act.
If your database is actually in recovery pending rather than suspect, the fix is gentler and usually loses nothing, and our guide on a SQL Server database stuck in recovery is the one to follow. Confirm the state in plain text before you decide.
SELECT name, state_desc
FROM sys.databases
WHERE name = N'YourDatabase';Diagnose Before You Repair
Suspect mode has a cause, and the cause decides whether you restore, repair or rebuild. Reading the error log first is what separates a clean recovery from a panicked one that loses data it did not need to. Open the current error log filtered to your database.
EXEC sys.sp_readerrorlog 0, 1, N'YourDatabase';The log names what recovery choked on. An 823 or 824 error points to a real read problem on the disk, the operating system handing back bad data, which means genuine corruption. Two more errors show up again and again on a suspect database. Error 9003 means the log scan number is invalid, a sign the LDF and MDF no longer match. Error 3414 means recovery itself failed, and it names the choice plainly, fix the errors or restore from a known good backup. A log error sometimes rebuilds cleanly, while a data file error usually does not. A full disk or a missing file is a simpler cause with a simpler fix. Read the message, then pick the route below that matches it rather than running a repair blind.
How to Recover a Database from Suspect Mode
Restore from Backup First
If you have a recent, good backup, restoring it is the right move and it discards nothing. A suspect database has likely damage, and a clean restore replaces the damaged files with known good ones rather than repairing around the harm. Restore the last full backup, then any differential and log backups in sequence. This is why the backup matters more on a suspect database than on any other state, it is the one recovery that costs you no rows.
Recover with Emergency Mode when there is No Backup
With no usable backup, emergency mode lets you read the database and run a repair. Emergency mode is a read only state that opens a suspect database so you can pull data out or check it, and it needs the sysadmin role. Run these from the master database as a sysadmin, since a permission error on the ALTER DATABASE step usually means you are in the wrong database context or lack the role. This route is for user databases, because system databases like master and msdb cannot be set to emergency mode and follow a separate rebuild and restore process. The correct sequence checks the damage before it repairs, which is the step most guides skip.
-- 1. Open the database for reading
ALTER DATABASE YourDatabase SET EMERGENCY;
-- 2. Check the damage WITHOUT repairing, and read the recommended repair level
DBCC CHECKDB (YourDatabase) WITH NO_INFOMSGS, ALL_ERRORMSGS;
-- 3. Lock out other users
ALTER DATABASE YourDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;Step 2 is the one that protects your data. DBCC CHECKDB reports the errors and names the minimum repair level needed to fix them. When it recommends REPAIR_REBUILD, you are in luck, because that option repairs without losing data, rebuilding indexes and fixing minor issues.
-- Only if CHECKDB recommends it, this loses no data
DBCC CHECKDB (YourDatabase, REPAIR_REBUILD);Only when REPAIR_REBUILD is not enough, and CHECKDB names REPAIR_ALLOW_DATA_LOSS as the minimum level, do you run the option whose name warns you what it does. It forces the database consistent by discarding what it cannot repair, so take a copy of the files first, and understand that some rows may not survive. Our explainer on what REPAIR_ALLOW_DATA_LOSS really does covers the risk in depth.
-- Last resort, only when it is the recommended minimum level
DBCC CHECKDB (YourDatabase, REPAIR_ALLOW_DATA_LOSS);
-- Return the database to normal use
ALTER DATABASE YourDatabase SET MULTI_USER;A word on sp_resetstatus, since half the guides online open with it. That stored procedure turns off the suspect flag, and that is all it does. It does not repair anything, so clearing the flag on a genuinely corrupt database simply brings a broken database online. Emergency mode, which reads the database without pretending it is healthy, is the modern route and the one above. This approach follows the guidance of Paul Randal, who wrote DBCC CHECKDB, in his reference piece on emergency mode repair as the very last resort.
When the Repair Fails or Loses Data
Sometimes DBCC CHECKDB cannot repair the database, or the data loss repair discards the rows you most needed. When the corruption is severe or the metadata itself is damaged, the built in commands run out of road, and with no backup there is nothing to restore. One firm limit sends you straight here. Emergency mode repair cannot rebuild the log on a database that uses a memory optimized filegroup, the In-Memory OLTP feature, so a database with one has no repair path and needs a restore or a file level read. This is the corner a file level tool is built for.
The Univik SQL Database Recovery tool opens the MDF directly without attaching it to SQL Server, reads the tables, indexes and stored procedures from the raw pages, and recovers what it finds to a new database or an export. Because it never runs SQL Server’s own recovery, it can often read a file that SQL Server marks suspect and refuses to mount, and it can pull rows that a data loss repair would have thrown away. The free MDF Viewer shows what is readable before you commit to anything. This is a salvage route for the severe cases, not a replacement for a restore, which stays the best outcome whenever a backup exists. When the trouble is a missing log rather than a corrupt data file, our guide on recovering a SQL database without a backup covers the wider set of options.
Stopping Suspect Mode from Happening
Suspect mode is usually the visible end of a problem that was building quietly, and a few habits catch it early. Run DBCC CHECKDB on a schedule so corruption shows up in a maintenance window rather than at startup after a crash. Turn on PAGE_VERIFY CHECKSUM so SQL Server flags a bad page the moment it reads one. Set SQL Agent alerts for errors 823, 824 and 825, the input and output errors that warn of a failing disk before it takes a database down.
None of that helps as much as the backup you tested. A suspect database with a good, verified backup is a short restore, and a suspect database without one is the situation this tool and this guide exist for. Keep data and log files on healthy storage, exclude the MDF and LDF from antivirus scans so nothing locks them mid write, and verify your backups with a real restore rather than trusting that the job succeeded.
Frequently Asked Questions
What does It Mean When a SQL Server Database is Marked Suspect?
It means SQL Server tried to recover the database on startup and could not finish, so it flagged the database as likely damaged rather than bring a corrupt database online. The damage is usually in the primary filegroup or the transaction log. Suspect is more serious than recovery pending, where recovery never started and the data is usually fine.
How do I Fix a Suspect Database Without Losing Data?
Restore from a recent backup, which loses nothing. With no backup, set the database to emergency mode and run DBCC CHECKDB to read the recommended repair level. If it recommends REPAIR_REBUILD, that repairs without data loss. Only REPAIR_ALLOW_DATA_LOSS can discard rows, so use it only when CHECKDB names it as the minimum level and no backup exists.
Should I Use sp_resetstatus to Fix a Suspect Database?
On its own, no. sp_resetstatus only turns off the suspect flag, it does not repair anything, so clearing the flag on a corrupt database just brings a broken database online. The modern approach is emergency mode, which opens the database for reading without pretending it is healthy, then a consistency check to see what actually needs fixing.
Does DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS Always Delete Data?
Not always, but it can, which is why the name warns you. It forces the database consistent by discarding pages it cannot repair, so any data on those pages is gone. Run a plain DBCC CHECKDB first to see whether the lighter REPAIR_REBUILD option is enough, and take a copy of the files before running the data loss repair.
Can I Recover a Suspect Database With No Backup?
Yes, though the outcome depends on the damage. Emergency mode plus DBCC CHECKDB repairs many suspect databases, and where the repair would lose the rows you need or the file will not mount, a tool that reads the MDF directly, such as Univik SQL Database Recovery, salvages the data at the file level. A tested backup is still the cleanest recovery when one exists.
Why did My Database Go Suspect After a Power Failure?
A power loss or hard shutdown can interrupt SQL Server mid write, leaving a data or log file inconsistent. When SQL Server restarts and runs recovery, it finds the damage, cannot finish and marks the database suspect. A working backup and a UPS on the server are the two things that turn this from a crisis into a quick restore.
The Bottom Line
A suspect database is SQL Server telling you recovery failed and the data may be damaged, which makes the order of your response matter more than on any gentler state. Read the error log to find the cause. Restore from backup if you can, because that is the one recovery that loses nothing. Only with no backup do you enter emergency mode, and even then you check the damage with DBCC CHECKDB and use the no data loss repair before the one that discards rows.
The mistake that costs people data is running the data loss repair first, on the advice of a guide that skipped the diagnosis. Work the ladder from the top, restore before repair, and the safe repair before the destructive one. When the file is genuinely corrupt and no backup exists, a direct MDF read salvages what SQL Server cannot, but that is the last stop on the road, not the first.