sp_addumpdevice (Transact-SQL)sp_addumpdevice (Transact-SQL)

适用对象: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

适用范围:SQL ServerSQL ServerSQL Server 2008SQL Server 2008当前版本) 。Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through current version).

将备份设备添加到 SQL ServerSQL Server 的实例中。Adds a backup device to an instance of SQL ServerSQL Server.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions


sp_addumpdevice [ @devtype = ] 'device_type'   
    , [ @logicalname = ] 'logical_name'   
    , [ @physicalname = ] 'physical_name'  
      [ , { [ @cntrltype = ] controller_type |  
          [ @devstatus = ] 'device_status' }  


[ @devtype = ] 'device_type' 为备份设备的类型。[ @devtype = ] 'device_type' Is the type of backup device. 设备类型varchar (20) ,无默认值,并且可以是以下值之一。device_type is varchar(20), with no default, and can be one of the following values.

Value 描述Description
diskdisk 硬盘文件作为备份设备。Hard disk file as a backup device.
tapetape MicrosoftMicrosoft Windows 支持的任何磁带设备。Any tape devices supported by MicrosoftMicrosoft Windows.

注意:在 SQL ServerSQL Server的未来版本中将不再支持磁带备份设备。Note: Support for tape backup devices will be removed in a future version of SQL ServerSQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

[ @logicalname = ] 'logical_name' 是在 BACKUP 和 RESTORE 语句中使用的备份设备的逻辑名称。[ @logicalname = ] 'logical_name' Is the logical name of the backup device used in the BACKUP and RESTORE statements. logical_namesysname,无默认值,且不能为 NULL。logical_name is sysname, with no default, and cannot be NULL.

[ @physicalname = ] 'physical_name' 是物理备份设备的名称。[ @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_namenvarchar(260) ,无默认值,且不能为 NULL。physical_name is nvarchar(260), with no default value, and cannot be NULL.

在远程网络位置上创建备份设备时,请确保启动数据库引擎Database Engine时所用的名称对远程计算机有相应的写权限。When creating a backup device on a remote network location, be sure that the name under which the 数据库引擎Database Engine was started has appropriate write capabilities on the remote computer.

如果添加磁带设备时,此参数必须是由 Windows; 分配给本地磁带设备的物理名称例如, \ \。 \TAPE0的计算机上的第一个磁带设备。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 cannot be used remotely. 如果名称包含非字母数字的字符,请用引号将其引起来。Enclose names that contain nonalphanumeric characters in quotation marks.


此过程会在目录中输入指定的物理名称。This procedure enters the specified physical name into the catalog. 此过程不会尝试访问或创建设备。The procedure does not attempt to access or create the device.

[ @cntrltype = ] 'controller_type' 已过时。[ @cntrltype = ] 'controller_type' Obsolete. 如果指定该选项,则忽略此参数。If specified, this parameter is ignored. 支持它完全是为了向后兼容。It is supported purely for backward compatibility. 新的使用sp_addumpdevice应省略此参数。New uses of sp_addumpdevice should omit this parameter.

[ @devstatus = ] 'device_status' 已过时。[ @devstatus = ] 'device_status' Obsolete. 如果指定该选项,则忽略此参数。If specified, this parameter is ignored. 支持它完全是为了向后兼容。It is supported purely for backward compatibility. 新的使用sp_addumpdevice应省略此参数。New uses of sp_addumpdevice should omit this parameter.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

结果集Result Sets



sp_addumpdevice添加到备份设备sys.backup_devices目录视图。sp_addumpdevice adds a backup device to the sys.backup_devices catalog view. 然后便可以在 BACKUP 和 RESTORE 语句中逻辑引用该设备。The device can then be referred to logically in BACKUP and RESTORE statements. sp_addumpdevice不会执行对物理设备的任何访问。sp_addumpdevice does not perform any access to the physical device. 只有在执行 BACKUP 或 RESTORE 语句后才会访问指定的设备。Access to the specified device only occurs when a BACKUP or RESTORE statement is performed. 创建一个逻辑备份设备可简化 BACKUP 和 RESTORE 语句,在这种情况下指定设备名称将代替使用 "TAPE =" 或 "DISK =" 子句指定设备路径。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. 请确保已将相应的文件权限授予用于启动数据库引擎Database Engine的 Windows 帐户。Make sure that appropriate file permissions are given to the Windows account under which the 数据库引擎Database Engine was started.

数据库引擎Database Engine支持将磁带备份到 Windows 支持的磁带设备上。The 数据库引擎Database Engine supports tape backups to tape devices that are supported by Windows. 有关 Windows 支持的磁带设备的详细信息,请参阅 Windows 的硬件兼容性列表。For more information about Windows-supported tape devices, see the hardware compatibility list for Windows. 若要查看计算机上可用的磁带设备,请使用 SQL Server Management StudioSQL Server Management StudioTo view the tape devices available on the computer, use SQL Server Management StudioSQL Server Management Studio.

对于特定的磁带机,请仅使用驱动器厂商建议的推荐磁带。Use only the recommended tapes for the specific tape drive that are suggested by the drive manufacturer. 如果您使用的是数字音频磁带 (DAT) 驱动器,请使用计算机级的 DAT 磁带(数字数据存储 (DDS))。If you are using digital audio tape (DAT) drives, use computer-grade DAT tapes (Digital Data Storage (DDS)).

sp_addumpdevice不能在事务内执行。sp_addumpdevice cannot be executed inside a transaction.

若要删除某个设备,请使用sp_dropdeviceSQL Server Management StudioTo delete a device, use sp_dropdevice orSQL Server Management Studio.


要求具有 diskadmin 固定服务器角色中的成员身份。Requires membership in the diskadmin fixed server role.

要求拥有写入磁盘的权限。Requires permission to write to the disk.


A.A. 添加磁盘转储设备Adding a disk dump device

下面的示例添加了一个名为 mydiskdump 的磁盘备份设备,其物理名称为 c:\dump\dump1.bakThe following 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.B. 添加网络磁盘备份设备Adding a network disk backup device

下面的示例显示了添加名为 networkdevice 的远程磁盘备份设备的过程。The following example shows adding a remote disk backup device called networkdevice. 用于启动数据库引擎Database Engine的名称必须对该远程文件 (\\<servername>\<sharename>\<path>\<filename>.bak) 拥有权限。The name under which the 数据库引擎Database Engine was started must have permissions to that remote file (\\<servername>\<sharename>\<path>\<filename>.bak).

USE master;  
EXEC sp_addumpdevice 'disk', 'networkdevice',  

C.C. 添加磁带备份设备Adding a tape backup device

下面的示例添加物理名称为 tapedump1\\.\tape0 设备。The following example adds the tapedump1 device with the physical name \\.\tape0.

USE master;  
EXEC sp_addumpdevice 'tape', 'tapedump1', '\\.\tape0';  

D.D. 备份到逻辑备份设备Backing up to a logical backup device

以下示例为某备份磁盘文件创建了名为 AdvWorksData 的逻辑备份设备。The following example creates a logical backup device, AdvWorksData, for a backup disk file. 该示例随后会将 AdventureWorks2012AdventureWorks2012 数据库备份到此逻辑备份设备。The example then backs up the AdventureWorks2012AdventureWorks2012 database to this logical backup device.

USE master;  
EXEC sp_addumpdevice 'disk', 'AdvWorksData',   
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\BACKUP\AdvWorksData.bak';  
BACKUP DATABASE AdventureWorks2012   
 TO AdvWorksData  

请参阅See Also

备份设备 (SQL Server) Backup Devices (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
为磁盘文件定义逻辑备份设备 (SQL Server) Define a Logical Backup Device for a Disk File (SQL Server)
为磁带驱动器定义逻辑备份设备 (SQL Server) Define a Logical Backup Device for a Tape Drive (SQL Server)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
sp_dropdevice (Transact-SQL) sp_dropdevice (Transact-SQL)
sys.backup_devices (Transact-SQL) sys.backup_devices (Transact-SQL)
系统存储过程 (Transact-SQL)System Stored Procedures (Transact-SQL)