FIX: RESTORE Fails When Fewer Devices Used on RESTORE than BACKUP

A database, log, file or file group backup, which was backed up to multiple tape backup devices (otherwise known as a striped dump), may fail to restore if fewer devices are used on the RESTORE operation than were used on the BACKUP. An extra tape mount is requested that can never be satisfied.

Note that this problem occurs with either the native SQL Server RESTORE command, or with the Original Equipment Manufacturer (OEM) backup/restore solutions that utilize the SQL Server Virtual Backup Device Interface (VDI) API.

Restore using either a single backup device, or the same number of devices used to create the striped backup.

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:For more information, contact your primary support provider.

SQL Server allows the use of multiple devices on BACKUP and RESTORE operations. This allows SQL Server to parallelize reads and writes during BACKUP and RESTORE operations thus improving BACKUP and RESTORE performance and throughput.

By way of example, in the case where a database backup was striped across four tape backup devices, and the media set consists of four tapes, SQL Server incorrectly requests the mount of a fifth tape.

The following example demonstrates the manifestation of this problem:

1. A striped BACKUP is created to four tape devices:

   backup database TestDB
   to tape = '\\.\TAPE0',
   tape = '\\.\TAPE1',
   tape = '\\.\TAPE2',
   tape = '\\.\TAPE3'
   with format
   						

2. A restore is attempted utilizing two tape devices with the following command:

   restore database TestDB 
   from tape = '\\.\tape0',
   tape = '\\.\tape1'
   with stats = 10
   						

   10 percent restored.
   20 percent restored.
   30 percent restored.
   40 percent restored.
   The RAID stream on device '\\.\tape1' has been finished. The device will
   now be reused for one of the remaining RAID streams.
   The RAID stream on device '\\.\tape0' has been finished. The device will
   now be reused for one of the remaining RAID streams.
   50 percent restored.
   60 percent restored.
   70 percent restored.
   The RAID stream on device '\\.\tape0' has been finished. The device will
   now be reused for one of the remaining RAID streams.
   80 percent restored.
   90 percent restored.
   100 percent restored.
   						

Note that the extra (last) outstanding mount request for device tape0 can never be satisfied, and the restore operation must be cancelled or it never completes. Even though the progress messages indicate that the restore operation is 100% complete, the transaction log has not yet been rolled forward against the database and as such the database is not transactionally consistent. The database should be restored again using the instructions in the "Workaround" section of this article.

REFERENCES

For more information about backing up and restoring SQL Server databases, see the chapter titled "Backing Up and Restoring Databases" in the "Administering SQL Server" manual available in SQL Server 7.0 Books Online.