Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

How to take database offline in Sql Server

Sometimes we want to disable user access to database without deleting or detaching that database. The easiest way to do it is to take database offline. Taking database offline can be performed using Transact-Sql or Sql Server Management Studio.

To take database offline in Sql Server Management Studio, right click the desired database, than choose Tasks, and select Take Offline.

Sql Server Take Database OfflineSql Server Take Database Offline

To bring database back online and allow users to access it, right click the desired database, than choose Tasks, and select Bring Online.

Sql Server Bring Database OnlineSql Server Bring Database Online

To take database offline using Transact Sql, we should use ALTER DATABASE STATEMENT.

ALTER DATABASE alerts SET OFFLINE;

The above command will try to make database offline immediately, however if there are running transactions, it will wait till all transactions are completed. Such behavior will prevent any unexpected rollbacks.

If you do not want ALTER DATABASE dbname SET OFFLINE statement to wait till all users and processes will disconnect, you can add WITH NO_WAIT clause. This will force ALTER DATABASE command to fail if it cannot take database offline immediately.

ALTER DATABASE alerts SET OFFLINE WITH NO_WAIT;

If other users were using the database you will see following error messages:

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'alerts'
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

Sometimes you do not want to wait till all users will disconnect from database, because either you have to take database offline quickly or you do not have an option to ask all users to disconnect in reasonable amount of time. If you want to force active users and processes to disconnect and rollback their active transactions, you can use ROLLBACK clause. ROLLBACK clause comes in two favors, one will allow to rollback transactions immediately, the second will wait for specified amount of time before rolling back.

ALTER DATABASE alerts SET OFFLINE ROLLBACK IMMEDIATELY; 

After above command finishes its execution you should see following messages:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

If you want to wait for specified amount of time before rollback will occur you can use the following instruction.

ALTER DATABASE alerts SET OFFLINE ROLLBACK AFTER 60 SECONDS;

To bring database online from Transact Sql we can use following statement:

ALTER DATABASE alerts ONLINE;