Determining the Next Step for Recovery of a File or Filegroup
The sys.database_files and sys.master_files catalog views contain information that determines both the state of a file or filegroup and the next step necessary to bring it online. The state of a filegroup is determined by the files it contains: unless all files are online, the entire filegroup is offline.
The key property of a file is its state, as given by the state and state_desc columns of sys.database_files. The table below describes how to interpret and respond to each state.
| File state | Meaning | Actions |
|---|---|---|
ONLINE |
The file is online. |
No action is necessary. |
RESTORING |
The file is being restored. |
For information about other significant columns for this state, see Planning a Piecemeal Restore Sequence for a File in the Restoring, Recovery-Pending, or Offline State. |
RECOVERY_PENDING |
Recovery of the file has been postponed. The file was online or did not exist before being changed to RECOVERY_PENDING by a partial restore. |
|
OFFLINE |
The file was taken offline explicitly using ALTER DATABASE. |
|
RECOVERING |
This is a transient state. If recovery is successful, the file comes online automatically. |
No action is necessary. |
SUSPECT |
The file is damaged. |
The file can be restored or dropped, or the database can be set to EMERGENCY mode. |
DEFUNCT |
The file has been dropped (when it was offline), but not yet cleaned up. |
None. Recovery is not possible. |
See Also
Concepts
Planning and Performing Restore Sequences (Full Recovery Model)
Planning a Piecemeal Restore Sequence for a File in the Restoring, Recovery-Pending, or Offline State
Other Resources
Working with Restore Sequences for SQL Server Databases