SQL Server 2005 & 2008: Find Database States !!

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 DATABASEPROPERTYEX(‘AdventureWorks’,‘STATUS’)
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.

Reference:MSDN