FIX: RESTORE Fails When Fewer Devices Used on RESTORE than BACKUP (248988)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q248988
BUG #: 55322 (SQLBUG_70)

SYMPTOMS

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.

WORKAROUND

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

STATUS

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.

MORE INFORMATION

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.
Modification Type:MajorLast Reviewed:3/14/2006
Keywords:kbBug kbfix KB248988 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.