sp_addumpdevice (T-SQL)

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 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

Execute permissions default to members of the diskadmin fixed server role.

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