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):
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.
Executing an ALTER DATABASE command with state option ONLINE will again take the SQL Server database online back.
ALTER DATABASE [HRSkills] SET ONLINE