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

This article was previously published under Q248988
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 55322 (SQLBUG_70)
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:
254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0
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 TestDBto 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 willnow be reused for one of the remaining RAID streams.The RAID stream on device '\\.\tape0' has been finished. The device willnow 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 willnow 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.


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.
stripe striped backup restore extra

Article ID: 248988 - Last Review: 10/22/2013 04:16:57 - Revision: 2.2

  • Microsoft SQL Server 7.0 Standard Edition
  • kbnosurvey kbarchive kbbug kbfix KB248988