Disaster Recovery in SQL Server and Repair Suspect Databases

Microsoft’s Relational Database Management System (RDBMS) probably known as SQL Server is a wholly featured database designed primarily to compete against competitors in MySQL and Oracle Database.

SQL Server, like all other major RDBMS, supports ANSI SQL, the standard SQL language. SQL Server also contains T-SQL and its implementation. SSMS- the SQL Server Management Studio known as Enterprise Manager earlier is SQL Server’s main interface tool supporting 32-bit and 64-bit environments.

Sometimes, the SQL Server is referred to as Microsoft SQL Server and MYSQL.

In some circumstances, possibilities or instances such as “Your account is suspended” due to distinct database errors and malfunctions can be encountered. In such scenarios, you should be capable of responding to the particular message and make your account activated again using some of the predefined/user-defined programs in MySQL.

These programs are available for MySQL server 2016 and its higher versions.

In other scenarios, there are possibilities that when you connect to an instance of SQL Server, you will find that the database is marked as SUSPECT. In such scenarios, you are unable to connect to the database.

In this article, I am going to discuss the sequences which are necessary to follow and recover a database that is marked as SUSPECT. The steps mentioned here also work on SQL Server 2005 and its higher versions.

Some significant reasons why an SQL Server database can be stamped as SUSPECT

• The database could have been corrupted
• Not enough space is available to recover during the database startup in the SQL Server.
• Due to insufficient disk space or memory or may be due to inaccessible files, the database cannot be opened.
• The files in the database are held by the third party backup software or the operating system.
• Unexpected Power failure or a hardware failure or suspicious SQL Server Shutdown.

Now Let’s discuss the steps required to Recover a Database that is Marked as SUSPECT

USE master
GO
SELECT NAME, STATE_DESC     FROM SYS.DATABASES
WHERE STATE_DESC='SUSPECT'
GO

To identify all the suspected databases, Run the below-mentioned TSQL code:

• Open the latest SQL Server Error Log
• Check for the errors in the databases marked as SUSPECT in the database you have logged in
• The other way to access the error log in SQL Server is through Node expansion
• Management Node->SQL Server Error Logs.
• In my server, you could find the entries in SQL Server Error Log

Here is the sample of an error message in the SQL Server Error Log when any particular database is SUSPECT and marked.

Beginning the database 'ELEARNING.'

Error: 9003, Severity: 20, State: 9.

The log scan number passed to log scan in database 'ELEARNING' let’s say (189624:16:2) is not valid.

It may indicate that the data corruption or that particular log file does not match with the data file.

Otherwise, restore from backup if the problem fails during startup.

Note that it is better to restore from backup if mainly the problem fails during startup.

Error: 3414, Severity: 21, State: 1.

During recovery, an error occurred 'ELEARNING' (database ID 10).

Diagnose the errors to recover and fix them, restoring the available support.

In case if errors are not expected or corrected, contact the technical support team.

CHECKDB for database 'ELEARNING' finished without errors on 2009-12-15 11:30:28.320 (local time).

You can’t fix SQL Server Performance errors until you identify the cause. Download the functional database execution analyzer and check how it will pinpoint SQL Server Performance Issue.

In the case of the database being in SUSPECT mode, you will not be able to get connected to it. Hence it is essential to bring the database first to an EMERGENCY mode for repairing the database. To bring the database to EMERGENCY mode, execute the below mentioned TSQL code.

USE master
GO
ALTER DATABASE ELEARNING SET     EMERGENCY
GO

Once the database enters EMERGENCY mode, you can send a query to the database. However, it doesn’t mean that your database is out of trouble.

Step 4:

DBCC  CHECKDB  (ELEARNING)
GO

Use the command DBCC CHECKDB to check the physical and logical object integrity within the specified database.

ALTER DATABASE ELEARNING SET     SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

5. The further step is bringing the user database into SINGLE_USER mode using the executable code mentioned as follows:

6. If the database is in the mode of a SINGLE_USER run the TSQL code to repair the database. When you restore your database using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command, there are chances of some data loss. 

DBCC CHECKDB (ELEARNING,     REPAIR_ALLOW_DATA_LOSS)
GO

Once the database is expertly repaired utilizing REPAIR_ALLOW_DATA_LOSS choice of DBCC CHECKDB command, at that point, there is no absolute way to return to the prior state.

ALTER DATABASE ELEARNING SET     MULTI_USER
GO

7. The final step is to execute the TSQL command allowing MULTI_USER access to the database.

Therefore, this is the process to recover a data that is marked, SUSPECT.