Article ID: 927797
Article Last Modified on 11/20/2007
APPLIES TO
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Enterprise X64 Edition
- Microsoft SQL Server 2005 Standard X64 Edition
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2000 Personal Edition
- Microsoft SQL Server 2000 Developer Edition
- Microsoft SQL Server 2000 Enterprise Edition 64-bit
SYMPTOMS
When you use a universal serial bus (USB)-based tape drive as a backup device on a computer that is running Microsoft SQL Server 2000 or Microsoft SQL Server 2005, you cannot specify the USB-based tape drive as a backup device in SQL Enterprise Manager or in SQL Server Management Studio.
CAUSE
This issue occurs because the xp_get_tape_devices stored procedure does not recognize the registry key of the USB device. The xp_get_tape_devices stored procedure checks for usable tape devices.
The xp_get_tape_devices stored procedure accesses the following backup device registry subkey:
HKEY_LOCAL_MACHINE\HARDWARE\DEVICEMAP\Scsi
The USB backup device is stored under the following registry subkey:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\USB
RESOLUTION
To work around this issue, follow these steps:
Use the following Transact-SQL statement to register the USB-based tape device:
USE master go EXEC sp_addumpdevice 'tape', 'tapedump1','\\.\Tape0'
Note "tapedump1" is registered as a \\.\Tape0 physical name.
Use a Backup Database Transact-SQL statement to back up a database. For example, use a Backup Database Transact-SQL statement such as the following:
USE <myDatabase> go Backup Database <myDatabase> TO tapedump1
Note
<myDatabase>is a placeholder for the database to back up.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
Steps to reproduce the problem
SQL Server 2005
- In SQL Server Management Studio, expand Server Objects.
- Right-click Backup Devices, and then click New Backup Device.
Notice that you cannot select the Tape option under Destination.
SQL Server 2000
- In SQL Server Enterprise Manager, expand Management.
- Right-click Backup, and then click New Backup Device.
Notice that you cannot select Tape drive name in the Backup Device Properties dialog box.
Keywords: kbtshoot kbprb kbbackup kbexpertiseinter KB927797
