sp_addumpdevice
Adds a backup device to Microsoft® SQL Server™.
Syntax
sp_addumpdevice [ @devtype = ] 'device_type' ,
[ @logicalname = ] 'logical_name' ,
[ @physicalname = ] 'physical_name'
[ , { [ @cntrltype = ] controller_type
| [ @devstatus = ] 'device_status'
}
]
Arguments
[@devtype =] 'device_type',
Is the type of backup device. device_type is varchar(20), with no default, and can be one of these values.
| Value | Description |
|---|---|
| disk | Hard disk file as a backup device. |
| pipe | Named pipe. |
| tape | Any tape devices supported by Microsoft Windows NT®. If device is tape, noskip is the default. |
[@logicalname =] 'logical_name'
Is the logical name of the backup device used in the BACKUP and RESTORE statements. logical_name is sysname, with no default, and cannot be NULL.
[@physicalname =] 'physical_name'
Is the physical name of the backup device. Physical names must follow the rules for operating-system file names or universal naming conventions for network devices, and must include a full path. physical_name is nvarchar(260), with no default value, and cannot be NULL.
When creating a backup device on a remote network location, be sure that the name under which SQL Server was started has appropriate write capabilities on the remote computer.
If you are adding a tape device, this parameter must be the physical name assigned to the local tape device by Windows NT®, for example, \\.\TAPE0 for the first tape device on the computer. The tape device must be attached to the server computer; it cannot be used remotely. Enclose names containing nonalphanumeric characters in quotation marks.
[@cntrltype =] controller_type
Is not required when creating backup devices. It is acceptable to supply this parameter for scripts, but SQL Server ignores it. controller_type is smallint, with a default of NULL, and can be one of these values.
| Value | Description |
|---|---|
| 2 | Use when device_type is disk. |
| 5 | Use when device_type is tape. |
| 6 | Use when device_type is pipe. |
[@devstatus =] 'device_status'
Is whether ANSI tape labels are read (noskip) or ignored (skip). device_status is varchar(40), with a default value of noskip.
Note Either specify controller_type or device_status, but not both.
Return Code Values
0 (success) or 1 (failure)
Result Sets
None
Remarks
sp_addumpdevice adds a backup device to the master.dbo.sysdevices table. It can then be referred to logically in BACKUP and RESTORE statements.
Ownership and permissions problems can interfere with the use of disk or file backup devices. Make sure that appropriate file permissions are given to the account under which SQL Server was started.
SQL Server supports tape backups to tape devices that are supported by Windows NT. For more information about Windows NT-supported tape devices, see the hardware compatibility list for Windows NT. To view the tape devices available on the computer, use SQL Server Enterprise Manager.
Use only the recommended tapes for the specific tape drive (as suggested by the drive manufacturer). If you are using DAT drives, use computer-grade DAT tapes (Digital Data Storage-DDS).
sp_addumpdevice cannot be executed inside a transaction.
Permissions
Only members of the sysadmin and diskadmin fixed server roles can execute this procedure.
Examples
A. Add a disk dump device
This example adds a disk backup device named MYDISKDUMP, with the physical name C:\Dump\Dump1.bak.
USE master
EXEC sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump\dump1.bak'
B. Add a network disk backup device
This example shows a remote disk backup device. The name under which SQL Server was started must have permissions to that remote file.
USE master
EXEC sp_addumpdevice 'disk', 'networkdevice',
'\\servername\sharename\path\filename.ext'
C. Add a tape backup device
This example adds the TAPEDUMP1 device with the physical name \\.\Tape0.
USE master
EXEC sp_addumpdevice 'tape', 'tapedump1',
'\\.\tape0'