SQL Server Management Studio (SSMS) – Unable to detect Tape Drive
Today I was walking a customer through SQL Server database backup on tape and realized that I’m unable to view the tape drive from SQL Server Management Studio. At this time I took a pause to check the if all is well with the Tape Drive.
Here’s tape drive config:
• Device – IMB Ultirum-TD3 scsi
• Device is physically attached to the server and tape is mounted properly
To validate further, I took a sample backup of file-folder on tape using “NTBACKUP” and it completed successfully. So tape is responding well!
To nail down the issue, I tried taking SQL database backup using ‘T-SQL’ statement, as below:
BACKUP DATABASE master TO TAPE='\\.\TAPE0'
And it completed successfully. So SQL is actually recognizing tape drive.
Then why is TAPE not visible from SSMS or EM?
With no ideas left, I switched to Bing and got KB # 953122. Reading between the lines, I could understand the issue as below:
SQL Server uses an extended procedure, "xp_get_tape_devices", to detect the presence of tape drives by looking for a certain Registry Key under:
HKEY_LOCAL_MACHINE\HARDWARE\DEVICEMAP\Scsi
• In the registry tree below this point, it tries to find a value entitled "Type" with the REG_SZ value, "TapePeripheral".
• However, some this drives use a Registry value that is called "DeviceType" instead of "Type". Refer below:
---- SAMPLE of INCORRECT REGISTRY ENTRY ----
"Identifier"="IBM ULTIRUM"
"DeviceType"="TapePeripheral"
---- SAMPLE of CORRECT REGISTRY ENTRY ----
"Identifier"=" IBM ULTIRUM"
"Type"="TapePeripheral"
• So the SQL procedure “xp_get_tape_devices” cannot find the tape drives that are installed on the system.
RESOLUTION
Quick Fix:
Step.1 HKEY_LOCAL_MACHINE\HARDWARE\DEVICEMAP\Scsi
Step2. Manually create the "Type" value with the appropriate REG_SZ entry of "TapePeripheral"
Caution: After next server reboot, this entry will get removed.
Longer Workaround:
Step 1. Click Start, click Run, type regedit, and then press ENTER.
Step 2. HKEY_LOCAL_MACHINE\HARDWARE\DEVICEMAP\SCSI\Scsi Port <Num1>\Scsi Bus <Num2>\Target Id <Num3>\Logical Unit Id <Num4>
Here: In this registry subkey, Num1 represents a SCSI port. Num2 represent a SCSI bus. Num3 represents a target ID. Num4 represents a logical unit ID
Next, follow below steps to make the registry changes permanent across server reboot.
1. Click Start, click Run, type Regedit, and then click OK.
2. Locate the following registry subkey: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run
3. Create a New String "TapeReg" of Type "REG_SZ" and Value
reg add "HKLM\HARDWARE\DEVICEMAP\Scsi\Scsi Port 3\Scsi Bus 0\Target Id 8\Logical Unit Id 0" /v Type /t REG_SZ /d "TapePeripheral", and then click OK Note: Refer Step 2. To get exact value specific to you server
4. Re-Start the server.
5. Once started, Click Start, click Run, type Regedit, and then click OK.
6. Locate the following registry subkey:
HKLM\HARDWARE\DEVICEMAP\Scsi\Scsi Port 3\Scsi Bus 0\Target Id 8\Logical Unit Id 0
7. A New String key "Type" with value "TapePeripheral" will be added
8. Open SQL Server Management Studio, and Run statement “xp_get_Tape_devices”
9. Output: \\.\Tape0
Thanks for reading in 2009 & hope to see you even more in 2010!
Wish you ‘Joyous Holidays’ and ‘Happy New Year’.
Disclaimer: I work at Microsoft. Everything here, though, is my personal opinion and is not read or approved by Microsoft before it is posted. No warranties or other guarantees will be offered as to the quality of the opinions or anything else offered here.