USE master
GO

-- Check who is still connected to the database.
sp_who2

ALTER DATABASE MyDB SET OFFLINE

-- Force rollback off current user.
ALTER DATABASE MyDB SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE MyDB SET ONLINE

Note:

  • Bring database OFFLINE / ONLINE should be a fast process, even for a large database.
  • Users must be disconnected from database before setting the database to offline. Else, use ROLLBACK IMMEDIATE clause.
  • If you run into problem with bring the database back ONLINE, try restarting SQL Server service.
  • If part of Availability Group, need to remove database from group first.
Last modified: September 21, 2020

Author

Comments

Write a Reply or Comment