從裝置還原備份 (SQL Server)Restore a Backup from a Device (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 restore a backup from a device in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

注意

如需將 SQL Server 備份放至 Azure Blob 儲存體服務的相關資訊,請參閱使用 Microsoft Azure Blob 儲存體服務進行 SQL Server 備份及還原For information on SQL Server backup to the Azure Blob storage service, see, SQL Server Backup and Restore with Microsoft Azure Blob Storage Service.

本主題內容In This Topic

開始之前Before You Begin

安全性Security

權限Permissions

如果還原的資料庫不存在,使用者必須有 CREATE DATABASE 權限,才能執行 RESTORE。If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. 如果資料庫存在,RESTORE 權限預設為 系統管理員 (sysadmin)資料庫建立者 (dbcreator) 固定伺服器角色的成員以及資料庫的擁有者 (dbo) (對 FROM DATABASE_SNAPSHOT 選項而言,資料庫一律存在)。If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).

RESTORE 權限提供給伺服器隨時可以取得其成員資格資訊的角色。RESTORE permissions are given to roles in which membership information is always readily available to the server. 由於資料庫必須是可存取且未損毀,才能夠檢查固定資料庫角色成員資格,但執行 RESTORE 時未必如此;因此, db_owner 固定資料庫角色的成員並沒有 RESTORE 權限。Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

使用 SQL Server Management StudioUsing SQL Server Management Studio

若要從裝置還原備份To restore a backup from a device

  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 Restore.

  4. 按一下您想要的還原作業類型 ([資料庫]、[檔案和檔案群組] 或 [交易記錄檔])。Click the type of restore operation you want (Database, Files and Filegroups, or Transaction Log). 這會開啟對應的還原對話方塊。This opens the corresponding restore dialog box.

  5. [一般] 頁面的 [還原來源] 區段中,按一下 [來源裝置]On the General page, in the Restore source section, click From device.

  6. 按一下 [來源裝置] 文字方塊的瀏覽按鈕,這會開啟 [指定備份] 對話方塊。Click the browse button for the From device text box, which opens the Specify Backup dialog box.

  7. [備份媒體] 文字方塊中,選取 [備份裝置],然後按一下 [加入] 按鈕,以開啟 [選取備份裝置] 對話方塊。In the Backup media text box, select Backup Device, and click the Add button to open the Select Backup Device dialog box.

  8. [備份裝置] 文字方塊中,選取您要用於還原作業的裝置。In the Backup device text box, select the device you want to use for the restore operation.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一個意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

使用 Transact-SQLUsing Transact-SQL

若要從裝置還原備份To restore a backup from a device

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

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

  3. RESTORE 陳述式中,指定備份作業要用的邏輯或實體備份裝置。In the RESTORE statement, specify a logical or physical backup device to use for the backup operation. 這個範例會從實體名稱為 Z:\SQLServerBackups\AdventureWorks2012.bak的磁碟檔案還原。This example restores from a disk file that has the physical name Z:\SQLServerBackups\AdventureWorks2012.bak.

RESTORE DATABASE AdventureWorks2012  
   FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak' ;  
  

另請參閱See Also

RESTORE FILELISTONLY (Transact-SQL) RESTORE FILELISTONLY (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL) RESTORE HEADERONLY (Transact-SQL)
RESTORE LABELONLY (Transact-SQL) RESTORE LABELONLY (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL) RESTORE VERIFYONLY (Transact-SQL)
在簡單復原模式下還原資料庫備份 (Transact-SQL) Restore a Database Backup Under the Simple Recovery Model (Transact-SQL)
Restore a Database Backup Using SSMS Restore a Database Backup Using SSMS
還原差異資料庫備份 (SQL Server) Restore a Differential Database Backup (SQL Server)
將資料庫還原到新位置 (SQL Server) Restore a Database to a New Location (SQL Server)
備份檔案和檔案群組 (SQL Server) Back Up Files and Filegroups (SQL Server)
備份交易記錄 (SQL Server) Back Up a Transaction Log (SQL Server)
建立差異資料庫備份 (SQL Server)Create a Differential Database Backup (SQL Server)