SQL SERVER – 2005 – Find Database Status Using sys.databases or DATABASEPROPERTYEX

http://blog.sqlauthority.com/2007/08/31/sql-server-2005-find-database-status-using-sysdatabases-or-databasepropertyex/

While writing article about database collation, I came across sys.databases and DATABASEPROPERTYEX. It was very interesting to me that this two can tell user so much about database properties.

Following are main database status: (Reference: BOL Database Status)

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.

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.

Let us see how we can find out database status using this sys.databases and DATABASEPROPERTYEX.

1) Using T-SQL (My Recommendation)

Example:
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
SELECT state_desc DatabaseStatus_sysDatabase
FROM sys.databases
WHERE name = 'AdventureWorks'
GO

ResultSet:
DatabaseStatus_DATABASEPROPERTYEX
——————————————————
ONLINE

DatabaseStatus_sysDatabase
——————————————————
ONLINE

2) Using SQL Server Management Studio