Interview Question of the Week #016 – How to Take Database Offline

At a database conference, someone asked me what it means to take a database offline. The command is short; its effect on the people using that database is the part to explain carefully.

Question: What does it mean to take a SQL Server database offline, and how do you bring it back?

Answer: An offline database remains registered with the SQL Server instance, but normal reads and writes to it are unavailable. In a planned maintenance window, connect to master, confirm the database name, and decide how to handle existing connections. This is the original command, with a placeholder database name:

USE master;
GO
ALTER DATABASE [myDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

ROLLBACK IMMEDIATE disconnects other sessions and rolls back their incomplete transactions. Do not run it casually against a database people are using. Without a termination clause, the change can wait for sessions to release the database.

After the maintenance, bring the same database online:

ALTER DATABASE [myDB] SET ONLINE;
GO
SELECT name, state_desc
FROM sys.databases
WHERE name = N'myDB';

Confirm that the database reports ONLINE and test a normal query. The files must still be present and accessible. An offline database differs from a detached one: detaching removes the database from the instance, while taking it offline leaves its registration in place. Jobs and applications that use it will fail while it is offline, so arrange the outage and backup before the change.

For related examples, see Take Offline or Detach Database and the offline and online T-SQL script.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.