Set Database State

Example

ALTER DATABASE [MyDB01] SET OFFLINE
ALTER DATABASE [MyDB01] SET EMERGENCY
ALTER DATABASE [MyDB01] SET ONLINE

The set state command should be a fast process. If it takes a while, stop the process, restart server and SQL server, and then try again.

<db_state_option>

Controls the state of the database.

OFFLINE
The database is closed, shut down cleanly, and marked offline. The database can’t be modified while it’s offline.

ONLINE
The database is open and available for use.

EMERGENCY
The database is marked READ_ONLY, logging is disabled, and access is limited to members of the sysadmin fixed server role. EMERGENCY is primarily used for troubleshooting purposes. For example, a database marked as suspect because of a corrupted log file can be set to the EMERGENCY state. This setting could enable the system administrator read-only access to the database. Only members of the sysadmin fixed server role can set a database to the EMERGENCY state.

You will not be able to perform database backups in EMERGENCY state.

Requires the ALTER DATABASE permission for the subject database, to change a database to the offline or emergency state, and the server level ALTER ANY DATABASE permission to move a database from offline to online.

You can determine this option’s status by examining the state and state_desc columns in the sys.databases catalog view. You can also determine the status by examining the Status property of the DATABASEPROPERTYEX function. For more information, see Database States.

A database marked as RESTORING can’t be set to OFFLINE, ONLINE, or EMERGENCY. A database may be in the RESTORING state during an active restore operation or when a restore operation of a database or log file fails because of a corrupted backup file.