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.