Take a SQL Server Database Offline

This post shows how to take your database offline if there are no current users. Before you read this post, I strongly recommend you read my previous post Drop All Connections to SQL Server Database

Take a Database Offline using SQL Server Management Studio

Using SSMS, you can right click the database > Tasks > Take Offline

Take a Database Offline using T-SQL and wait for existing connections to close

ALTER DATABASE AdventureWorks SET OFFLINE

The command waits for existing connections to close and also does not accept any new connections. Use at discretion!

Take a Database Offline Immediately using T-SQL

ALTER DATABASE AdventureWorks
SET OFFLINE WITH ROLLBACK IMMEDIATE

Bring back the Database Online

ALTER DATABASE AdventureWorks
SET ONLINE

Note: I have seen users still using the sp_dboption to take a database offline. Note that sp_dboption feature will be removed in the next version of Microsoft SQL Server. It’s best to use ALTER DATABASE

Note: You may also want to read View Active connections for each Database in SQL Server

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook