SQL Server Database status options

Option 1,

Single_user (single user), Multi_user (multi-user), Restricted_user (limited user);

Describes the user access properties of the database, which are mutually exclusive, and setting any of these options cancels the setting of the other options

The Single_user mode database can have only one connection at a time.

Restricted_user mode can have multiple connections, provided that these users are considered ' qualified ' as Sysadmin,dbcreator,dbowner.

Multi_user Any valid user can connect.

Example:

ALTER DATABASE Studio
Set Single_user
With
Rollback immediate; ----       | Rollback after 1000|no_wait;
Go

Note: This time only one connection can operate the database, if the other connection wants to manipulate the database, it will fail, not because of its permissions problem, but can only have a connection.

Option 2,

Online, offline, emergency they are mutually exclusive

If the data is set to offline, it is turned off and turned off completely, and is marked as offline and the database cannot be modified when it is offline, if the database

Any connection in the offline can not be marked as a.

Example:

Select Name, State_desc
From sys.databases
where name = ' Studio '

with rollback immediate; ----       | Rollback after 1000|no_wait;
Go

Attention:

For the state of the database we can set is only offline, online,emergency! It's some other state we're not going to be able to set it up

such as: Restore from the backup process will correspond to recovering, if for some reasons there is no way to complete the restoration will correspond to recovery_pending;

such as: During the restore process detected data corruption will correspond to suspect, the database will become completely unavailable, in many cases the database can be set to emergency

So that it can be read-only, if one or more log files of the database are lost, when the database is copied to a new location, the emergency mode also

Access to the database is also possible. When you convert from recovery_pending to emergency, SQL Server shuts down the database and then uses a special tag to re-

Boot, which skips the recovery process, skipping recovery means there may be logical errors or physical inconsistencies (such as index loss, broken pages)

Option 3,

READ_ONLY, Read_write

-----------------------

ALTER DATABASE Studio
Set Read_Only
With
Rollback immediate; ----       | Rollback after 1000|no_wait;
Go

--------------------------------------------------------------------------------------------------------------- -------------------------------------

Status options are available and

Rollback IMMEDIATE | Rollback after 1000 | no_wait;

With

SQL Server Database status options