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:
The following can be the different Database States:
- ONLINE: Database is available for access.
- OFFLINE: Database is unavailable.
- 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.
- RECOVERING: Database is being recovered.
- RECOVERY PENDING: SQL Server has encountered a resource-related error during recovery.
- SUSPECT: At least the primary filegroup is suspect and may be damaged.
- EMERGENCY: User has changed the database and set the status to EMERGENCY.