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

Article translations Article translations
Article ID: 248988 - View products that this article applies to.
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)
Expand all | Collapse all

On This Page

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.

Properties

Article ID: 248988 - Last Review: October 22, 2013 - Revision: 2.2
APPLIES TO
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbbug kbfix KB248988

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com