States of SQL Server Database:
Online: When a database is in an online state, the database is available for access.
OFFLINE: When a database is in OFFLINE state, then the database is not accessible for user connections. One can set the database to this state if you don’t want users to connect to the database.
RESTORING: When a database is in RESTORING state, then it means one or more files of the primary filegroup have been restored or one or more secondary files are being restored offline.
RECOVERING: When a database is in a recovering state, it means its in the process of recovery, and it will become automatically online for user connectivity.
SUSPECT: When a database is in SUSPECT state, it means the database is unavailable for user connection. The database may be damaged, or at least the primary filegroup is supect.
EMERGENCY: When a database is in EMERGENCY state, it means a user has changed the status of the database to EMERGENCY. In emergency mode, the database will remain in SINGLE_USER mode, and the database can be repaired or restored. Database will remain READ_ONLY.
Below is the ALTER DATABASE syntax that is related to setting database options in SQL Server: ALTER DATABASE DBNAME SET {Options}
How to know database in which state:
SELECT * FROM sys.databases
SELECT DB_NAME() AS DatabaseName, DATABASEPROPERTYEX(‘master’, ‘Status’) AS DBStatu
How to find database ID:
select DB_ID(‘MASTER’);