SQL Server Database States

1.       When database is started at server startup or when created or attached the state of the database is temporarily changed to RECOVERING.  Here two operations are occurred.

ü  If there is an issue with resource such as database files or system resource limitations which may be preventing it from starting, the state changes to RECOVERY_PENDING and this state is continued. As such when you have identified and resolved the underlying issue; user must have to use ALTER DATABASE <db_name> SET ONLINE to make the database functional.

ü  If there are no issues with resources, the engine tries to run recovery on the database and if successful the database state changes to ONLINE and continued but if recovery fails for the database state is changed to SUSPECT but not continued (from SQL Server 2005 onwards). Please note; SUSPECT state is temporary so if you restart the server recovery runs again on the database so you have the option to have a look into the errorlog to find out the underlying reason then may troubleshoot appropriately.

2.       When the database is ONLINE, you can use ALTER DATABASE <db_name> SET OFFLINE to OFFLINE the database which is again continued. You are free to use ALTER DATABASE <db_name> SET EMERGENCY on any database states that are continued (please consider your environment) and to add here EMERGENCY state of the database is also persisted (SQL Server 2005 onwards).

3.       Now, what happens when you restore any database from a backup? Well, in such scenario initially database state is changed to RESTORING and when all pages are restored it moves to RECOVERING followed by normal recovery process and same as normal startup (please refer to the diagram above).