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. See “Getting information about backups and restores” for the content and location of this file.

Restore backups as follows:

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:

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 A1, A2, and A3. Tapes A1 and A2 contain the catalog store. This set must be restored first, and must be in the first device.

Set B Tapes B1 and B2. 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 C1, C2, and C3. 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.

NoteYou 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.