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.

[@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.

[@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'

See Also

BACKUP

RESTORE

sp_dropdevice

sp_helpdevice

System Stored Procedures