Microsoft KB Archive/927797

= You cannot specify a USB-based tape drive as a backup device in SQL Server 2005 and in SQL Server 2000 =

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:

The USB backup device is stored under the following registry subkey:



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 &quot;tapedump1&quot; 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  go Backup Database  TO tapedump1 Note  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 &quot;Applies to&quot; section.



SQL Server 2005

 * 1) In SQL Server Management Studio, expand Server Objects.
 * 2) Right-click Backup Devices, and then click New Backup Device.

Notice that you cannot select the Tape option under Destination.

SQL Server 2000

 * 1) In SQL Server Enterprise Manager, expand Management.
 * 2) 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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.