Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 9504 | Views in the last 30 days: 11
 
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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones