First we should know that the database is always in a specific state, the following first to understand the database of the common three states:
1, offline: we can see the database in Microsoft SQL Server management, but there is an "offline" word next to the database name, stating that the database now exists in the instance of the database engine, but it is not possible to perform any valid data operations, such as new, modified , delete, etc., this is the offline state.
2, Online: The state of the database is normal state, that is, we often see the state of the database, the state of the database is in an operational state, the database can be any permissions within the operation.
3, suspicious: As with the "offline" state, we can see the database in Microsoft SQL Server management, but there is a "suspicious" word next to the database name, indicating that at least the primary filegroup is suspect or possibly corrupted.
How to view database status with SQL commands：
The sys.databases table can be used. The state of the database is marked in the STATE_DESC column of the table, and the SQL statement is as follows:
Select Name,state_desc from sys.databases
Visualize setting Database state：
Set to offline: Open the database in Microsoft SQL SERVER Management, right-click on the database, select Tasks-> Offline in the right-click menu
Set to Online: if the current state of the database is offline, you can open the database in Microsoft SQL SERVER Management, right-click on the database, and select Tasks-> online from the right-click menu
Suspicious: The state of the database automatically tests the status of the data file.
setting database state with SQL commands：
The SQL statements that are set to take offline are as follows:
EXEC sp_dboption ' database name ', ' Offline ', ' TRUE '
Or
ALTER database name
SET OFFLINE
The SQL statement that is set to online is as follows:
ALTER database name
SET ONLINE
Description
• While offline and online SQL Server databases are two very simple concepts, it can reflect in subtle detail how well our developers understand the fundamentals of the database, so we want you to have a clear idea of the state of the database and the SQL commands that change the state of the database.
• If we prompt the file to be used when copying the source file of the database, we can first set up the database for offline replication.
SQL Server database status (offline, online, suspicious) and SQL settings statement
