Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

VIII.2.1. Controlling Database State

Before we get started on how to take a database offline and then bring it back online, a short detour is in order to get a good understanding of the many different states that your SQL Server 2008 database can be in. These states include:

• Online: You hope to see this state most frequently for your database. It means that the database is ready for action.

• Off-line: This generally refers to a database that has been deliberately taken off-line, usually for actions, such as moving it to a new disk or new server.

• Recovering: SQL Server is attempting to recover the database, usually after a restore or system restart.

• Recovery pending: This state generally happens when SQL Server is missing a resource or other key component during the database recovery process. When you see this state, it's likely that an administrator will have to take some further action to help things work right.

• Suspect: You don't want to see this state. It means that something has gone wrong with your database (either during startup or recovery), and SQL Server considers your database (or one of its underlying data files) to be damaged.

• Emergency: As you might expect, this database state means that some serious administrative work needs to be done before the database is ready for general consumption. Typically set when an administrator is trying to figure out why a database was placed into the suspect state, the database is only in single-user mode until the problem is resolved.