Overview

There are multiple numbers states of a SQL Database. These states determine the current status or mode of database in SQL server.

At once, the database is only in one state, which may be any one of the following:

• Online
• Offline
• Restoring
• Recovering
• Recovery Pending
• Emergency
• Suspect

In this blog, we are going to discuss about all these states in detail.

Description Of Several States of SQL Database

Online: This server state indicates that the database is accessible for end users. The primary filegroup is online, even if the recovery process of undo phase has not been finished.

Offline: The state illustrates that the database is unavailable. This state of server is caused by explicit user action and it remains in offline mode until & unless users don’t perform any action on database.

Note: To determine whether the server is in offline state or in an online state, one can run the following command:

SELECT * FROM sys.databases

After running this command, you will find a table with ColumnName and Value attributes. In this table, search for state_desc option in ColumnName and check the Value attribute that whether it is Online or Offline.

Restoring: In this state of SQL database one or more data files are being restored from primary filegroup. Apart from this, the state also occurs when one or more secondary files are being restored in offline mode (i.e., database is unavailable). After completion of restoration procedure, the database is modified into an useable state and hence, users can now access the restored database.

Recovering: At the time of restarting the SQL server, all database undergoes “Recovery” process. In this state, the database comes in its consistent state i.e., during the process its state changes from recovery state to online state. Moreover, the recovering procedure is performed in three phases i.e., Discovery, roll forward, and Rollback

• Discovery Phase: The first phase discovers that where will the server proceed through log file. During its discovery procedure, it built-in the memory structure for determining the memory required for going to next phases.
• Roll Forward Phase: The second phase determines the transactions that were committed while shutting down the database but not yet written to MDF file through checkpoints.
• Rollback Phase: Last phase of this recovering process determines whether there are any uncommitted transactions in database or not. If yes, then they first roll back those transactions and than bring database into consistent state.

TIP: With help of following command, one can recover a database, which is in restoring state:

RESTORE DATABASE ADventureWorks WITH RECOVERY

GO

Recovery Pending: While working with the server, users encounter an error that are related to resources during recovery process. In such cases, database is not corrupted or damaged. This state occurs when some data files are missing or there is some problem with system resources. Therefore, some external operations are being required by users to resolve this error state and continue the recovery process until its end.

Suspect: In this state, the recovery gets started but does not reach to its completion. It does not allow users to connect with database and can be considered as an inconsistent state. However, the common reason behind occurrence of such state is that the primary filegroup may be corrupted & hence, in suspect. Therefore, this state also requires some additional actions that repair the corrupted file & recover data from it.

Emergency: This state is enable by users by making some default changes. Such state implementation can only be performed in single-user mode and could be restored or repaired. In such state, database is in READ_ONLY state, logging is disabled, and access is restricted. Suppose a database is find out as a suspect can be set to emergency state, which will allow admin to perform read-only operation within it. Hence, no one can perform any updation in such database that is in emergency mode.

Conclusion

One can conclude with the fact that some of the states of SQL database are caused due to damage in database files therefore, users are recommended to use a recovery utility in order to recover data from SQL server database files and export that recovered data into SQL server database. One such utility is SQL Database Repair Tool, which recovers entire data from corrupted database file by performing scanning operation on source file. After completion of scanning operation, it convert the source file into SQL server database file. Moreover, it supports MDF and NDF files of the server. Therefore, the server users could use this third-party utility to take out the database from an inconsistent state.