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.
Controls the state of the database.
The database is closed, shut down cleanly, and marked offline. The database can’t be modified while it’s offline.
The database is open and available for use.
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.
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_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.