SQL Server database status (offline, online, suspicious) and SQL setting statements

First, we should know that the database is always in a specific State. Next, let's take a look at three common statuses of the database: 1. Offline: we can see the database in Microsoft SQL Server Management, however, the database name is suffixed with "offline", indicating that the database currently exists in the Database Engine instance, but it cannot perform any valid data operations, such as adding or modifying the database name, delete. This is the offline status.

2. Online: the database is in the normal state, that is, the database status we often see. The database in this state is in the operational state and can perform any operation within the database.

3. Suspicious: the database is in the same status as "offline". You can view the database in Microsoft SQL Server Management, but the database name is marked with "suspicious, this indicates that at least the primary file group is suspicious or may be damaged.

How to use SQL commands to view the database status: You can use the SYS. Databases table to mark the database status in the state_desc column of the table. The SQL statement is as follows:

Select name, state_desc from SYS. Databases

Visual setting of database status: Set to offline: Open the database in Microsoft SQL Server Management, right-click the database, and choose "task"> "offline" from the context menu"

Set to online: if the current status of the database is offline, you can open the database in Microsoft SQL Server Management and right-click the database, right-click the task and choose "task"> "online"

Suspicious: the status of the data file automatically tested by the database.

Use SQL commands to set the database status: the SQL statements that are set to offline are as follows:

Exec sp_dboption 'database name', 'offline', 'true'

Or

Alter database name set offline

The SQL statement set to online is as follows:

Alter database name Set Online

Note: Although taking the SQL Server database offline and online is two simple concepts, it can reflect the knowledge of our developers on the basic knowledge of the database in a subtle way, therefore, we hope that you can clearly understand the database status and SQL commands for changing the database status.

· If we prompt that the file is in use when copying the source file of the database, we can set the database to offline before copying.