title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | |||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sp_addumpdevice (Transact-SQL) |
Adds a backup device to an instance of SQL Server. |
markingmyname |
maghan |
randolphwest |
01/23/2024 |
sql |
system-objects |
reference |
|
|
|
[!INCLUDE SQL Server]
Adds a backup device to an instance of [!INCLUDE ssNoVersion].
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_addumpdevice
[ @devtype = ] 'devtype'
, [ @logicalname = ] N'logicalname'
, [ @physicalname = ] N'physicalname'
[ , [ @cntrltype = ] cntrltype ]
[ , [ @devstatus = ] 'devstatus' ]
[ ; ]
The type of backup device. @devtype is varchar(20), with no default, and can be one of the following values.
Value | Description |
---|---|
disk |
Hard disk file as a backup device. |
tape |
Any tape devices supported by [!INCLUDE msCoName] Windows. Note: Support for tape backup devices will be removed in a future version of [!INCLUDE ssNoVersion]. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. |
The logical name of the backup device used in the BACKUP and RESTORE statements. @logicalname is sysname, with no default, and can't be NULL
.
The physical name of the backup device. @physicalname is nvarchar(260), with no default, and can't be NULL
. Physical names must follow the rules for operating system file names, or universal naming conventions for network devices, and must include a full path.
When creating a backup device on a remote network location, be sure that the name under which the [!INCLUDE ssDE] was started has appropriate write capabilities on the remote computer.
If you add a tape device, this parameter must be the physical name assigned to the local tape device by Windows; for example, \\.\TAPE0
for the first tape device on the computer. The tape device must be attached to the server computer; it can't be used remotely. Enclose names that contain nonalphanumeric characters in quotation marks.
Note
This procedure enters the specified physical name into the catalog. The procedure doesn't attempt to access or create the device.
Obsolete. If specified, this parameter is ignored. Supported for backward compatibility. New uses of sp_addumpdevice
should omit this parameter.
Obsolete. If specified, this parameter is ignored. Supported for backward compatibility. New uses of sp_addumpdevice
should omit this parameter.
0
(success) or 1
(failure).
None.
sp_addumpdevice
adds a backup device to the sys.backup_devices
catalog view. The device can then be referred to logically in BACKUP
and RESTORE
statements. sp_addumpdevice
doesn't perform any access to the physical device. Access to the specified device only occurs when a BACKUP
or RESTORE
statement is performed. Creating a logical backup device can simplify BACKUP
and RESTORE
statements, where specifying the device name is an alternative using a TAPE =
or DISK =
clause to specify the device path.
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 Windows account under which the [!INCLUDE ssDE] was started.
The [!INCLUDE ssDE] supports tape backups to tape devices supported by Windows. For more information about Windows-supported tape devices, see the hardware compatibility list for Windows. To view the tape devices available on the computer, use [!INCLUDE ssManStudioFull].
Use only the recommended tapes for the specific tape drive suggested by the drive manufacturer. If you're using digital audio tape (DAT) drives, use computer-grade DAT tapes (Digital Data Storage (DDS)).
sp_addumpdevice
can't be executed inside a transaction.
To delete a device, use sp_dropdevice or Delete a Backup Device (SQL Server).
Requires membership in the diskadmin fixed server role.
Requires permission to write to the disk.
The following example adds a disk backup device named mydiskdump
, with the physical name C:\dump\dump1.bak
.
USE master;
GO
EXEC sp_addumpdevice 'disk', 'mydiskdump', 'C:\dump\dump1.bak';
The following example shows adding a remote disk backup device called networkdevice
. The name under which the [!INCLUDE ssDE] was started must have permissions to that remote file (\\<servername>\<sharename>\<path>\<filename>.bak
).
USE master;
GO
EXEC sp_addumpdevice 'disk', 'networkdevice',
'\\<servername>\<sharename>\<path>\<filename>.bak';
The following example adds the tapedump1
device with the physical name \\.\tape0
.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'tapedump1', '\\.\tape0';
The following example creates a logical backup device, AdvWorksData
, for a backup disk file. The example then backs up the [!INCLUDE ssSampleDBobject] database to this logical backup device.
USE master;
GO
EXEC sp_addumpdevice
'disk',
'AdvWorksData',
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\AdvWorksData.bak';
GO
BACKUP DATABASE AdventureWorks2022
TO AdvWorksData WITH FORMAT;
GO
- Backup Devices (SQL Server)
- BACKUP (Transact-SQL)
- Define a Logical Backup Device for a Disk File (SQL Server)
- Define a Logical Backup Device for a Tape Drive (SQL Server)
- RESTORE Statements (Transact-SQL)
- sp_dropdevice (Transact-SQL)
- sys.backup_devices (Transact-SQL)
- System stored procedures (Transact-SQL)