INF: Testing Methods for SQL Server Tape Dumps or Loads

ID: Q124023

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 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:

Q123405 : 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.

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.

Additional query words: test


Keywords          : kbusage SSrvAdmin 
Version           : WINNT:4.2x 6.0 6.5
Platform          : winnt 
Issue type        :

Last Reviewed: April 15, 1999