I'm no SQL guy at all, however many of the products I work with require SQL backend databases.  (e.g. Citrix XenApp datastore & VMware vCenter databases).  Many times I find myself needing to take a database offline to make changes like moving the database to a new server for instance.  I know I'm not the only IT guy that has right clicked on the SQL database and tried to take it offline only to get an error.  It's always because there are active connections to the DB.

Everytime I need to take a SQL database offline I have to go research how to do it.  So, I wanted to post a little article on it here, so I myself won't forget and for anybody else that may need to do such.  I've found there are a few different ways to do this but the easiest I've found is with the following SQL script.

Just create a sql query and put the following line in:

ALTER DATABASE YOURDATABASENAMEHERE SET SINGLE_USER WITH ROLLBACK IMMEDIATE

What the line does is kill all open connects to the DB and takes the database offline.  When you're done with your maintenance here's what you do to bring it back online:

ALTER DATABASE YOURDATABASENAMEHERE SET MULTI_USER

 

Easy enough, right?