Overview of Restore and Recovery in SQL Server
SQL Server 2005 supports restoring data on the following levels:
The database (a complete database restore)
The whole database is restored and recovered, and the database is offline for the duration of the restore and recovery operations.The data file (a file restore)
A data file or a set of files is restored and recovered. During a file restore, the filegroups that contain the files are automatically offline for the duration of the restore. Any attempt to access an offline filegroup causes an error.Note
You cannot back up or restore individual tables.
The data page (a page restore)
Under the full recovery model or bulk-logged recovery model, you can restore individual databases. Page restores can be performed on any database, regardless of the number of filegroups.
Restore Scenarios
A restore scenario in SQL Server is the process of restoring data from one or backups and then recovering the database. The supported restore scenarios depend on the recovery model of the database and the edition of SQL Server 2005.
The following table introduces the possible restore scenarios that are supported for different recovery models.
| Restore scenario | Under simple recovery model | Under full/bulk-logged recovery models |
|---|---|---|
Complete database restore |
This is the basic restore strategy. A complete database restore might involve simply restoring and recovering a full database backup. Alternatively, a complete database restore might involve restoring a full database backup followed by restoring and recovering a differential backup. For more information, see Performing a Complete Database Restore (Simple Recovery Model). |
This is the basic restore strategy. A complete database restore involve restoring a full database backup and, optionally, a differential backup (if any), followed by restoring all subsequent log backups (in sequence). The complete database restore is finished by recovering the last log backup and also restoring it (RESTORE WITH RECOVERY). For more information, see Performing a Complete Database Restore (Full Recovery Model) |
File restore * |
Restore one or more damaged read-only files, without restoring the entire database. File restore is available only if the database has at least one read-only filegroup. |
Restores one or more files, without restoring the entire database. File restore can be performed while the database is offline or, for some editions of SQL Server 2005, while the database remains online. During a file restore, the filegroups that contain the files that are being restored are always offline. |
Page restore |
Not applicable |
Restores one or more damaged pages. Page restore can be performed while the database is offline or, for some editions of SQL Server 2005, while the database remains online. During a page restore, the pages that are being restored are always offline. An unbroken chain of log backups must be available, up to the current log file, and they must all be applied to bring the page up to date with the current log file. For more information, see Performing Page Restores. |
Piecemeal restore * |
Restore and recover the database in stages at the filegroup level, starting with the primary and all read/write, secondary filegroups. |
Restore and recover the database in stages at the filegroup level, starting with the primary filegroup. |
* Online restore is supported only in SQL Server 2005 Enterprise Edition.
Regardless of how data is restored, before a database can be recovered, the SQL Server Database Engine guarantees that the whole database is logically consistent. For example, if you restore a file, you cannot recover it and bring it online until it has been rolled far enough forward to be consistent with the database.
Note
Backup and restore works correctly with the vardecimal storage format. But each Database Engine must be upgraded to at least SQL Server 2005 Service Pack 2, and all related databases must be enabled for vardecimal storage format. For example, you cannot restore the backup of a vardecimal storage format database to database that does not have vardecimal storage format enabled, and you cannot restore a backup of a service pack 2 database that has vardecimal storage format enabled to an earlier version of SQL Server. For more information about vardecimal storage format, see Storing Decimal Data As Variable Length.
Advantages of a File or Page Restore
Restoring and recovering files or pages, instead of the whole database, provides the following advantages:
- Restoring less data reduces the time required to copy and recover it.
- On SQL Server 2005 Enterprise Edition, restoring files or pages might allow other data in the database to remain online during the restore operation.
Recovery Models and Supported Restore Operations
The restore operations that are available for a database depend on its recovery model. The following table summarizes whether and to what extent each of the recovery models supports a given restore scenario.
| Restore operation | Full recovery model | Bulk-logged recovery model | Simple recovery model |
|---|---|---|---|
Data recovery |
Complete recovery (if the log is available). |
Some data-loss exposure. |
Any data since last full or differential backup is lost. |
Point-in-time restore |
Any time covered by the log backups. |
Disallowed if the log backup contains any bulk-logged changes. |
Not supported. |
File restore * |
Full support. |
Sometimes.** |
Available only for read-only secondary files. |
Page restore * |
Full support. |
Sometimes.** |
None. |
Piecemeal (filegroup-level) restore * |
Full support. |
Sometimes.** |
Available only for read-only secondary files. |
* Available only in the SQL Server 2005 Enterprise Edition.
** For the required conditions, see Restore Restrictions Under the Simple Recovery Model.
Additional Topics
| Topic | Description |
|---|---|
Contains an introduction to the restore scenarios that are available for SQL Server backups under the simple recovery model. |
|
Describes restore considerations that are unique to bulk-logged recovery model. |
|
Understanding How Restore and Recovery of Backups Work in SQL Server |
Provides the basic concepts of restore and media recovery and how they work. |
See Also
Concepts
Backup Overview (SQL Server)
Overview of the Recovery Models
Working with Transaction Log Backups
Other Resources
Creating Full and Differential Backups of a SQL Server Database
Implementing Restore Scenarios for SQL Server Databases
Working with Backup Media in SQL Server
Working with Restore Sequences for SQL Server Databases
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|
17 July 2006 |
|