, ,

We can easily find the database states in SQL Server 2005 and above by using sys.databases and DATABASEPROPERTYEX. To know about the current database states..we can use the following queries:


SELECT state_desc,name
FROM sys.databases
WHERE name=‘AdventureWorks’

The following can be the different Database States:

  1. ONLINE: Database is available for access.
  2. OFFLINE: Database is unavailable.
  3. RESTORING: One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.
  4. RECOVERING: Database is being recovered.
  5. RECOVERY PENDING: SQL Server has encountered a resource-related error during recovery.
  6. SUSPECT: At least the primary filegroup is suspect and may be damaged.
  7. EMERGENCY: User has changed the database and set the status to EMERGENCY.



Subhro Saha