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

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

適用於SQL ServerSQL Server (SQL 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. device_typeVarchar (20),沒有預設值,而且可以是下列其中一個值。device_type is varchar(20), with no default, and can be one of the following values.

Value 描述Description
硬碟disk 做為備份裝置的硬碟檔。Hard disk file as a backup device.
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 EngineDatabase Engine 的名稱有遠端電腦的適當寫入功能。When creating a backup device on a remote network location, be sure that the name under which the Database EngineDatabase 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會將備份裝置新增至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 EngineDatabase Engine 的 Windows 帳戶已取得適當的檔案權限。Make sure that appropriate file permissions are given to the Windows account under which the Database EngineDatabase Engine was started.

[!INCLUDE[ssDE](../../includes/ssde-md.md)] 支援利用 Windows 支援的磁帶裝置來進行磁帶備份。The [!INCLUDE[ssDE](../../includes/ssde-md.md)] 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. 若要檢視電腦中所能使用的磁帶裝置,請使用 [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)]。To view the tape devices available on the computer, use [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)]. 請只用磁帶機製造商所建議的特定磁帶機專用磁帶。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_dropdevice](../../relational-databases/system-stored-procedures/sp-dropdevice-transact-sql.md)或[SQL Server Management Studio](../../relational-databases/backup-restore/delete-a-backup-device-sql-server.md)。To delete a device, use [sp_dropdevice](../../relational-databases/system-stored-procedures/sp-dropdevice-transact-sql.md) or[SQL Server Management Studio](../../relational-databases/backup-restore/delete-a-backup-device-sql-server.md). ## 權限Permissions 需要 **diskadmin** 固定伺服器角色的成員資格。Requires membership in the **diskadmin** fixed server role. 需要寫入磁碟的權限。Requires permission to write to the disk. ## 範例Examples ### A.A. 加入磁碟傾印裝置Adding a disk dump device 下列範例會加入名稱為 `mydiskdump` 的磁碟備份裝置,實體名稱是 `c:\dump\dump1.bak`。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'; ``` ### B.B. 加入網路磁碟備份裝置Adding a network disk backup device 下列範例會顯示如何加入稱為 `networkdevice` 的遠端磁碟備份裝置。The following example shows adding a remote disk backup device called `networkdevice`. 用來啟動 [!INCLUDE[ssDE](../../includes/ssde-md.md)] 的名稱必須有該遠端檔案 (`\\\\\.bak`) 的權限。The name under which the [!INCLUDE[ssDE](../../includes/ssde-md.md)] was started must have permissions to that remote file (`\\\\\.bak`). ``` USE master; GO EXEC sp_addumpdevice 'disk', 'networkdevice', '\\\\\.bak'; ``` ### C.C. 加入磁帶備份裝置Adding a tape backup device 下列範例會加入實體名稱為 `tapedump1` 的 `\\.\tape0` 裝置。The following example adds the `tapedump1` device with the physical name `\\.\tape0`. ``` USE master; GO 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. 接著,這個範例會將 [!INCLUDE[ssSampleDBobject](../../includes/sssampledbobject-md.md)] 資料庫備份至這個邏輯備份裝置。The example then backs up the [!INCLUDE[ssSampleDBobject](../../includes/sssampledbobject-md.md)] 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 AdventureWorks2012 TO AdvWorksData WITH FORMAT; GO ``` ## 另請參閱See Also [備份裝置 (SQL Server)](../../relational-databases/backup-restore/backup-devices-sql-server.md) [Backup Devices (SQL Server)](../../relational-databases/backup-restore/backup-devices-sql-server.md) [BACKUP (Transact-SQL)](../../t-sql/statements/backup-transact-sql.md) [BACKUP (Transact-SQL)](../../t-sql/statements/backup-transact-sql.md) [定義磁碟檔案的邏輯備份裝置 (SQL Server)](../../relational-databases/backup-restore/define-a-logical-backup-device-for-a-disk-file-sql-server.md) [Define a Logical Backup Device for a Disk File (SQL Server)](../../relational-databases/backup-restore/define-a-logical-backup-device-for-a-disk-file-sql-server.md) [定義磁帶機的邏輯備份裝置 (SQL Server)](../../relational-databases/backup-restore/define-a-logical-backup-device-for-a-tape-drive-sql-server.md) [Define a Logical Backup Device for a Tape Drive (SQL Server)](../../relational-databases/backup-restore/define-a-logical-backup-device-for-a-tape-drive-sql-server.md) [RESTORE (Transact-SQL)](../../t-sql/statements/restore-statements-transact-sql.md) [RESTORE (Transact-SQL)](../../t-sql/statements/restore-statements-transact-sql.md) [sp_dropdevice (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-dropdevice-transact-sql.md) [sp_dropdevice (Transact-SQL)](../../relational-databases/system-stored-procedures/sp-dropdevice-transact-sql.md) [sys.backup_devices (Transact-SQL)](../../relational-databases/system-catalog-views/sys-backup-devices-transact-sql.md) [sys.backup_devices (Transact-SQL)](../../relational-databases/system-catalog-views/sys-backup-devices-transact-sql.md) [系統預存程序 (Transact-SQL)System Stored Procedures (Transact-SQL)](../../relational-databases/system-stored-procedures/system-stored-procedures-transact-sql.md)