Getting Started with SQL Server database state

A database can have many states but it can only be in one particular state at a time. Similarly SQL database can be in any state like ONLINE, OFFLINE, SUSPECT, RESTORING etc. In SQL you can also check the current state of a database by following command;

SELECT DB_NAME() AS Databasename, DATABASEPROPERTEYX( 'Emp' , 'Status') AS DBStatus

Where 'Emp' is the name of database.

Different SQL Server Database States:

ONLINE State: In online state you can easily access your database. You can also take your database offline using either T-SQL command or SQL Server Management Studio.

Take a database offline using T-SQL command
ALTER DATABASE Emp (database name) SET OFFLINE

Take a database offline using SQL Server Management Studio
In SQL Server Management Studio simply right click the database-> Tasks-> Take Offline.

OFFLINE State: In offline state you can not access your database. You sometime need to take your database offline if you want to move a file to new drive. After completing task database can be brought back online using the ALTER command or SQL Server Management Studio.

Take a database offline using T-SQL command
ALTER DATABASE Emp (database name) SET ONLINE

Take a database offline using SQL Server Management Studio
In SQL Server Management Studio simply right click the database-> Tasks-> Take Online.

Restoring State: In restoring state you can restore your data in offline mode. You can easily bring your database back from restoring state using below command:

RESTORE DATABSE Emp (database name) WITH RECOVERY
Go

RECOVERING State: In recovering state data is being recovered. Once data is recovered it will automatically become online.

RECOVERY PENDING State: In recovery pending state you can not start your database due to some resource related error. To start the database you first need to resolve the error.

SUSPECT State: Database can become suspect due to various reasons:
1.If database become corrupt.
2.Hardware failure, system shutdown.
3.Can not open database due to insufficient disk space.
In suspect mode you can not perform any action on your database. You can bring your database back by restoring it from backup.

EMERGENCY State: Emergency mode is used for troubleshooting purpose for example if your database becomes suspect then you can set it to the emergency state. In this case the database remains only in single mode.