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

Controlling Creation of Added Files During Roll Forward

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.

Restoring Renamed Files and Filegroups

Discusses how restore handles a changed name and a reused file or filegroup name.

Using the REPLACE Option

Discusses how you can override the safeguards that prevent the accidental overwriting of a database with a different database.

Caution:

The REPLACE option should be used only after careful consideration.

See Also

Concepts

Using the REPLACE Option

Other Resources

RESTORE (Transact-SQL)
Working with Restore Sequences for SQL Server Databases

Help and Information

Getting SQL Server 2005 Assistance