The information in this article applies to:
- Microsoft SQL Server, versions 4.2x, 6.0, and 6.5
NOTE: This article is specific to Microsoft SQL Server on Windows NT
platforms.
SUMMARY
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 device.
The purpose of this article is to document a method for testing the SQL
Server dump and load procedures with a tape drive. For additional
information on specific problems with SQL Server dumps or loads, see the
following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q123405
TITLE : INF: How to Troubleshoot SQL Server Tape Read/Write
Errors
MORE INFORMATION
Preliminary Checks
- 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.
NOTE: The manufacturer's documentation or the Windows NT
documentation will provide details on which driver is appropriate
for the tape drive attached to the system).
- 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 procedure:
sp_helpdevice <backup_device_name>
The result you receive should be in the following format, where X is the
tape device number:
\\.\tapeX
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.
Testing Procedures
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 Administrative
privileges.
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 window:
dump database pubs to tapedump with init,nounload
go
NOTE: Replace tapedump with the SQL Server logical name for the system
being tested.
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:
94/02/01 15:49:08.83 kernel Tape pubs SQ0001 mounted on tape
drive \\.\TAPE0
- Open the Backup or Dump device folder from SQL Enterprise Manager, and
then open the tape backup device. Verify that the Volume Label and the
dump Header are displayed.
- Switch back to the ISQL/W query window and execute the following
statements:
dump database pubs to tapedump with noinit,nounload
go
dump database pubs to tapedump with noinit,nounload
go
You should receive the following messages:
Msg 4029, Level 10, State 1:
Database 'pubs' (89 pages) dumped to file <2> on tape 'SQ0001'.
Msg 4029, Level 10, State 1:
Database 'pubs' (89 pages) dumped to file <3> on tape 'SQ0001'.
Review the SQL Server error log, which should have the following
corresponding messages:
94/02/01 15:49:08.83 kernel Tape pubs SQ0001 mounted on tape
drive \\.\TAPE0
94/02/01 15:50:18.83 kernel Tape pubs SQ0001 mounted on tape
drive \\.\TAPE0
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 following
statements:
load database pubs from tapedump with file=2,nounload
go
Review the SQL Server error log, which should record results similar to
the following:
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 'pubs'
database.
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.
Problems
- Review the system log of the Windows NT Event Viewer application
(found under Administrative tools). Inspect it for any SCSI or tape
device error.
- 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 drivers.
- 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
Viewer.
- Specific tape drive make and model information (not who makes the
internal components).
- The results from executing sp_helpdevice and xp_msver.
|