Sunday, June 17, 2012

SQL Server Database SET OFFLINE and ONLINE

Sometimes database needed to be taken offline for a few reason, e.g. removing all access to the database, or changing the physical file name. The database could then be set online when it is ready.

When setting the database OFFLINE, the database is shut down, and can not be modified during this state. Since the database is unavailable, request to database returns error. When the database is set ONLINE, the database is open and available to be use.

To take the database base OFFLINE,

Using SQL Server Management Studio (SSMS),
Right click the database > Tasks > Take Offline

Using T-SQL,

ALTER DATABASE YourDatabase
SET OFFLINE
WITH ROLLBACK IMMEDIATE;

The rollback immediate is an option if you wish to immediately rollback all active transactions before taking the database offline.

To bring the database ONLINE,

Using SSMS,
Right click on the database > Tasks > Bring Online

Using T-SQL,

USE master;

ALTER DATABASE YourDatabase
SET ONLINE;

To view the database current state.

Using SSMS,
Right click on the database > Properties > Status. (When the database is offline, there is a red color down arrow overlay as indication. You may also right click the database and click refresh to update its current status)

Using T-SQL,

USE master;

SELECT name, state, state_desc
FROM sys.databases
WHERE name = 'YourDatabase';

There is also another option DETACH and ATTACH. Here is the blog about if you should use OFFLINE or DETACH.

Google+