指定磁碟或磁帶做為備份目的地 (SQL Server)Specify a Disk or Tape As a Backup Destination (SQL Server)

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

此主題描述如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management Studio ,在 Transact-SQLTransact-SQL中指定磁碟或磁帶做為備份目的地。This topic describes how to specify a disk or tape as a backup destination in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

注意

未來的 SQL Server 版本中將會移除磁帶備份裝置的支援。Support for tape backup devices will be removed in a future version of SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

本主題內容In This Topic

開始之前Before You Begin

安全性Security

權限Permissions

BACKUP DATABASE 和 BACKUP LOG 權限預設為 sysadmin 固定伺服器角色以及 db_ownerdb_backupoperator 固定資料庫角色的成員。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

備份裝置實體檔案的擁有權和權限問題可能會干擾備份作業。Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server 必須能夠讀取和寫入裝置;執行 SQL ServerSQL Server 服務的帳戶必須具備寫入權限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 不過,在系統資料表中加入備份裝置項目的 sp_addumpdevice並不會檢查檔案存取權限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 當您嘗試備份或還原時,存取實體資源之前不一定會出現備份裝置實體檔案的這些問題。Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

使用 SQL Server Management StudioUsing SQL Server Management Studio

若要指定磁碟或磁帶做為備份目的地To specify a disk or tape as a backup destination

  1. 連接到適當的 MicrosoftMicrosoft SQL Server Database EngineSQL Server Database Engine執行個體之後,在 [物件總管] 中按一下伺服器名稱展開伺服器樹狀目錄。After connecting to the appropriate instance of the MicrosoftMicrosoft SQL Server Database EngineSQL Server Database Engine, in Object Explorer, click the server name to expand the server tree.

  2. 展開 [資料庫],然後視資料庫而定,選取使用者資料庫,或者展開 [系統資料庫] 並選取一個系統資料庫。Expand Databases, and, depending on the database, either select a user database or expand System Databases and select a system database.

  3. 以滑鼠右鍵按一下資料庫,指向 [工作],然後按一下 [備份]Right-click the database, point to Tasks, and then click Back Up. 會出現 [備份資料庫] 對話方塊。The Back Up Database dialog box appears.

  4. [一般] 頁面的 [目的地] 區段中,按一下 [磁碟][磁帶]In the Destination section of the General page, click Disk or Tape. 若要選取包含單一媒體集的磁碟或磁帶機 (最多 64 個) 的路徑,請按一下 [加入]To select the paths of up to 64 disk or tape drives containing a single media set, click Add.

若要移除備份目的地,請選取目的地,然後按一下 [移除]To remove a backup destination, select it and click Remove. 若要檢視備份目的地的內容,請選取目的地,然後按一下 [內容]To view the contents of a backup destination, select it and click Contents.

使用 Transact-SQLUsing Transact-SQL

若要指定磁碟或磁帶做為備份目的地To specify a disk or tape as a backup destination

  1. 連接到 Database EngineDatabase EngineConnect to the Database EngineDatabase Engine.

  2. 在標準列中,按一下 [新增查詢]From the Standard bar, click New Query.

  3. BACKUP 陳述式中,指定檔案或裝置及其實體名稱。In the BACKUP statement, specify the file or device and its physical name. 這個範例會將 AdventureWorks2012 資料庫備份到磁碟檔案 Z:\SQLServerBackups\AdventureWorks2012.BakThis example backs up the AdventureWorks2012 database to the disk file Z:\SQLServerBackups\AdventureWorks2012.Bak.

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'  
GO  

另請參閱See Also

備份交易記錄 (SQL Server) Back Up a Transaction Log (SQL Server)
備份檔案和檔案群組 (SQL Server) Back Up Files and Filegroups (SQL Server)
定義磁碟檔案的邏輯備份裝置 (SQL Server) Define a Logical Backup Device for a Disk File (SQL Server)
建立差異資料庫備份 (SQL Server) Create a Differential Database Backup (SQL Server)
定義磁帶機的邏輯備份裝置 (SQL Server)Define a Logical Backup Device for a Tape Drive (SQL Server)