Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Quick Tip: Taking a Database Offline

By Brian Knight,

An interesting database option which is hidden from Enterprise Manager in SQL Server 7.0 is offline. The offline option as it sounds, simply takes a database offline. Often DBAs take database offline instead of placing them in single user mode to see if anyone is using the database. The main reason you would use the offline option however is to transport the database. You can place the database offline, then copy the .MDF and .LDF files to the destination server and then attach them. This is used in place of the sp_detachdb system stored procedure.

When a database is offline, it can not be used by the server or users. Many DBAs place a database in single user mode for weeks before deleting it to make sure nobody needs it. This would be a better solution because it allows no access to the database. To place a database offline, use the following syntax:

sp_dboption 'northwind', 'offline', 'true'

You can also place a database back online by using the 'false' keyword. Once taken offline, users connecting to the database would receive the below error.

Server: Msg 942, Level 14, State 4, Line 1
Database 'Northwind' cannot be opened because it is offline.
In Enterprise Manager, users would see the below. When trying to view the database's properties, they would see nothing as well.

 
Total article views: 9523 | Views in the last 30 days: 12
 
Related Articles
FORUM

offline

offline

FORUM

indexing online vs offline, and setting a database offline

indexing online vs offline, and setting a database offline

FORUM
FORUM

SQL server (2000) logs doesn't show information related to taking database offline

SQL server (2000) logs doesn't show any information related to taking database offline.

FORUM

db offline

db offline

Tags
 
Contribute