How to Take SQL Server Database Offline

UPDATE 12-16-2011: If you are using ASP.NET and your website / database is live, and you want to take the database offline, you're better off putting an App_Offline.htm file in your website main directory to first take the website down for maintance (I believe this should also close any active connections to the database).  You should then be able to take your SQL Server database offline by simply using SQL Server Management Studio, rather than the code below. (END)

I ran into a problem earlier today, where I wasn't able to take a SQL Server database offline.  When I tried to take the database offline I would get the following error:

ALTER DATABASE failed because a lock could not be placed on database

To get around this error and bring the database offline, I used the following SQL query to bring it offline (just change "YourDatabaseName" with your database name):

USE master 
GO
 
ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE 
GO

You can then bring the database back online, using:

USE master
GO

ALTER DATABASE YourDatabaseName SET MULTI_USER
GO

ALTER DATABASE YourDatabaseName SET ONLINE WITH ROLLBACK IMMEDIATE
GO

NOTE: Make sure that you reset your database back to SET MULTI_USER in a production environment (in other words, don't leave the database in SINGLE_USER mode.  Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions. If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

You can also change or check the database user Mode using SQL Server Management Studio, here's how:

To set a database to single-user mode or multi-user mode in Management Studio:

1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
2. Right-click the database to change, and then click Properties.
3. In the Database Properties dialog box, click the Options page.
4. From the Restrict Access option, select SINGLE_USER (or MULTI_USER) 
5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

Keep in mind that if you try the SET OFFLINE sql script above and still have issues geting your database offline, you may just want to do a quick "Stop" and "Start" (or "Restart") of your SQL Server to drop connections to the database so that you can take it offline.  To Stop and Start SQL Server, you will need to go into Computer Management -> Services, here's how:

1. In Windows 7 (or Windows Server), click Start -> then in the search box type: "Computer Management"
2. Open Computer Management -> then in the sidebar expand "Services and Applications" -> then click on "Services"
3. In the Services window, scroll down until you find "SQL Server (MSSQLSERVER)" and select it.  If you using SQL Server Express you will select "SQL Server (SQL EXPRESS)"
4. Now click "Stop the service" or "Restart the service".   If you click Stop the service, you will manually have to click "Start the service" after it has stopped to get SQL Server up and running.
5. Once you've stopped or restarted SQL Server, all the previous connections and/ or locks should be gone so you can then Take the database offline.
6. That's it.