SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.

download SQL Server 2017
download SQL Server 2016
download SQL Server 2014

Offline Databases and Take SQL Database Offline / Online

On MS SQL Server instances database administrators or developers can set sql databases offline or online by a number of methods.

How to Offline Database for MS SQL Server

A MS SQL Server administrator or a sql developer that has the required permissions can take database offline or take database online using a few methods including executing database t-sql scripts and by using the Management Studio GUI (graphical user interface).
Below I'll list the methods you can use for setting a sql database offline and take database online back.

Executing ALTER DATABASE command for setting database option to OFFLINE or ONLINE.
Executing sp_dboption system stored procedure for setting the offline property to true for OFFLINE database or setting to false for ONLINE database status.
Take SQL Database Offline using Microsoft SQL Server Management Studio Object Explorer.

Execute ALTER DATABASE Command to Set Database Option Offline or Online

SQL Server Database Administrators (DBAs) can take sql database offline or databae online by using the ALTER DATABASE command for MS SQL Server 2005 or for MSSQL Server 2008 databases.

As you will see below the first sql statement will set database offline on the other hand the following t-sql script will set database online.


Execute sp_dboption System Stored Procedure to Set Database Option offline

SQL Server Database Administrators (DBAs) can use the sp_dboption system stored procedure to set the offline database option to offline / online by setting the @optname parameter to offline and @optvalue parameter to true or false.
System procedure sp_dboption takes one more parameter as input @dbname which indicates the database name that you want to set or display its properties.

sp_dboption @dbname=N'HRSkills',@optname=N'offline',@optvalue=N'true'
sp_dboption @dbname=N'HRSkills',@optname=N'offline',@optvalue=N'false'

The first t-sql statement above set database offline on the other hand the second statement set sql database online.

Take SQL Database Offline using Microsoft SQL Server Management Studio Object Explorer

You can take a database offline for MS SQL Server databases using SQL Server Management Studio (SSMS) Object Explorer visually.
To set database offline on Object Explorer you can select the Tasks > Take Offline menu items in order on the context menu on the SQL database you want to take database offline.

Here is a screenshot how a DBA (database administrator) can set MSSQL database offline (SQL Server 2005 or SQL Server 2008):

take database offline on MS SQL Server

While tracing the server for executed sql scripts on the MS SQL Server 2005 using Microsoft SQL Server Profiler, I see that the following t-sql statement is executed on master database on the SQL Server instance.


The HRSkills is the name of the database which I forced to offline status.
The ALTER DATABASE ... SET OFFLINE|ONLINE t-sql statement controls the sql database is offline or online.
After setting the SQL database offline, the icon displaying the status of the database changes as seen in the below screenshot from the object explorer.

database offline status icon

Executing an ALTER DATABASE command with state option ONLINE will again take the SQL Server database online back.


Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums

Copyright © 2004 - 2018 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems