1. Run a checkdb
DBCC CHECKDB ([db_name_here]) WITH ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS2. Ensure there is enough free disk space for the data files and transaction log files to grow.
3. Check SQL Server and Windows event logs to see if can determine when corruption started, any errors or events that may have contributed to corruption, when last restart of instance, last online of database, I/O interruptions, power outages, etc. If “under the gun” to get db back online, this step may have to be performed post-mortem.
4. Attempt repair
EXEC sp_resetstatus 'db_name_here';
ALTER DATABASE [db_name_here] SET EMERGENCY
DBCC CHECKDB('db_name_here')
ALTER DATABASE [db_name_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB('db_name_here', REPAIR_REBUILD)
ALTER DATABASE [db_name_here] SET MULTI_USER
5. If REPAIR_REBUILD does not fix it and data loss is a concern, then make copies of the *.ldf, *.mdf, and *.ndf files.
You can then attempt to restore the database from last good backups and transaction log backups up to the point of corruption, or attempt a REPAIR_ALLOW_DATA_LOSS
EXEC sp_resetstatus 'db_name_here';
ALTER DATABASE [db_name_here] SET EMERGENCY
DBCC CHECKDB('db_name_here')
ALTER DATABASE [db_name_here] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
BACKUP DATABASE [db_name_here] TO DISK = N'R:\backup_path_here\db_name_here.bak'
DBCC CHECKDB('db_name_here', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [db_name_here] SET MULTI_USER
6. If possible, can try restarting instance. If not possible to restart instance due to causing other DB outages, then attempt to offline database, then set back online. Sometimes suspect mode is due to a problem during the on-lining of the db and a second attempt to online db clears up suspect issue. Of course, in extreme cases, attempting this may result in the database not being able to online at all…
7. Once you do get it back online run a checkdb and a full backup
DBCC CHECKDB ([db_name_here]) WITH ALL_ERRORMSGS, DATA_PURITY, EXTENDED_LOGICAL_CHECKS
BACKUP DATABASE [db_name_here] TO DISK = N'R:\backup_path_here\db_name_here.bak'
NOTE: See the suspect database a lot on EXPRESS editions and databases with AUTO_CLOSE enabled. If this is the case then sometimes the database does not “open” properly and reports as suspect. You can clear the suspect by offline then online the database. When it is back online, recommend turning the AUTO_CLOSE off.