Recovering a Database Without Restoring Data
.gif "Icon showing a blue database disk")
This topic is relevant only for SQL Server databases that have more than one filegroup.
Usually, data is restored before it is recovered. However, you can recover the database without restoring a backup; for example, for a read-only files that are consistent with the database. This is referred to as a recovery-only restore. A recovery-only restore is for those cases where the data is already consistent with the database and needs only to be made available. The goal is to complete the recovery and bring the affected data online.
A recovery-only restore operation brings the data in the offline filegroup online; no data-copy, redo, or undo phase occurs. For information about the phases of restore, see Understanding How Restore and Recovery of Backups Work in SQL Server.
A recovery-only restore can occur for a file, filegroup, or the whole database, as follows:
- A recovery-only file restore can be useful in the following situation:
A database is restored piecemeal. After restore of the primary filegroup is complete, one or more of the unrestored files are consistent with the new database state, perhaps because it has been read-only for some time. These files only have to be recovered; data copying is unnecessary.
The RESTORE syntax for a recovery-only file restore is:
RESTORE DATABASE database_name { FILE **=**logical_file_name | FILEGROUP **=logical_filegroup_name }[ ,...n ] WITH RECOVERY - A recovery-only database restore can be useful in the following situations:
- You did not recover the database when restoring the last backup in a restore sequence, and but you now want to recover the database to bring it online.
- The database is in standby mode, and you want to make the database updatable without applying another log backup.
The RESTORE syntax for a recovery-only database restore is as follows:
RESTORE DATABASE database_name WITH RECOVERY
Note
The FROM = <backup_device> clause is not used for recovery-only restores because no backup is necessary.
To recover a database without restoring data
Examples
The following example illustrates a recovery-only file restore of the files in a secondary filegroup, SalesGroup2, in the Sales database. The primary filegroup has already been restored as the initial step of a piecemeal restore, and SalesGroup2 is consistent with the restored primary filegroup. Recovering this filegroup and bringing it online requires only a single statement.
RESTORE DATABASE Sales FILEGROUP=SalesGroup2 WITH RECOVERY;
Completing a Piecemeal Restore Scenario with a Recovery-Only Restore
Simple recovery model
- Example: Piecemeal Restore of Database (Simple Recovery Model)
- Example: Piecemeal Restore of Only Some Filegroups (Simple Recovery Model)
Full recovery model
- Example: Piecemeal Restore of Database (Full Recovery Model)
- Example: Piecemeal Restore of Only Some Filegroups (Full Recovery Model)
See Also
Concepts
Understanding How Restore and Recovery of Backups Work in SQL Server
Other Resources
RESTORE (Transact-SQL)
Restoring a Database to a Point Within a Backup
Working with Restore Sequences for SQL Server Databases