ONLINE – Database is available for access. The primary fi­legroup is online, although the undo phase of recovery may not have been completed.

OFFLINE – Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.

RESTORING – One or more files of the primary filegroup are be­ing restored, or one or more secondary files are being restored offline. The database is unavailable.

RECOVERING – Database is being recovered. The recovering process is a transient state; the database will automatically be­come online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.

RECOVERY PENDING – SQL Server has encountered a re­source-related error during recovery. The database is not dam­aged, but files may be missing, or system resource limitations may be preventing it from starting. The database is unavailable.
Additional action by the user is required to resolve the error and let the recovery process be completed.

SUSPECT – the primary filegroup is suspect and may be dam­aged. The database cannot be recovered during the startup of the SQL Server. The database is unavailable. Additional action by the user is required to resolve the problem.

EMERGENCY – The user has changed the database and set the status to EMERGENCY. The database is in single-user mode and may be repaired or restored. The database is marked READ_ ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect can be set to the EMERGENCY state. This would permit the system administrator read-only access to the data­base. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

SIMPLE – No log backups
Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transac­tion log space.

FULL – Requires log backups. No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time.

BULK LOGGED – Requires log backups. An adjunct of the full recovery model that permits high-performance bulk copy oper­ations.
Reduces log space usage by using minimal logging for most bulk operations.