Placing a Database Online or Offline

Placing a database online makes it ready to be used. All devices belonging to the specified database are opened (if they are not already open) and marked nondeferred, and the database is recovered and becomes available for use. Recovery is skipped if the database is set as read only.

Placing a database offline sets the database as unavailable and allows the storage media (CD or other) to be removed from the drive. All devices belonging to that database are closed and marked as deferred, except those that contain space belonging to other online databases. Databases placed offline are not recovered automatically at server startup.

Before a removable media disk is removed from a drive, all databases on that disk must be placed offline.

Note If you remove the storage media from the drive without first placing the database offline, you can later receive operating system error messages.

Use the sp_dboption stored procedure to place a database online or offline.

    To place an installed database online
  1. If necessary, place the removable media into the drive.

    When a database is placed online, any needed removable disk must be in the disk drive. A disk is needed if it contains a data device that was not moved to the local hard disk during installation.

  2. Run sp_dboption using the offline, false option.

    For example, to place the database inventory online, from the Query Analyzer in SQL Enterprise Manager, use: 

    sp_dboption inventory,offline,FALSE
    To place a database offline
  1. Make sure the database has no active users.

    View active users by opening the Current Activity window of SQL Enterprise Manager.

  2. Run sp_dboption using the offline, true option.

    For example, to place the database inventory offline, from the Query Analyzer in SQL Enterprise Manager, use: 

    sp_dboption inventory,offline,true
    To remove media that contains an installed database
  1. Place the database offline using sp_dboption
  2. If the removable media contains more than one database, repeat step 1 for each.
  3. Remove the storage media from the drive.

For more information about using sp_dboption, see the sp_dboption System Stored Procedure topic in the Microsoft SQL Server Transact-SQL Reference.