The tape device subsystem is made up of several layers that
are required for successful SQL Server database dump and load procedures. The
primary level is the hardware level, which is made up of the tape device,
cable, terminator, and SCSI controller. The kernel layer consists of the
Windows NT kernel, I/O Manager, file system driver, and the tape device driver.
The final layer is the user or application level. At this level, SQL Server
operates and depends on the operation of the underlying hardware and operating
system levels for successful completion of dump and load operations to a tape
The purpose of this article is to document a method for
testing the SQL Server dump and load procedures with a tape drive.
Make sure that the specific tape device is on the hardware
compatibility list (HCL). The devices on the HCL have been tested by Microsoft,
at the request of the manufacturer, for use with the Windows NT operating
system. Tape drive manufacturers frequently sell tape drives to original
equipment manufacturer (OEM) vendors, and it is possible that the firmware for
the repackaged drive has been changed, according to the request of the OEM
vendor. These changes may result in problems with the operation of the tape
device under Windows NT and/or SQL Server. As a result, even though the
underlying tape drive may have been manufactured by a vendor on the HCL, the
tape drive that has been repackaged and sold under a different vendor's label
may have compatibility problems, due to the firmware changes.
Install the tape device according to the manufacturer's
recommendations. Make sure you comply with the recommendations for the SCSI
controller, SCSI cable length, and type of SCSI terminator.
Check with the vendor for changes in the driver being used
for the tape device. Some vendors will provide tape drivers for use with
Windows NT. Tape drives supplied by Compaq, which are not on the HCL, should
use drivers from the Compaq-recommended software support disk (SSD). This is
because the Compaq tape drive is solely supported by Compaq.
Verify that the tape device has been installed
appropriately under Windows NT. The following are three areas to check to
verify the tape device setup:
For computers running Windows NT 3.5x, run Windows NT
Setup, which is in the Main program group in Windows NT Program Manager. On the
Options menu in Setup, click Add/Remove Tape Devices. A driver should be listed
that matches the tape drive.
For computers running Windows NT 4.0,
check the tape device driver in Control Panel Tape Devices.
The manufacturer's documentation or the Windows NT documentation will provide
details on which driver is appropriate for the tape drive attached to the
In Control Panel Devices, the tape drive should be
listed as Started and have a startup setting of System.
Start Ntbackup.exe. On the Operations menu, click
Hardware Setup. If the device has been properly set up, the tape drive will be
visible in the drop down list box.
Test with Ntbackup.exe. Make sure that a successful archive
and restore of a file or group of files can be accomplished without error.
NOTE: It is important to perform both of these operations.
Also view the tape catalog and perform an archive with a
verification. If there are any problems with this procedure, the SQL Server
dump and load to tape will either not work or not work reliably.
Use SQL Enterprise Manager to verify SQL Server's setup of
the tape dump device. Go to a query window and execute the following stored
The result you receive should be in the following
format, where X is the tape device number:
By default, the device numbers start with tape0...tapeX,
depending on the number of tape devices found by NTDetect when the system
starts up. Generally, on a system with a single tape drive, the system device
identifier will be tape0, and the physical definition will be \\.\tape0.
For SQL Server 4.2x, go to SQL Administrator, click Devices, then
double-click the tape dump device. Make sure that the physical path is correct.
NOTE: The following procedures must be tested by an
Administrative login. The purpose is to verify that the tape unit works, not
the system rights belonging to a particular login. So if the login for SQL
Server has been changed from the default, ensure that the login has
Also, it is assumed that the database
being used for the basic and advanced tests have no structural problems. If the
state of the database is unknown or if it has not been checked recently,
execute DBCC CHECKDB and DBCC NEWALLOC.
Review the output of these
DBCC checks and correct any structural problems before attempting the following
tests. Structural problems that exist in a database at the time of the database
dump to tape may cause a database load from the tape device to fail.
Basic Testing Procedure
Before beginning the procedure, use SQL Server Setup to
disable automatic startup for SQL Server and SQLExecutive (or SQL Monitor for
SQL Server 4.2x).
Turn the server off. Wait approximately 60 seconds, then
turn the unit back on. If the server has an external tape drive, turn it on
first and wait until it has completed initialization before turning the server
on. This is an important step, because it resets the tape device back to the
embedded default settings.
Start SQL Server and go to a command prompt.
Log in to SQL Server as system administrator (SA) and go to
the ISQL/W query window.
Execute the following statements from the ISQL/W query
dump database pubs to tapedump with init,nounload
NOTE: Replace tapedump with the SQL Server logical name for the system
You should receive the following message:
Msg 4029, Level 10, State 1: Database 'pubs' (89 pages)
dumped to file <1> on tape 'SQ0001'.
Review the SQL Server
error log, which should have the following corresponding message:
If the SQL Server error log does not contain these messages,
review the previous steps and repeat if necessary. Go to the "Problem" section
of this article.
Switch back to the ISQL/W query window and execute the
load database pubs from tapedump with file=2,nounload
Review the SQL Server error log, which should record results similar to
94/02/01 15:49:08.83 server Recovery dbid 4 ckpt (1017,8)
NOTE: The information found in the error log may be different,
depending on the dbid of the database the dump is loaded into. Additionally,
the ckpt value in parenthesis is the location of the last checkpoint record
that was found during the recovery process, so it may also vary.
Testing complete. If there are problems, see the "Problems"
section of this article.
Advanced Testing Procedure
This section details more extensive testing. Proceeding beyond
this point, an assumption is made that the tape system has been thoroughly
tested with NTBackup, which resulted in the successful completion of both
archive and restore procedures. It is recommended that you do not use
production or important development databases for testing unproved hardware.
The advanced testing procedure varies mainly in the size of the
database involved and its importance to the organization. If resources are
available, it is certainly appropriate to create a database that mirrors a
production or important development database in size and content for this
testing. It is also important to realize that at some point the production or
development database will need to be dumped to tape.
As a result,
the following procedure has some built-in redundancy that is illustrated by
step 2. The following is the advanced testing procedure:
Shut down SQL Server, either from the SQL Services Manager
or from a command prompt by using a NET STOP MSSQLSERVER command (for SQL
Server 4.2x, the command is NET STOP SQLSERVER).
Using Ntbackup.exe, archive ALL database device files,
including Master.dat and any devices used for tempdb. This procedure MUST be
performed with the Verify option of NTBackup.
Start SQL Server either through the SQL Services Manager or
from a command prompt, with a NET START MSSQLSERVER command.
Perform steps 1-0 as listed in the "Basic Testing
Procedure" section of this article, substituting the desired database for the
Final Notes on Testing
The successful completion of the testing procedures outlined
above does not guarantee that there will never be problems with your SQL Server
dump or load operations to tape. SQL Server tape operations depend on the
successful operation of all underlying layers that make up the tape subsystem,
as well as a database that is free of structural problems.
Review the system log of the Windows NT Event Viewer
application (found under Administrative tools). Inspect it for any SCSI or tape
Review the application log of the Windows NT Event Viewer
application for any non-SQL Server errors.
Review the SQL Server error log.
Call the appropriate hardware vendor and verify that the
problems seen have not been corrected by newer firmware versions or device
Correct any obvious issues and perform testing again.
Contact the appropriate vendor support.
When calling Microsoft SQL Server Support, be prepared to
provide the following information:
The registry entries found in
HKEY_LOCAL_MACHINE\Hardware\Devicemap for each tape device. Include the
specifics regarding the number, type, and manufacturer of each SCSI controller,
the number of devices on the SCSI Port, and SCSI Bus.
The file size, date, and time for the tape driver being
used by the device in question.
Results from the test procedures detailed above, as well as
the SQL Server error log, system log, and application log from the Event
Specific tape drive make and model information (not who
makes the internal components).
The results from executing sp_helpdevice and xp_msver.