以覆蓋現有檔案的方式還原檔案與檔案群組 (SQL Server)Restore Files and Filegroups over Existing Files (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 over existing files 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 who is 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 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 over existing files

  1. 在 [物件總管] 中,連接到 SQL Server Database EngineSQL Server Database Engine的執行個體,展開該執行個體,然後展開 [資料庫] 。In Object Explorer, connect to an instance of the SQL Server Database EngineSQL Server Database Engine, expand that instance, and then expand Databases.

  2. 以滑鼠右鍵按一下所要的資料庫,依序指向 [工作] 與 [還原] ,然後按一下 [檔案與檔案群組] 。Right-click the database that you want, point to Tasks, point to Restore, and then click Files and Filegroups.

  3. [一般] 頁面上的 [目的地資料庫] 清單方塊,輸入要還原的資料庫。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.

  4. 若要指定要還原之備份組的來源與位置,請按一下下列任一個選項: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.

  5. [選取要還原的備份組] 方格中,選取要還原的備份。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.
  6. [選取頁面] 窗格中,按一下 [選項] 頁面。In the Select a page pane, click the Options page.

  7. 在 [還原選項] 面板中,選取 [覆寫現有的資料庫 (WITH REPLACE)] 。In the Restore options panel, select Overwrite the existing database (WITH REPLACE). 還原作業會覆寫任何現有的資料庫及其相關檔案,即使已經有另一個資料庫或檔案具有相同名稱也一樣。The restore operation overwrites any existing databases and their related files, even if another database or file already exists with the same name.

  8. 按一下 [確定] 。Click OK.

使用 Transact-SQLUsing Transact-SQL

若要以覆蓋現有檔案的方式還原檔案與檔案群組To restore files and filegroups over existing files

  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.

    • REPLACE 選項,以指明可以用覆蓋相同名稱、相同位置之現有檔案的方式來還原每一個檔案。The REPLACE option to specify that each file can be restored over existing files of the same name and location.

      警告

      請小心使用 REPLACE 選項。Use the REPLACE option cautiously. 若需相關資訊,請參閱 。For more information, see .

    • NORECOVERY 選項。The NORECOVERY option. 如果檔案在備份建立之後沒有做過任何修改,請指定 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.

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

下列範例還原 MyNwind 資料庫的檔案和檔案群組,並取代任何相同名稱的現有檔案。The following example restores the files and filegroups for the MyNwind database, and replaces any existing files of the same name. 同時還套用了兩份交易記錄,以便將資料庫還原到目前的時間。Two transaction logs will also be applied to restore the database to the current time.

USE master;  
GO  
-- Restore the files and filesgroups for MyNwind.  
RESTORE DATABASE MyNwind  
   FILE = 'MyNwind_data_1',  
   FILEGROUP = 'new_customers',  
   FILE = 'MyNwind_data_2',  
   FILEGROUP = 'first_qtr_sales'  
   FROM MyNwind_1  
   WITH NORECOVERY,  
   REPLACE;  
GO  
-- Apply the first transaction log backup.  
RESTORE LOG MyNwind  
   FROM MyNwind_log1  
   WITH NORECOVERY;  
GO  
-- Apply the last transaction log backup.  
RESTORE LOG MyNwind  
   FROM MyNwind_log2  
   WITH RECOVERY;  
GO  

另請參閱See Also

Restore a Database Backup Using SSMS Restore a Database Backup Using SSMS
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
還原檔案和檔案群組 (SQL Server) Restore Files and Filegroups (SQL Server)
使用備份與還原複製資料庫Copy Databases with Backup and Restore