SQL Server Database in SUSPECT or RECOVERY PENDING: What to Do

When SQL Server starts a database it runs recovery: it reads the transaction log and brings the data files to a consistent state. A SQL database is marked SUSPECT when that recovery started and failed. It shows RECOVERY PENDING when recovery could not even start. In both cases the data files are still on the disk, and in most cases most of the data in them is intact. The risk comes from the repair attempts that follow.

RECOVERY PENDING usually has a cause outside the database: a data or log file is missing, the drive is full, the drive letter changed, or the SQL Server service account lost permission to the folder. SQL Server could not open what it needs, so it waits.

SUSPECT means SQL Server opened the files and found something it could not recover from: a damaged log, damaged pages in the data file, or a file that was cut short when the server lost power or the storage failed. The SQL Server error log names the file and often the page.

Before any command, stop the SQL Server service or take the database offline, and copy the MDF, all NDF files and the LDF to another drive. Every repair command changes these files. With an untouched copy, a failed attempt costs you nothing; without one, it can cost you the database.

Read the SQL Server error log from the time the database went down. Then check that every file listed for the database exists at its path, that the drive has free space, and that the service account can read and write the folder. If one of these was the problem, fix it and bring the database online again; SQL Server runs recovery once more and the database often opens normally.

If you have a recent backup that restores cleanly, restoring it to a new database name is the safest route. Test the restore under another name before you replace anything.

When the database stays SUSPECT, setting it to EMERGENCY makes it readable by an administrator without running recovery. In that state you can often run SELECT statements and copy the tables out to a new database. This is reading, not repairing, and it is the right next step.

DBCC CHECKDB without a repair option tells you how much is damaged and where. Run it, read the result, and decide with that information.

REPAIR_ALLOW_DATA_LOSS does what its name says: it makes the database consistent by deleting the pages it cannot fix, and the rows on them are gone. Rebuilding the log discards transactions that were never written to the data file. Detaching a suspect database can leave you with files that will not attach again. None of these should be tried on the only copy.

If the database will not come up even in emergency mode, the data can still be read from the MDF file directly, page by page, without SQL Server. Every page is checked, the tables and rows on intact pages are recovered, and the damaged pages are listed. The free edition of AS SQL Recovery & Repair Studio shows what is recoverable before you decide anything.