A SQL Server Database is can only be in one specific state at a given time. There are seven different States of SQL Server Database are:-
RECOVERY PENDING: A database will be in this state if SQL Server encounters a resource-related error during recovery. The database will be unavailable until the database administrator resolves the resource error and allows the recovery process to be completed.
SUSPECT :One or more database files have been marked as suspect because of a data access or Read error. This may occur if a TORN PAGE has been detected during database Read operations. If a database has been marked as SUSPECT, the database is unavailable until the error has been resolved.
EMERGENCY: The database will be in this state when the database administrator has set the status to EMERGENCY. In this state, the database is in single-user mode and may be repaired or restored. If the database has been marked as SUSPECT, this is the first step in correcting the problem, short of adatabase restore. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state
ONLINE : The database is online and available. This will show up as the NORMAL state.
OFFLINE :The database is unavailable. Databases are set offline by executing the command ALTER DATABASE <DBName> SET OFFLINE. This can be done if the database administrator wants to move a database file from one location to another. In this case, the database would be set OFFLINE, then the ALTER DATABASE <DBName> MODIFY FILE command would be executed, followed by changing the database back to ONLINE.
RESTORING : One or more files are being restored. The database is unavailable.
RECOVERING:The database is being recovered. Except in the case of database mirroring, this is a transient state that occurs during the automatic or manual recovery process. The database is unavailable.
OFFLINE :The database is unavailable. Databases are set offline by executing the command ALTER DATABASE <DBName> SET OFFLINE. This can be done if the database administrator wants to move a database file from one location to another. In this case, the database would be set OFFLINE, then the ALTER DATABASE <DBName> MODIFY FILE command would be executed, followed by changing the database back to ONLINE.
RESTORING : One or more files are being restored. The database is unavailable.
RECOVERING:The database is being recovered. Except in the case of database mirroring, this is a transient state that occurs during the automatic or manual recovery process. The database is unavailable.
RECOVERY PENDING: A database will be in this state if SQL Server encounters a resource-related error during recovery. The database will be unavailable until the database administrator resolves the resource error and allows the recovery process to be completed.
SUSPECT :One or more database files have been marked as suspect because of a data access or Read error. This may occur if a TORN PAGE has been detected during database Read operations. If a database has been marked as SUSPECT, the database is unavailable until the error has been resolved.
EMERGENCY: The database will be in this state when the database administrator has set the status to EMERGENCY. In this state, the database is in single-user mode and may be repaired or restored. If the database has been marked as SUSPECT, this is the first step in correcting the problem, short of adatabase restore. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state
How to Identify Current State of SQL Server Database?
Use master
GO
SELECT
@@SERVERNAME AS [Server Name]
,NAME AS [Database Name]
,DATABASEPROPERTYEX(NAME, 'Recovery') AS [Recovery Model]
,DATABASEPROPERTYEX(NAME, 'Status') AS [Database Status]
FROM dbo.sysdatabases
ORDER BY NAME ASC
GO
GO
SELECT
@@SERVERNAME AS [Server Name]
,NAME AS [Database Name]
,DATABASEPROPERTYEX(NAME, 'Recovery') AS [Recovery Model]
,DATABASEPROPERTYEX(NAME, 'Status') AS [Database Status]
FROM dbo.sysdatabases
ORDER BY NAME ASC
GO
