SQL Server Database Primary state

A SQL Server database is in many different states, such as ONLINE, Offline,restoring, RECOVERING, Recovery_pending, SUSPECT, and EMERGENCY.

Only in the online state can the database be accessed normally.

You can use the following statement to view its status

Select  from sys.databases

1.ONLINE

The database can be accessed normally only in the online state

2.OFFLINE

We can see the database in Microsoft SQL Server management, but there is an "offline" next to the database name, which means that the database now exists in the instance of the database engine, but cannot perform any valid data operations, such as new, modified, deleted, etc. This is the offline state. (This time, you can directly copy the source files, will say the most original backup method)

But:

Unable to overwrite file ' F:\TEST\SHOP. MDF '. Database ' Shop ' is using this file.

You still can't ....

3.RESTORING

Due to the role of SQL Server LAZY write and checkpoint, there are some uncommitted data on the hard disk, if the database is closed at this time, the next time SQL Server re-open the database, in order to maintain database consistency, SQL Server will roll back , in order to ensure the consistency of database transactions (Undo/rollback), for those already committed transactions, temporarily in memory, not written to disk, all redo, the process of undo and redo is called database recovery. Only a restored database can be guaranteed to be a "consistent" database for secure access.

4.recovery_pending

If the database does not open all the database files properly when it is being restored, the database will enter the recovery pending state. In this state, the administrator has two options,

A. To make SQL Server go live without the alter online command,

B. Or you can only discard the current database and restore the backup.

5.SUSPECT

We can see the database in Microsoft SQL Server management, but there is a "suspicious" word next to the database name, which means at least the primary filegroup is suspect or may be corrupt

The solution for questioning: http://www.jb51.net/article/23363.htm

6.EMERGENCY

The database is marked as READ_ONLY, logging is disabled, and access is restricted to members of the sysadmin fixed server role.EMERGENCY is primarily used for troubleshooting. For example, a database that is flagged as suspect because of corrupted log files can be set to the EMERGENCY state. This allows the system administrator to make read-only access to the database. Only members of the sysadmin fixed server role can set the database to the EMERGENCY state.

T-SQL statements to toggle their state

ALTER DATABASE SET | | EMERGENCY

SQL Server Database Primary state