Restore and Recovery Overview (SQL Server)
SQL Server 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.
Note
In SQL Server 2008, you can restore a database backup that was created by using SQL Server 2000, SQL Server 2005, or SQL Server 2008. However, backups of master, model and msdb that were created by using SQL Server 2000 or SQL Server 2005 cannot be restored by SQL Server 2008. Also, SQL Server 2008 backups cannot be restored by any earlier version of SQL Server.
SQL Server backup and restore work across all supported operating systems, whether they are 64-bit or 32-bit systems. For information about the supported operating systems, see Hardware and Software Requirements for Installing SQL Server 2008 R2.
Restore Scenarios
A restore scenario in SQL Server is the process of restoring data from one or more backups and then recovering the database. The supported restore scenarios depend on the recovery model of the database and the edition of SQL Server.
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 and later versions, 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 and later versions, 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 and later versions.
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.
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 and later versions, 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 and later versions.
** 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