SQL Server Database States
A SQL database is considered to be damaged if one or more of its core files are in an inconsistent state. Depending on how severe the damage is, the db is marked with different states. Some of these states are:
Online – If one of the data files is damaged when executing a query or some other operation, the database will remain online and accessible.
Suspect – If a database cannot be recovered during startup of SQL Server, the database is marked as Suspect.
Recovery Pending – If the SQL Server knows that database recovery needs to be run but something is preventing it from starting, the Server marks the db in ‘Recovery Pending’ state. This is different from the SUSPECT state because it cannot be said that recovery is going to fail – it just hasn’t started yet.
1. Mark Database in Emergency Mode and Initiate Forceful Repair
Database EMERGENCY mode marks the database as READ_ONLY, disables logging, and grants access only to system administrators. Essentially, setting the db in this mode can bring the inaccessible database online.
Once you have opened the db in EMERGENCY mode, try repairing the database using the DBCC CHECKDB command with the ‘REPAIR_ALLOW_DATA_LOSS’ option. To do so, open SSMS and execute the following set of queries:
ALTER DATABASE [DBName] SET EMERGENCY;
GO
ALTER DATABASE [DBName] set single_user
GO
DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [DBName] set multi_user
GO
2. Mark Database in Emergency Mode, Detach the Main Database and Re-attach It
This solution also requires to mark db in EMERGENCY mode. Once done, take the database offline (detach) and then bring it online (re-attach). To do so, execute the following set of queries in SSMS:
ALTER DATABASE [DBName] SET EMERGENCY;
ALTER DATABASE [DBName] set multi_user
EXEC sp_detach_db ‘[DBName]’
EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’
