FIX: Dumping to a DLT Tape Drive May Fail to Span Tapes

A database dump may fail to span more than one tape if you are using a DLT tape drive. The first tape may become full and you will be prompted to respond to the following message: The light on the tape unit will not turn green, so you will be unable to change the tape. You will then receive the following message:

The DLT tape unit does not physically eject the tape, so SQL Server always detects a tape in the drive, and attempts to keep writing to it even when it is full.

To avoid this problem, use any of the following:

• Use a DLT drive with a higher capacity, such as a Digital TZ88, so that the dump requires only one tape.
• Use a 4 mm or 8 mm DAT tape drive.
• Make a striped dump to multiple DLT tape drives.
• Type Console.exe at the command prompt before starting the dump operation. Follow the instructions on the screen and start the dump. Be sure to follow the same process for the load operation.

Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.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.

This section provides an example of what the command prompt will look like when using the CONSOLE application to dump or load a database that spans two DLT tapes. In this example, comments on how to respond to these messages are given in square brackets.

Option I. Use the DUMP DATABASE or LOAD DATABASE Command

Microsoft(R) Windows NT(TM)
(C) Copyright 1985-1996 Microsoft Corp.
C:\>console
[Execute the DUMP DATABASE or LOAD DATABASE command from a SQL query window.]

Mount first tape SQ0001.
Press ENTER when mounted, or press 0 to abort.
[Press ENTER when the first tape is in the drive. The dump or load will continue until the end of the tape is reached and the tape drive light will turn green.]

Change tapes and mount SQ0002.
Press ENTER when mounted, or press 0 to abort.
[Press ENTER when the second tape is in the drive. The dump or load will finish on this tape.]

Remove the tape.
Press ENTER when removed.

Option II. Use SQL Enterprise Manager

When using the SQL Enterprise Manager Backup/Restore function to start the dump or load, the technique is the same as that described previously in Option I. However, the messages you see are slightly different. This is because the server will run a LOAD HEADERONLY command which, when completed, returns the following message to the CONSOLE:

Remove the diskette.
Press ENTER when removed.


When the dump or load has completed, press CTRL+C to quit the CONSOLE application.