Advanced Considerations for Restore Sequences
The topics in this section address the following considerations for restore sequences:
Changing the roll forward set after roll forward has started
Skipping roll forward of data in read-only files
Using multiple full backups of the same data
Executing concurrent backup and restore in a single database
Also, this section contains several advanced topics.
Changing the Roll Forward Set After Roll Forward Has Started
You cannot remove files or pages from a roll forward set, but you can add them. Adding a file is useful, for example, if you start a partial restore sequence and then realize that you want to include more files.
Adding a file or page might require repeating some of the roll forward. However, read-only files that are consistent with the database can be added to the roll forward set without repeating any of the roll forward; for more information, see Controlling Creation of Added Files During Roll Forward.
Adding pages is subject to the restriction described in for "Multiple Copies of the Same Data," later in this topic.
Skipping Roll Forward of Data in Read-Only Files
Note
For an introduction to the redo phase, during which rolling forward occurs, see Understanding How Restore and Recovery of Backups Work in SQL Server.
When you restore a read-only file to a filegroup that has read-only since before the backup was created, the file is consistent with the rest of the database, so the redo phase is skipped. After restoring such read-only files from the backup (during the data-copy phase of restore), the SQL Server Database Engine immediately recovers those files. Then, it continues the redo phase to roll forward the rest of roll forward set, if any.
If a filegroup became read-only after the latest full backup but before a differential backup and has remained read-only, roll forward occurs up to the point in time at which the differential backup was created. Under the full recovery model, if a filegroup became read-only after the latest full backup and the latest differential backup, if any, you can use log backups to roll forward the data in a file until the filegroup reaches the read-only point.
Using Multiple Data Backups of the Same Data
Because multiple restore statements can be issued, you can copy data from different full backups into the same location. For example, one RESTORE statement copies all the files in a database. The next statement in the restore sequence overwrites one of the files. Generally, this is allowed and the most recent data restored is used.
However, special rules apply to page restores. Pages cannot be copied to a file after the whole file has been restored; trying to do this causes an error. The restore sequence can continue, but the page is not restored.
Executing Concurrent Backup and Restore in a Single Database
In some circumstances, BACKUP and RESTORE statements that affect the same database can be run at the same time. When an operation is not allowed because of an operation that is already in progress, the Database Engine issues an error.
The following table indicates whether each of the possible combinations of concurrent statements is allowed or is disallowed.
Statement in progress |
BACKUP DATABASE |
BACKUP LOG |
Offline RESTORE |
Online RESTORE |
|---|---|---|---|---|
BACKUP DATABASE |
Error |
Permitted |
Error |
Error |
BACKUP LOG |
Permitted |
Error |
Error |
Error |
Offline RESTORE |
Error |
Error |
Error |
Error |
Online RESTORE |
Error |
Permitted |
Error |
Error |
In This Section
Topic |
Description |
|---|---|
Discusses how, when you use restore to repair a known and isolated problem, you can optimize restore performance by restoring only files in which the problem appears. |
|
Discusses how restore handles a changed name and a reused file or filegroup name. |
|
Discusses how you can override the safeguards that prevent the accidental overwriting of a database with a different database. .gif "Caution note") Caution
The REPLACE option should be used only after careful consideration.
|
See Also