還原檔案和檔案群組 (SQL Server)Restore Files and Filegroups (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 files and filegroups in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

  • 負責還原檔案與檔案群組備份的系統管理員,必須是目前唯一正在使用即將還原之資料庫的人。The system administrator restoring the files and filegroups must be the only person currently using the database to be restored.

  • 在明確或隱含的交易中,不允許使用 RESTORE。RESTORE is not allowed in an explicit or implicit transaction.

  • 在簡單復原模式之下,檔案必須屬於唯讀檔案群組。Under the simple recovery model, the file must belong to a read-only filegroup.

  • 在完整或大量記錄復原模式下,您必須先備份使用中的交易記錄 (也稱為記錄的結尾),才能還原檔案。Under the full or bulk-logged recovery model, before you can restore files, you must back up the active transaction log (known as the tail of the log). 如需詳細資訊,請參閱 備份交易記錄 (SQL Server)中還原檔案與檔案群組。For more information, see Back Up a Transaction Log (SQL Server).

  • 若要還原加密的資料庫,您必須能夠存取之前用來加密資料庫的憑證或非對稱金鑰。To restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used to encrypt the database. 如果沒有該憑證或非對稱金鑰,就無法還原資料庫。Without the certificate or asymmetric key, the database cannot be restored. 因此,只要需要備份,就必須保留用來加密資料庫加密金鑰的憑證。As a result, the certificate that is used to encrypt the database encryption key must be retained as long as the backup is needed. 如需詳細資訊,請參閱 SQL Server Certificates and Asymmetric KeysFor more information, see SQL Server Certificates and Asymmetric Keys.

安全性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 files and filegroups

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

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

  3. 以滑鼠右鍵按一下資料庫,指向 [工作] ,然後按一下 [還原] 。Right-click the database, point to Tasks, and then click Restore.

  4. 按一下 [檔案和檔案群組] ,開啟 [還原檔案和檔案群組] 對話方塊。Click Files and Filegroups, which opens the Restore Files and Filegroups dialog box.

  5. [一般] 頁面上的 [目的地資料庫] 清單方塊,輸入要還原的資料庫。On the General page, in the To database list box, enter the database to restore. 您可以輸入新的資料庫,或者從下拉式清單中選擇現有的資料庫。You can enter a new database or choose an existing database from the drop-down list. 清單包含伺服器上的所有資料庫,排除系統資料庫 mastertempdbThe list includes all databases on the server, excluding the system databases master and tempdb.

  6. 若要指定要還原之備份組的來源與位置,請按一下下列任一個選項:To specify the source and location of the backup sets to restore, click one of the following options:

    • 來源資料庫From database

      在清單方塊中輸入資料庫名稱。Enter a database name in the list box. 此清單僅包含已根據 msdb 備份記錄而備份的資料庫。This list contains only databases that have been backed up according to the msdb backup history.

    • 來源裝置From device

      按一下 [瀏覽] 按鈕。Click the browse button. [指定備份裝置] 對話方塊中,選取 [備份媒體類型] 清單方塊上列出的其中一個裝置類型。In the Specify backup devices dialog box, select one of the listed device types in the Backup media type list box. 若要選取 [備份媒體] 清單方塊的一個或多個裝置,請按一下 [加入]To select one or more devices for the Backup media list box, click Add.

      將您要的裝置加入 [備份媒體] 清單方塊後,按一下 [確定] 即可回到 [一般] 頁面。After you add the devices you want to the Backup media list box, click OK to return to the General page.

  7. [選取要還原的備份組] 方格中,選取要還原的備份。In the Select the backup sets to restore grid, select the backups to restore. 這個方格會顯示指定位置可用的備份。This grid displays the backups available for the specified location. 依預設,會建議一個復原計畫。By default, a recovery plan is suggested. 若要覆寫建議的復原計畫,您可以變更方格中的選取項目。To override the suggested recovery plan, you can change the selections in the grid. 任何相依於已取消選取備份的備份,會自動被取消選取。Any backups that depend on a deselected backup are deselected automatically.

    資料行標頭Column head Values
    RestoreRestore 選取的核取方塊會指出要還原的備份組。The selected check boxes indicate the backup sets to be restored.
    名稱Name 備份組的名稱。The name of the backup set.
    檔案類型File Type 指定備份中的資料類型:[資料] 、[記錄] 或 [Filestream 資料] 。Specifies the type of data in the backup: Data, Log, or Filestream Data. 資料表中所包含的資料位於 [資料] 檔案中。Data that is contained in tables is in Data files. 交易記錄資料位於 [記錄檔] 中。Transaction log data is in Log files. 儲存在檔案系統上的二進位大型物件 (BLOB) 資料位於 [Filestream 資料] 檔案中。Binary large object (BLOB) data that is stored on the file system is in Filestream Data files.
    型別Type 執行的備份類型:[完整] 、[差異] 或 [交易記錄] 。The type of backup performed: Full, Differential, or Transaction Log.
    ServerServer 執行備份作業的 Database Engine 執行個體名稱。The name of the Database-Engine instance that performed the backup operation.
    檔案邏輯名稱File Logical Name 檔案的邏輯名稱。The logical name of the file.
    [資料庫備份]Database 備份作業中所含的資料庫名稱。The name of the database involved in the backup operation.
    開始日期Start Date 備份作業開始時的日期和時間,會出現在用戶端的地區設定中。The date and time when the backup operation began, presented in the regional setting of the client.
    完成日期Finish Date 備份作業完成時的日期和時間,會出現在用戶端的地區設定中。The date and time when the backup operation finished, presented in the regional setting of the client.
    大小Size 備份組的大小 (以位元組為單位)。The size of the backup set in bytes.
    使用者名稱User Name 執行備份作業的使用者名稱。The name of the user who performed the backup operation.
  8. 若要檢視或選取進階選項,請按一下 [選取頁面] 窗格中的 [選項]To view or select the advanced options, click Options in the Select a page pane.

  9. [還原選項] 面板中,您可以選擇下列任何選項 (如果情況適用)。In the Restore options panel, you can choose any of the following options, if appropriate for your situation.

    還原成檔案群組Restore as filegroup
    指出正在還原整個檔案群組。Indicates that an entire filegroup is being restored.

    覆寫現有的資料庫Overwrite the existing database
    指定還原作業應該覆寫任何現有的資料庫及其相關檔案,即使已經有另一個資料庫或檔案具有相同名稱也一樣。Specifies that the restore operation should overwrite any existing databases and their related files, even if another database or file already exists with the same name.

    選取此選項相當於使用 Transact-SQLTransact-SQL RESTORE 陳述式的 REPLACE 選項。Selecting this option is equivalent to using the REPLACE option in a Transact-SQLTransact-SQL RESTORE statement.

    還原每個備份之前先提示Prompt before restoring each backup
    在將每個備份組還原之前,會要求您確認。Asks you for confirmation before restoring each backup set.

    您必須為不同的媒體集交換磁帶時,這個選項特別有用,例如當伺服器只有一個磁帶機時。This option is particularly useful where you must swap tapes for different media sets, such as when the server has one tape device.

    限制對還原資料庫的存取Restrict access to the restored database
    僅有 db_ownerdbcreator系統管理員的成員可以使用還原資料庫。Makes the restored database available only to the members of db_owner, dbcreator, or sysadmin.

    選取此選項相當於使用 Transact-SQLTransact-SQL RESTORE 陳述式的 RESTRICTED_USER 選項。Selecting this option is synonymous to using the RESTRICTED_USER option in a Transact-SQLTransact-SQL RESTORE statement.

  10. 另外,您也可以在 [將資料庫檔案還原為] 方格中為每個檔案指定新的還原目的地,以將資料庫還原到新的位置。Optionally, you can restore the database to a new location by specifying a new restore destination for each file in the Restore database files as grid.

    資料行標頭Column head Values
    原始檔案名稱Original File Name 來源備份檔案的完整路徑。The full path of a source backup file.
    檔案類型File Type 指定備份中的資料類型:[資料] 、[記錄] 或 [Filestream 資料] 。Specifies the type of data in the backup: Data, Log, or Filestream Data. 資料表中所包含的資料位於 [資料] 檔案中。Data that is contained in tables is in Data files. 交易記錄資料位於 [記錄檔] 中。Transaction log data is in Log files. 儲存在檔案系統上的二進位大型物件 (BLOB) 資料位於 [Filestream 資料] 檔案中。Binary large object (BLOB) data that is stored on the file system is in Filestream Data files.
    還原成Restore As 還原資料庫檔案的完整路徑。The full path of the database file to be restored. 若要指定新的還原檔案,請按一下文字方塊並編輯建議的路徑與檔案名稱。To specify a new restore file, click the text box and edit the suggested path and file name. 變更 [還原成] 資料行中的路徑或檔案名稱相當於使用 Transact-SQLTransact-SQL RESTORE 陳述式中的 MOVE 選項。Changing the path or file name in the Restore As column is equivalent to using the MOVE option in a Transact-SQLTransact-SQL RESTORE statement.
  11. [復原狀態] 面板可決定資料庫在還原作業之後的狀態。The Recovery state panel determines the state of the database after 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.

回復未認可的交易,讓資料庫保持備妥可用。無法還原其他交易記錄。(RESTORE WITH RECOVERY)Leave the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)
復原資料庫。Recovers the database. 這是預設行為。This is the default behavior. 只有在您要立即還原所有必要的備份時,才選擇這個選項。Choose this option only if you are restoring all of the necessary backups now. 此選項相當於在 Transact-SQLTransact-SQL RESTORE 陳述式中指定 WITH RECOVERY。This option is equivalent to specifying WITH RECOVERY in a Transact-SQLTransact-SQL RESTORE statement.

讓資料庫保持不運作,且不回復未認可的交易。可以還原其他交易記錄。(RESTORE WITH NORECOVERY)Leave the database non-operational, and don't roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)
讓資料庫保持在還原狀態。Leaves the database in the restoring state. 若要復原資料庫,就必須使用先前的 RESTORE WITH RECOVERY 選項 (請參閱上面說明) 執行另一個還原。To recover the database, you will need to perform another restore using the preceding RESTORE WITH RECOVERY option (see above). 此選項相當於在 Transact-SQLTransact-SQL RESTORE 陳述式中指定 WITH NORECOVERY。This option is equivalent to specifying WITH NORECOVERY in a Transact-SQLTransact-SQL RESTORE statement.

如果選取此選項,將無法使用 [保留複寫設定] 選項。If you select this option, the Preserve replication settings option is unavailable.

讓資料庫保持唯讀模式。回復未認可的交易,但是將回復作業儲存在檔案中,以便能夠恢復復原結果。(RESTORE WITH STANDBY)Leave the database in read-only mode. Roll back the uncommitted transactions, but save the rollback operation in a file so the recovery effects can be undone. (RESTORE WITH STANDBY)
讓資料庫處於待命狀態。Leaves the database in a standby state. 此選項相當於在 Transact-SQLTransact-SQL RESTORE 陳述式中指定 WITH STANDBY。This option is equivalent to specifying WITH STANDBY in a Transact-SQLTransact-SQL RESTORE statement.

您必須指定待命資料庫檔案,才能選擇此選項。Choosing this option requires that you specify a standby file.

回復恢復檔案Rollback undo file
在 [回復恢復檔案] 文字方塊中,指定待命資料庫檔案名稱。Specify a standby file name in the Rollback undo file text box. 如果您讓資料庫保持唯讀模式 (RESTORE WITH STANDBY),就需要指定此選項。This option is required if you leave the database in read-only mode (RESTORE WITH STANDBY).

使用 Transact-SQLUsing Transact-SQL

還原檔案和檔案群組To restore files and filegroups

  1. 執行 RESTORE DATABASE 陳述式以還原檔案及檔案群組備份,並指定以下項目:Execute the RESTORE DATABASE statement to restore the file and filegroup backup, specifying:

    • 所要還原的資料庫名稱。The name of the database to restore.

    • 將要還原完整資料庫備份的來源備份裝置。The backup device from where the full database backup will be restored.

    • 替要還原的每個檔案指定 FILE 子句。The FILE clause for each file to restore.

    • 替要還原的每個檔案群組指定 FILEGROUP 子句。The FILEGROUP clause for each filegroup to restore.

    • NORECOVERY 子句。The NORECOVERY clause. 如果檔案在備份建立之後沒有做過任何修改,請指定 RECOVERY 子句。If the files have not been modified after the backup was created, specify the RECOVERY clause.

  2. 如果檔案在備份建立之後做過修改,則請執行 RESTORE LOG 陳述式以套用交易記錄備份,並指定下列項目:If the files have been modified after the file backup was created, execute the RESTORE LOG statement to apply the transaction log backup, specifying:

    • 交易記錄檔要套用的資料庫名稱。The name of the database to which the transaction log will be applied.

    • 用於還原交易記錄備份的備份裝置。The backup device from where the transaction log backup will be restored.

    • 倘若在目前的交易記錄備份之後還有另一個交易記錄備份要套用,請指定 NORECOVERY 子句,否則請指定 RECOVERY 子句。The NORECOVERY clause if you have another transaction log backup to apply after the current one; otherwise, specify the RECOVERY clause.

      如果套用交易記錄備份,則交易記錄備份必須涵蓋備份檔案與檔案群組直到記錄結束的時間 (除非還原「所有的」資料庫檔案)。The transaction log backups, if applied, must cover the time when the files and filegroups were backed up until the end of log (unless ALL database files are restored).

範例 (Transact-SQL)Example (Transact-SQL)

這個範例會還原 MyDatabase 資料庫的檔案和檔案群組。This example restores the files and filegroups for the MyDatabase database. 為了將資料庫還原到目前的時間,還套用了兩份交易記錄。To restore the database to the current time, two transaction logs are applied.

USE master;  
GO  
-- Restore the files and filesgroups for MyDatabase.  
RESTORE DATABASE MyDatabase  
   FILE = 'MyDatabase_data_1',  
   FILEGROUP = 'new_customers',  
   FILE = 'MyDatabase_data_2',  
   FILEGROUP = 'first_qtr_sales'  
   FROM MyDatabase_1  
   WITH NORECOVERY;  
GO  
-- Apply the first transaction log backup.  
RESTORE LOG MyDatabase  
   FROM MyDatabase_log1  
   WITH NORECOVERY;  
GO  
-- Apply the last transaction log backup.  
RESTORE LOG MyDatabase  
   FROM MyDatabase_log2  
   WITH RECOVERY;  
GO  

另請參閱See Also

Restore a Database Backup Using SSMS Restore a Database Backup Using SSMS
備份檔案和檔案群組 (SQL Server) Back Up Files and Filegroups (SQL Server)
建立完整資料庫備份 (SQL Server) Create a Full Database Backup (SQL Server)
備份交易記錄 (SQL Server) Back Up a Transaction Log (SQL Server)
還原交易記錄備份 (SQL Server) Restore a Transaction Log Backup (SQL Server)
RESTORE (Transact-SQL)RESTORE (Transact-SQL)