Suspect mode is a critical situation of MS SQL Server when one or more SQL databases become inaccessible and SQL administrator is not able to manage database items
on the MS SQL Server. There are several possible reasons of SQL Server turning in suspect mode:
There are various options to restore suspect MDF database files or tables such as SQL database backup, DBCC CHECKTABLE command utility and third party SQL recovery tool.
SQL database Backups:
Creating database backups always help business organization in restoring crucial data in case original database files become corrupt or damaged. You can use MS Windows backup or Winzip tool to save MDF tables of your organization in a single folder. Alternatively, one can also use third party data compression tool to compact all MDF tables in a single zipped folder. A single MS backup or zipped folder can store entire database objects of MS SQL Server. You can restore both MDF and LDF files of a suspected database from SQL database backup using Transact SQL RESTORE command.
DBCC CHECKTABLE tool:
DBCC CHECKTABLE is the command tool of Microsoft SQL Server that can recover objects from the corrupt or suspected MDF table. It comprises of a set of command line such as ALTER, RENAME, MOVE, COPY and many more.
You can use DBCC CHECKTABLE command lines from the SQL Server Management Studio Windows. If you use express version of MS SQL Server, then DBCC CHECKTABLE commands can be run from the SQL Server Management Studio Express. Before you can use DBCC repair command lines to rebuild a suspected SQL database, switch that file in the single user mode. Use the Transact- SQL statements to repair corrupt MDF table for example MRF_typre_sales.MDF are given below:
ALTER DATABASE MRF_tyre_sales.MDF
SET single_user WITH ROLLBACK IMMEDIATE;
DBCC CHECKTABLE (MRF_tyre_sales.MDF, REPAIR_REBUILD) WITH ALL_ERRORMSGS;
After recovery of the Master database file, you must switch the file into multi-user mode using following T-SQL commands:
ALTER MRF_tyre_sales.MDF SET MULTI_USER;
Now the MRF_tyre_sales.MDF file can be accessed by all users. You must not use REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command as it may result in the loss of crucial data of your original database. If you’re using REPAIR_FAST option of the DBCC CHECKTABLE command, then only syntax errors of a SQL table (MDF or LDF) will be fixed.
Third party Recovery Software
Restoring corrupt or suspected SQL database using T-SQL commands is a tiresome procedure. Moreover, T-SQL commands cannot be used if MS SQL Server has severely or completely collapsed. Third party software such as SQL recovery can help you in the complete recovery of suspected or inaccessible SQL databases. It extract all the objects such as indexes, fields, primary keys, triggers, stored procedures, unique keys and many more from the inaccessible SQL database. SQL tool successfully recover MDF tables of all SQL Server versions such as SQL Server 2008, 2008 R2, 2012, 2000 and 2005. SQL recovery software is capable of recovering large SQL databases of an organization.