How To Recover SQL Server Suspect Database
One of the common situations that are faced by the users of SQL Server is while trying to connect to the database server; he/she realize that it is in suspect mode. The SQL Server Suspect Database server in suspect mode will not allow users to perform any operation on that database before it is being repaired. In such situations, the only solution is to find out ways on how to recover SQL Server Suspect Database. In the article, we will be discussing some causes that can make the database suspect and methods to recover database from suspect mode.
Reasons That Makes Database Marked as Suspect
Some of the reasons that can make the SQL Server Database marked as Suspect are as follows:
- The operating system is not able to find the location of SQL Server database file.
- Insufficient Memory or Disk Space can make the Database file inaccessible.
- Another reason can be a corruption of SQL Server Database files.
- Abrupt SQL Server Shutdown or failure of Power/Hardware.
- SQL Server Database is unable to access device that stores LDF & other files.
Methods to Recover SQL Server Suspect Database
Before recovering the SQL Server Database from the suspect mode, the user can run the following to check the logical and physical integrity of all objects in the database and reason that has made the database suspect.
DBCC CHECKDB (‘DBName’) WITH NO_INFOMSGS, ALL_ERRORMSGS
The parameters used in the command are:
DBCC CHECKDB: will check the integrity of the database.
WITH NO_INFOMSGS: will suppress all the informational messages.
ALL_ERRORMSGS: will display all the reported errors per object in the database.
In order to recover SQL Server Database from the suspect mode, the user can take reference of the following ways stated below:
Manual Approach using SQL Server Management Studio
Steps involved in the manual procedure on how to recover SQL Server Suspect Database are:
- Connect the SQL Server Database using SQL Server Management Studio and execute the following command to identify the databases that are in SUSPECT mode.
SELECT NAME, STATE_DESC FROM SYS.DATABASES
- The user can open the error log by expanding Management Node > SQL Server Error Logs. Check the errors logged for the database that is marked as suspect in the latest SQL Server Error Log.
- In this step, the user needs to bring the SQL Server Suspect Database to Emergency Mode first, a user would not be able to connect with the suspect database. The user can run the following command to bring the database into EMERGENCY mode:
ALTER DATABASE DBO SET EMERGENCY
Note: Emergency Repair Mode should only be chosen knowing all the risks involved in this procedure, as the action cannot be rolled back once started. Backup of data should always be taken, if possible before using Emergency mode repair.
- After changing to Emergency mode, the database is in read-only state and has only system admin privileges restricting any other user to access the database.
- To check the logical & physical integrity of all objects in the database, the user can run the below command:
DBCC CHECKDB (DBO)
- The user will set the database into the single user mode using the command ‘Rollback Immediate’.
ALTER DATABASE DBO SET SINGLE_USER WITH ROLLBACK IMMEDIATELY
The above command will check if any rollback is available and set the database to single user mode.
- To repair the database, the user can use REPAIR_ALLOW_DATA_LOSS parameter of DBCC CHECKDB command.
DBCC CHECKDB (DBO, REPAIR_ALLOW_DATA_LOSS)
This command may repair the database but it will also lead to the loss of some data during the procedure. Once the database is repaired using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command, it cannot return to the previous state.
- In the last step, we will again set the database to MULTI_USER mode to allow multiple users to access the database using this command:
ALTER DATABASE DBO SET MULTI_USER
Recover Suspect SQL Server Database Using a Third-Party Tool
Another alternative that can be used by the user to recover SQL Server Suspect database without data loss is with the help of Third Party Tool. Steps to carry out the recovery process are as follows:
- Launch the Tool after its installation and click on ‘Open’ button to add the Database file that is in suspect mode.
- The tool will give the detail of the complete scanning of the SQL Server Database file.
- Preview of the recovered database will be shown including all the tables, triggers, and all the other elements. The user needs to click on the Export option to begin exporting the recovered database.
- The user can export the recovered suspect database into two ways: SQL Server database and SQL Server Compatible Scripts. Choose Export of file either with the only schema or with Schema and data.
- After successful export of the database that was in suspect mode, a confirmation message will be shown.
After studying various methods on how to recover SQL Server Suspect Database, we have observed that the manual recovery approach involves one-way operation such as REPAIR_ALLOW_DATA_LOSS command. This operation involves data loss and the action cannot be reversed even after the database has been repaired. That is why, it is advisable to create the backup of the database, if possible before executing these commands. Additionally, a user can go through the steps involved in third party tool to repair SQL DB that is in Suspect mode.