還原差異資料庫備份 (SQL Server)Restore a Differential Database Backup (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 differential database backup 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

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

  • 在舊版 SQL ServerSQL Server 中,無法還原較新的 SQL ServerSQL Server版本所建立的備份。Backups that are created by more recent version of SQL ServerSQL Server cannot be restored in earlier versions of SQL ServerSQL Server.

  • SQL Server 2017SQL Server 2017中,您可以從使用 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 或更新版本所建立的資料庫備份還原使用者資料庫。In SQL Server 2017SQL Server 2017, you can restore a user database from a database backup that was created by using SQL Server 2005 (9.x)SQL Server 2005 (9.x) or a later version.

必要條件Prerequisites

  • 在完整或大量記錄還原模式下,您必須先備份使用中的交易記錄檔 (也稱為記錄檔的結尾),才能還原資料庫。Under the full or bulk-logged recovery model, before you can restore a database, 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).

安全性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 differential database backup

  1. 連接到適當的 MicrosoftMicrosoft SQL Server Database EngineSQL Server Database Engine執行個體之後,請在 [物件總管] 中按一下伺服器名稱以展開伺服器樹狀目錄。After you connect 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. 視資料庫而定,選取使用者資料庫,或者展開 [系統資料庫] ,再選取系統資料庫。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, point to Restore, and then click Database.

  4. [一般] 頁面上,使用 [來源] 區段指定要還原之備份組的來源和位置。On the General page, use the Source section to specify the source and location of the backup sets to restore. 選取下列其中一個選項:Select one of the following options:

    • [資料庫備份]Database

      從下拉式清單中選取要還原的資料庫。Select the database to restore from the drop-down list. 此清單僅包含已根據 msdb 備份記錄而備份的資料庫。The list contains only databases that have been backed up according to the msdb backup history.

    注意

    如果備份是根據不同的伺服器建立的,目的地伺服器就沒有指定之資料庫的備份記錄資訊。If the backup is taken from a different server, the destination server will not have the backup history information for the specified database. 在此情況下,請選取 [裝置] ,以便手動指定要還原的檔案或裝置。In this case, select Device to manually specify the file or device to restore.

    • [裝置]Device

      按一下瀏覽 ( ... ) 按鈕,開啟 [選取備份裝置] 對話方塊。Click the browse (...) button to open the Select backup devices dialog box. [備份媒體類型] 方塊中,選取列出的其中一種裝置類型。In the Backup media type box, select one of the listed device types. 若要選取 [備份媒體] 方塊中的一個或多個裝置,請按一下 [加入]To select one or more devices for the Backup media box, click Add.

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

      [來源:裝置:資料庫] 清單方塊中,選取應該還原的資料庫名稱。In the Source: Device: Database list box, select the name of the database which should be restored.

      注意 :這份清單只能在選取 [裝置] 時使用。Note This list is only available when Device is selected. 只有在所選取裝置上有備份的資料庫才可供使用。Only databases that have backups on the selected device will be available.

  5. [目的地] 區段中,會將要還原之資料庫的名稱自動填入 [資料庫] 方塊。In the Destination section, the Database box is automatically populated with the name of the database to be restored. 若要變更資料庫的名稱,請在 [資料庫] 方塊中輸入新名稱。To change the name of the database, enter the new name in the Database box.

    注意

    若要在特定時間點停止還原,請按一下 [時間表] 存取 [備份時間表] 對話方塊。To stop the restore at a specific point in time, click Timeline to access the Backup Timeline dialog box. 如需在特定時間點停止資料庫還原的說明,請參閱將 SQL Server 資料庫還原至某個時間點 (完整復原模式)For help with stopping a database restore at a specific point in time, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

  6. [要還原的備份組] 方格中,透過您想要還原的差異備份選取備份。In the Backup sets to restore grid, select the backups through the differential backup that you wish to restore.

    如需 [要還原的備份組] 方格中各資料行的相關資訊,請參閱還原資料庫 (一般頁面)For information about the columns in the Backup sets to restore grid, see Restore Database (General Page).

  7. [選項] 頁面的 [還原選項] 面板中,您可以選取下列任何選項 (如果情況適用):On the Options page, in the Restore options panel, you can select any of the following options, if appropriate for your situation:

    • 覆寫現有的資料庫 (WITH REPLACE)Overwrite the existing database (WITH REPLACE)

    • 保留複寫設定 (WITH KEEP_REPLICATION)Preserve the replication settings (WITH KEEP_REPLICATION)

    • 還原每個備份之前先提示Prompt before restoring each backup

    • 限制對還原資料庫的存取 (WITH RESTRICTED_USER)Restrict access to the restored database (WITH RESTRICTED_USER)

    如需這些選項的詳細資訊,請參閱還原資料庫 (選項頁面)For more information about these options, see Restore Database (Options Page).

  8. 針對 [還原狀態] 方塊,選取選項。Select an option for the Recovery state box. 此方塊決定資料庫在還原作業之後的狀態。This box determines the state of the database after the restore operation.

    • RESTORE WITH RECOVERY 是預設行為,透過回復未認可的交易,讓資料庫保持備妥可用。RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. 無法還原其他交易記錄。Additional transaction logs cannot be restored. 若您要立即還原所有必要的備份,請選取這個選項。Select this option if you are restoring all of the necessary backups now.

    • RESTORE WITH NORECOVERY ,讓資料庫保持不運作,且不回復未認可的交易。RESTORE WITH NORECOVERY which leaves the database non-operational, and does not roll back the uncommitted transactions. 可以還原其他交易記錄。Additional transaction logs can be restored. 資料庫在復原之前都無法使用。The database cannot be used until it is recovered.

    • RESTORE WITH STANDBY ,讓資料庫處於唯讀模式。RESTORE WITH STANDBY which leaves the database in read-only mode. 它會復原未認可的交易,但會將復原動作儲存在待命資料庫檔案中,以還原復原影響。It undoes uncommitted transactions, but saves the undo actions in a standby file so that recovery effects can be reverted.

    如需這些選項的描述,請參閱還原資料庫 (選項頁面)For descriptions of the options, see Restore Database (Options Page).

  9. 若資料庫有使用中的連接,還原作業會失敗。Restore operations will fail if there are active connections to the database. 核取 [關閉現有的連接選項] ,確定已關閉 Management StudioManagement Studio 與資料庫之間的所有使用中連接。Check the Close existing connections option to ensure that all active connections between Management StudioManagement Studio and the database are closed.

  10. 如果想要系統在每個還原作業之間提示您,請選取 [還原每個備份之前先提示]Select Prompt before restoring each backup if you wish to be prompted between each restore operation. 除非資料庫夠大,而且您想要監視還原作業的狀態,否則這通常不需要。This is not usually necessary unless the database is large and you wish to monitor the status of the restore operation.

  11. 或者,使用 [檔案] 頁面將資料庫還原到新位置。Optionally, use the Files page to restore the database to a new location. 如需移動資料庫的說明,請參閱將資料庫還原到新位置 (SQL Server)For help with moving a database, see Restore a Database to a New Location (SQL Server).

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

使用 Transact-SQLUsing Transact-SQL

還原差異資料庫備份To restore a differential database backup

  1. 執行 RESTORE DATABASE 陳述式並指定 NORECOVERY 子句,以還原在差異資料庫備份之前的完整資料庫備份。Execute the RESTORE DATABASE statement, specifying the NORECOVERY clause, to restore the full database backup that comes before the differential database backup. 如需詳細資訊,請參閱如何:還原完整備份For more information, see How to: Restore a Full Backup.

  2. 執行 RESTORE DATABASE 陳述式以還原差異資料庫備份,請指定:Execute the RESTORE DATABASE statement to restore the differential database backup, specifying:

    • 將套用差異資料庫備份的資料庫名稱。The name of the database to which the differential database backup is applied.

    • 將要還原差異資料庫備份的備份裝置。The backup device where the differential database backup is restored from.

    • 如果在還原差異資料庫備份之後,您有交易記錄備份可以套用,則指定 NORECOVERY 子句。The NORECOVERY clause if you have transaction log backups to apply after the differential database backup is restored. 否則,指定 RECOVERY 子句。Otherwise, specify the RECOVERY clause.

  3. 使用完整或大量記錄復原模式,還原差異資料庫備份可將資料庫還原到完成差異資料庫備份的時間點。With the full or bulk-logged recovery model, restoring a differential database backup restores the database to the point at which the differential database backup was completed. 若要復原到失敗點,您必須套用上次建立差異資料庫備份後所建立的所有交易記錄備份。To recover to the point of failure, you must apply all transaction log backups created after the last differential database backup was created. 如需詳細資訊,請參閱套用交易記錄備份 (SQL Server)For more information, see Apply Transaction Log Backups (SQL Server).

範例 (Transact-SQL)Examples (Transact-SQL)

A.A. 還原差異資料庫備份Restoring a differential database backup

這個範例還原 MyAdvWorks 資料庫與差異資料庫備份。This example restores a database and differential database backup of the MyAdvWorks database.

-- Assume the database is lost, and restore full database,   
-- specifying the original full database backup and NORECOVERY,   
-- which allows subsequent restore operations to proceed.  
RESTORE DATABASE MyAdvWorks  
   FROM MyAdvWorks_1  
   WITH NORECOVERY;  
GO  
-- Now restore the differential database backup, the second backup on   
-- the MyAdvWorks_1 backup device.  
RESTORE DATABASE MyAdvWorks  
   FROM MyAdvWorks_1  
   WITH FILE = 2,  
   RECOVERY;  
GO  

B.B. 還原資料庫、差異資料庫及交易記錄備份Restoring a database, differential database, and transaction log backup

這個範例還原 MyAdvWorks 資料庫、差異資料庫及其交易記錄備份。This example restores a database, differential database, and transaction log backup of the MyAdvWorks database.

-- Assume the database is lost at this point. Now restore the full   
-- database. Specify the original full database backup and NORECOVERY.  
-- NORECOVERY allows subsequent restore operations to proceed.  
RESTORE DATABASE MyAdvWorks  
   FROM MyAdvWorks_1  
   WITH NORECOVERY;  
GO  
-- Now restore the differential database backup, the second backup on   
-- the MyAdvWorks_1 backup device.  
RESTORE DATABASE MyAdvWorks  
   FROM MyAdvWorks_1  
   WITH FILE = 2,  
   NORECOVERY;  
GO  
-- Now restore each transaction log backup created after  
-- the differential database backup.  
RESTORE LOG MyAdvWorks  
   FROM MyAdvWorks_log1  
   WITH NORECOVERY;  
GO  
RESTORE LOG MyAdvWorks  
   FROM MyAdvWorks_log2  
   WITH RECOVERY;  
GO  

相關工作Related Tasks

另請參閱See Also

差異備份 (SQL Server) Differential Backups (SQL Server)
還原 (Transact-SQL)RESTORE (Transact-SQL)