SQL Server: How to Set a Database Offline/Online

Setting a Database Offline/Online in SQL Server

There are three options to set a database offline or bring it back online.

Option 1: Using ALTER DATABASE

Set the database offline:

ALTER DATABASE TestDB1 SET OFFLINE;

Bring the database online:

ALTER DATABASE TestDB1 SET ONLINE;

Option 2: Using sp_dboption

`sp_dboption` is an older SQL Server approach for changing database options.
 

Set the database offline:

sp_dboption 'TestDB1', 'offline', true;

Bring the database online:

sp_dboption 'TestDB1', 'offline', false;

Option 3: Using SQL Server Management Studio (SSMS)

• Open `Object Explorer`
• Right-click the database.
• Select `Tasks`
• Select `Take Offline`.

The database can now be taken offline or brought back online depending on the operation you perform.

Note: Taking a database offline makes it unavailable to users until it is brought back online. The `sp_dboption` method is mainly of historical interest; for current SQL Server versions, `ALTER DATABASE` or SSMS is the preferred approach.