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 Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




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.

ALTER DATABASE [HRSkills] SET OFFLINE
ALTER DATABASE [HRSkills] SET 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.

ALTER DATABASE [HRSkills] SET OFFLINE

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.

ALTER DATABASE [HRSkills] SET ONLINE





Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems