The problem of the suspect database appears quite often when the user is connecting to the SQL Server Database. In the following material, we’ll provide a few solutions to this issue with Transact-SQL options in SQL Server Management Studio (SMS), and Recovery Toolbox for SQL Server https://sql.recoverytoolbox.com/.
The Suspect Mode of SQL Database is quite a common problem that may appear during the server connection.
How to get rid of Suspect Mode?
If the SQL server database is indicated as suspicious, the connection to it will be lost. In this case, access to a database and recovery procedure through a server restart won’t help.
To fix the Suspect Mode of the Database, you may use one of the following solutions:
- Repair through existing backup;
- Use T-SQL instruments;
- Run Recovery Toolbox for SQL Database Server.
Repair With the Existing Backup
One of the easiest ways to fix suspect mode is by using a recent backup. In some cases, it may be helpful. If it’s not available, use other options.
T-SQL Operations
To repair the SQL database suspect, follow this algorithm:
- Go to SSMS to reach the database.
- Enter the New Query transaction.
- In the window for editing, enter the next code to remove the database suspect mark. Then, send it to EMERGENCY using T-SQL operations.
EXECsp_resetstatus‘db_name’
ALTER DATABASE db_name SET EMERGENCY
- Check consistency on the master database by entering the code in the editing panel.
DBCC CHECKDB (‘database_name’)
- Turn on the mode of a Single-User in your database and go back to the last operations using the code.
ALTER DATABASE database_nameSET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Create a full backup of your database file.
- Try to restore the Database even if some data may be lost. Use the transaction.
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)
Switch to the Multi-User Mode.
ALTER DATABASE database_nameSET MULTI_USER
Restart the server.
If you’ve completed all the steps, check the server connection. If some data disappeared, you may use the created backup and make a restoration procedure.
Recovery Toolbox for SQL Server
If the method described above didn’t remove the suspect error, there is an alternative solution.
If the server database is severely damaged, the described procedure may be useless. In such a case, you may turn to Recovery Toolbox for SQL Server.
The software serves to fix typical corruption errors of the SQL database that may appear as a result of suspect working mode and some others. The tool is based on efficient algorithms, so, most likely, you will be able to fix the problem, and your database will turn back to a normal working mode.
Note! Before starting the software of Recovery Toolbox for SQL Server, you need to stop the server itself.
For fixing the issue, follow the algorithm:
- Load, proceed installation and start Recovery Toolbox for SQL Server
- In the Select window, indicate Browse for choosing a damaged object (*.mdf/*.ndf files)
- When the file is chosen, move on
- Make a preview of the chosen file
- Move on
- Save the repaired objects
- Use the following options:
- Save scripts to disc
- Indicate the directory root
- Move to the Next step
- Select all the objects via confirmation action
- Start recovery procedure
- Create a new database using previously saved scripts.
Conclusion
The article provides several efficient ways for the recovery procedure of the MS SQL database from suspect mode. Here one can find the detailed algorithms for fixing the error, including the algorithm of using Recovery Toolbox for SQL Server.
