Restoring in the Correct Order

When you restore from a full backup, every block in use at the time the backup was made is written to disk. When you restore from an incremental backup, only the blocks that changed between the previous backup (or the previous full backup) and this backup are written to disk.

You must restore full and incremental backups in the correct order, with a separate RESTORE command for each backup you are restoring. RESTORE ensures that backups are restored in order, and gives the following error if it determines that the order is incorrect:

SQL Code:  -1012009

SQL State: QUA09

This restore cannot immediately follow the previous restore.

To determine the correct order, you need the information about backup files that is stored in the backup log.

Restore backups as follows:

If your database is inconsistent, or if you are moving any files to a new location, you must restore a FULL backup.
If your most recent backup is a FULL backup, or if you need to restore a database to the state that existed before any existing incremental(s) were made, restore the full backup only.
If you have an INCREMENTAL_SINCE_FULL backup that precedes the database failure, first restore from the last FULL backup, and then restore the INCREMENTAL_SINCE_FULL backup.
If you do not have an INCREMENTAL_SINCE_FULL backup, but you have performed one or more INCREMENTAL backups since your last FULL backup, first restore the FULL backup, and then restore the INCREMENTAL backups in the order in which they were made.

You can also use the advisory stored procedure sp_iqrestoreaction to suggest the sequence of restore actions required to attain a stable database set. Always confirm the steps suggested against above rules. The stored procedure also does not factor in moving any database files.

Within a given backup, the order in which you restore tapes is also important. In particular, you need to keep track of the order of tapes in each backup tape set, that is, the set of tapes produced in a given backup on a given archive device:

You must restore the tape set that contains the backup of the catalog store first, and it must be on the first archive device.
Within each set, you must restore tapes in the order in which they were created.
You cannot interleave sets; each set must be restored before you can restore another set.
After the first set, the order in which sets are restored does not matter, as long as it is correct within each set.

Use the same number of drives to restore as were used to produce the backup, so that you do not accidentally interleave tapes from different sets.

Example

Assume that you are restoring a full backup, in which you used three archive devices, and thus produced three tape sets, A, B, and C.

The contents of each set, and the restore order, are as follows:

Set A – Tapes A₁, A₂, and A₃. Tapes A₁ and A₂ contain the catalog store. This set must be restored first, and must be in the first device.
Set B – Tapes B₁ and B₂. These must be restored as a set, after Set A, and either before or after Set C. They can be in either the second or third device.
Set C – Tapes C₁, C₂, and C₃. These must be restored as a set, after Set A, and either before or after Set B. They can be in either the second or third device.

The Restore program checks that tapes within each set are in the correct order on a single device. If not, you get an error, and the restore does not proceed until you supply the correct tape. Except for the set with the catalog store, it does not matter which set you put on a given device.

Note: You must ensure that the catalog store tape set is restored first. The Restore program does not check this.

Although these rules also apply to disk files, you are not likely to back up to multiple files on a given disk device.