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

Source: Internet
Author: User
Tags how to use sql

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.

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.