sp_addumpdevice System Stored Procedure

Adds a dump device to SQL Server.

Syntax

sp_addumpdevice {'disk' | 'diskette' | 'tape'}, 'logical_name',
'physical_name' [, {{cntrltype [, noskip | skip [, media_capacity]]} |
{@devstatus = {noskip | skip}}}]

where

disk | diskette | tape

Specifies the type of dump device. Use disk to add a hard disk file as a dump device. Use diskette to add a 5.25-inch or 3.5-inch diskette drive file as a dump device. Use tape for any tape devices supported by Windows NT. Enclose these options in single quotation marks.

logical_name

Is the logical name of the dump device used in the LOAD and DUMP statements. This name must follow the rules for identifiers.

physical_name

Is the physical name of the dump device. Physical names must follow the rules for operating-system filenames or universal naming conventions for network devices and must include a full path.

When creating a dump device on a remote network location, be sure that the name under which the 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 non-alphanumeric characters in quotation marks.

cntrltype

Is not required when creating dump devices. However, if used in scripts, it is still acceptable to supply this parameter, but SQL Server ignores it.

Note As of SQL Server version 4.2, sp_addumpdevice no longer uses the cntrltype or media_capacity parameters because SQL Server automatically detects the control type and media capacity information. In this release of SQL Server, the syntax is still supported; however, both parameters will be removed in a future release.

skip | noskip

Indicates whether ANSI tape labels are read (noskip) or ignored (skip). For example, when skip is specified and the ANSI label of a tape warns that it has expired or that you don't have permission to write to it, SQL Server ignores the warning. Or, if the tape you are writing to is new (and therefore has no ANSI label), SQL Server writes a new label on the first try. The skip option prevents unnecessary retries as SQL Server tries to find a label.

Specifying noskip tells SQL Server to read existing ANSI tape labels on the tape to which you are writing. Use noskip for diskette dump devices.

The sp_addumpdevice system stored procedure lets you choose between the security of paying attention to ANSI labels or the convenience of ignoring them. In either case, SQL Server still reads/writes the labels.

media_capacity

Is not required when creating dump devices. However, if used in scripts, it is still acceptable to supply this parameter, but SQL Server ignores it. For more information, see contrltype, above.

@devstatus = skip | noskip

Explicitly defines whether ANSI tape labels are read (noskip) or ignored (skip). Use this local variable when you do not explicitly use the ctrltype or media_capacity parameters.

Remarks

The sp_addumpdevice system stored procedure adds a dump device to the master.dbo.sysdevices table. It can then be referred to logically in the DUMP, DUMP TRANSACTION, and LOAD statements.

Ownership and permission problems can interfere with the use of disk or file dump 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 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). If you are using a non-computer-grade tape, SQL Server backups are restricted to a single tape (a database dump that spans multiple tapes is not allowed).

Examples

A.    Disk Dump Device

This example adds a disk device named MYDISKDUMP, with the physical name C:\DUMP\DUMP1.DAT.

sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump\dump1.dat'

B.    Network Disk Dump Device

This example shows a remote disk device dump. The name under which SQL Server was started must have permissions to that remote file.

sp_addumpdevice 'disk', 'networkdevice',
    '\\servername\sharename\path\filename.ext'

C.    Tape Dump Device

This example adds the TAPEDUMP1 device with the physical name \\.\TAPE0.

sp_addumpdevice 'tape', 'tapedump1',
    '\\.\tape0'

Permission

Execute permission defaults to the system administrator.

Table Used

master.dbo.sysdevices

See Also