There are two kinds of customers: The ones who do backups and those who didn’t have data loss yet, ignoring all good advice and best practices. However, in the end it’s always us, the IT crowd being yelled for to look at the shards and doing a miracle.

A broken database at the MSSQL server is marked as ‘SUSPECT’ and is pretty much useless. To get round some transaction log issues, you can try to start it up in ‘EMERGENCY’ mode, which is a read-only mode. For safety reasons, it’s recommended to prevent other users from accessing the database using single user mode:


ALTER DATABASE dbName SET EMERGENCY
ALTER DATABASE dbName SET SINGLE_USER


The database is now prepared to do some repair jobs using CheckDB:


DBCC CheckDB (dbName , REPAIR_ALLOW_DATA_LOSS)


If things went okay, you can open the database for multi user usage again as things were before and do a backup.


ALTER DATABASE dbName SET MULTI_USER


Personally I’d copy the database into a new one, no matter if things could be fixed or not to prevent further damage…