Sev1: SQL Server database stuck in RECOVERY_PENDING or SUSPECT

#The two states

SELECT name, state_desc, user_access_desc FROM sys.databases WHERE state_desc <> 'ONLINE';

#Step 1: read the error log

EXEC sp_readerrorlog 0, 1, N'SalesDB';

Look for:

• Operating system error 2/3 (file not found), 5 (access denied), 112 (disk full).
• Error 9001 (log not available), 824 (consistency error), 3414 (error during recovery).

#Step 2a: RECOVERY_PENDING, so fix the files

SELECT name, physical_name, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'SalesDB');

• Missing disk or drive letter: bring the disk online / restore the letter.
• Permissions: grant the SQL Server service SID full control on the folder.
• Disk full: free space (often the log disk).

Then:

ALTER DATABASE SalesDB SET ONLINE;

If it comes online, run DBCC CHECKDB (SalesDB) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm it's clean.

#Step 2b: SUSPECT, so prefer restore

If you have good backups, restore is the safe path:

-- Take a tail-log backup first if the log is readable (keeps the latest transactions)
BACKUP LOG SalesDB TO DISK = N'B:\Backup\SalesDB_tail.trn' WITH NO_TRUNCATE, CONTINUE_AFTER_ERROR;

RESTORE DATABASE SalesDB FROM DISK = N'B:\Backup\SalesDB_full.bak' WITH NORECOVERY, REPLACE;
RESTORE LOG SalesDB FROM DISK = N'B:\Backup\SalesDB_log_01.trn' WITH NORECOVERY;
-- ...all log backups in order...
RESTORE LOG SalesDB FROM DISK = N'B:\Backup\SalesDB_tail.trn' WITH RECOVERY;

If the database is in an AG, check whether another replica has a healthy copy. Failing over may be faster than any restore.

#Step 3: last resort, emergency mode repair

Only if there is no usable backup, and the business accepts data loss.

ALTER DATABASE SalesDB SET EMERGENCY;
DBCC CHECKDB (SalesDB) WITH NO_INFOMSGS, ALL_ERRORMSGS;     -- see the damage first
ALTER DATABASE SalesDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (SalesDB, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
ALTER DATABASE SalesDB SET MULTI_USER;

Before repairing, copy the database files (or take a COPY_ONLY backup if possible) so you can try again. In emergency mode you can also read data out with SELECT / bcp into another database.

Caution

REPAIR_ALLOW_DATA_LOSS deletes whatever it can't fix: rows, pages, and in emergency mode it may rebuild the transaction log, breaking transactional consistency. Constraints and relationships can be violated afterwards. Treat the result as damaged and validate with the application owners.

#Step 4: after it's online

• Full backup right away; restart the log backup chain.
• DBCC CHECKDB on the other databases on the same storage.
• Check msdb.dbo.suspect_pages and storage/hardware logs for the cause.

#Decision table

#Prevent it next time

• Tested restores, not just backups.
• PAGE_VERIFY CHECKSUM, alerts on 823/824/825, weekly CHECKDB.
• Monitoring on disk free space and disk availability.