November 12, 2010

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

image

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, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

No comments: