Dołącz do nas!

Wszystkie znaki interpunkcyjne za wyjątkiem kropek, łączników, znaków podkreśleń i spacji są niedozwolone.
Adres e-mail, koniecznie prawidłowy. Będą nań przesyłane wszystkie wiadomości e-mail od systemu. Adres nie jest udostępniany publicznie i będzie wykorzystywany jedynie w wypadku prośby o przesłanie nowego hasła lub do przesyłania informacji o nowościach czy innych komunikatów.
CAPTCHA
To pytanie ma na celu ochronę przed automatycznym spamowaniem oraz ustalenie, czy odwiedzający stronę jest człowiekiem.
Image CAPTCHA
Wpisz kod widoczny powyżej.

Logowanie

Nazwa użytkownika witryny Project Envision.
Hasło powiązane z nazwą użytkownika.
Prześlij nowe hasło

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;