How to work in various SQL Server Database States

When you opened your database, it shows in one of the following state: ONLINE, OFFLINE, RESTORING, RECOVERING, RECOVERY PENDING, SUSPECT.   If you are not sure about your database state then you can also check it by selecting the state_desc column in the sys.databases catalog view. 

It is very necessary to understand that What is the exact meaning of these states & How's SQL behave in these states. Let me explain it:

Mean of these states:

ONLINE: You can easily access your database in online state.

OFFLINE: You can not access your database in offline state. In some cases, You need to set your database offline like you want remove all access to a database for a period of time or you want to move database files. You can set database offline by following options: 

1. Executing ALTER DATABASE command for setting database option to OFFLINE or ONLINE.

2. Executing sp_dboption system stored procedure for setting the offline property to true for OFFLINE database or setting to false for ONLINE database status

3. Take SQL Database Offline using Microsoft SQL Server Management Studio Object Explorer

Restoring: One or more files of the primary filegroup are being restored. If your database shows in “Restoring” then You need to use the WITH RECOVERY option, with your database RESTORE command, to bring your database online.

RECOVERING: If the database shows in 'Recovering' state, it means that the database is being recovered. TO bring your database online restart the sql service.

RECOVERY PENDING: If the database shows in 'Recovering Pending' state, it means that the database encountered an error which prevent recovery process from starting.  TO bring your database online, First Stop the SQL service & Free up some space & Restart the SQL service.

SUSPECT: If the database shows in 'Suspect' state, it means that primary filegroup get suspected and may be damaged. In this case, database should be restored from backup and logs reapplied. That’s the first and generally recommended approach any time a DB is suspect.  There is one another option emergency mode repair which used as a last resort if your database has no backups. 

EMERGENCY:  The EMERGENCY state used for troubleshooting purposes like if your database marked as suspected then you should set it to the EMERGENCY state. In this case the database is in single-user mode & permitted to system administrator to repair database. 

From all above states, suspect state is very tough to handle. Because when we used emergency mode repair option to repair suspect database & it get failed then there is no another way to repair database. Hope the article will help you to understand these states & to get back your database online from these states.