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.