Database States

MS SQL Server Database will be in any of the states.

• ONLINE: Database is available for access.
• OFFLINE: Database is unavailable.
• RESTORING: Database Restore is not completed, so The database is unavailable.
• RECOVERING: Database is being recovered. the database state will become online if the recovery succeeds.
• RECOVERY PENDING: recovery is failed due to missing resources, Additional action by the user is required to resolve the error and let the recovery process be completed.
• SUSPECT: database may be damaged. Additional is required to resolve the problem.
• EMERGENCY: This state prevent all the changes, It allows only readonly and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes.

To verify the current state of a database, following SQL Queries or system funtions will be useful:

SELECT name,state_desc FROM sys.databases

SELECT DATABASEPROPERTYEX ( 'Test' , 'status' )