將 SQL Server 資料庫還原至某個時間點 (完整復原模式)Restore a SQL Server Database to a Point in Time (Full Recovery Model)

適用於: 是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 database to a point in time in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 本主題僅與使用完整或大量記錄復原模式的 SQL ServerSQL Server 資料庫相關。This topic is relevant only for SQL ServerSQL Server databases that use the full or bulk-logged recovery models.

重要

在大量記錄復原模式下,如果記錄備份包含大量記錄的變更,則時間點復原不可能復原至該備份內的時間點。Under the bulk-logged recovery model, if a log backup contains bulk-logged changes, point-in-time recovery is not possible to a point within that backup. 資料庫必須復原至交易記錄備份的結尾。The database must be recovered to the end of the transaction log backup.

開始之前Before You Begin

建議Recommendations

  • 您可以使用 STANDBY 尋找未知時間點。Use STANDBY to find unknown point in time.

  • 指定還原順序中較早的時間點Specify the point in time early in a restore sequence

安全性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 database to a point in time

  1. 在 [物件總管] 中,連接至適當的 SQL Server Database EngineSQL Server Database Engine執行個體,並展開伺服器樹狀目錄。In Object Explorer, connect to the appropriate instance of the SQL Server Database EngineSQL Server Database Engine, and 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.

  6. 按一下 [時間表] ,存取 [備份時間表] 對話方塊。Click Timeline to access the Backup Timeline dialog box.

  7. [還原至] 區段中,按一下 [特定的日期與時間]In the Restore to section, click Specific date and time.

  8. 使用 [日期][時間] 方塊或滑動軸,指定應該停止還原的特定日期與時間。Use either the Date and Time boxes or the slider bar to specify a specific date and time to where the restore should stop. 按一下 [確定] 。Click OK.

    注意

    您可以使用 [時間表間隔] 方塊變更時間表上顯示的時間量。Use the Timeline Interval box to change the amount of time displayed on the timeline.

  9. 當您指定了特定的時間點之後,Database Recovery Advisor 便會在 [要還原的備份組] 方格的 [還原] 資料行中確定只選取要還原到該時間點所需的備份。After you have specified a specific point in time, the Database Recovery Advisor ensures that only backups that are required for restoring to that point in time are selected in the Restore column of the Backup sets to restore grid. 這些選取的備份為您的時間點還原構成了建議的還原計畫。These selected backups make up the recommended restore plan for your point-in-time restore. 您應該只使用選取的備份來進行時間點還原作業。You should use only the selected backups for your point-in-time restore operation.

    如需 [要還原的備份組] 方格中各資料行的相關資訊,請參閱還原資料庫 (一般頁面)For information about the columns in the Backup sets to restore grid, see Restore Database (General Page). 如需資料庫復原建議程式的相關資訊,請參閱還原和復原概觀 (SQL Server)For information about the Database Recovery Advisor, see Restore and Recovery Overview (SQL Server).

  10. [選項] 頁面的 [還原選項] 面板中,您可以選取下列任何選項 (如果情況適用):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)

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

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

  11. 針對 [還原狀態] 方塊,選取選項。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).

  12. 如果選取的時間點需要 [還原前先進行結尾記錄備份] ,則會予以選取。Take tail-log backup before restore will be selected if it is necessary for the point in time that you have selected. 您不需要修改這個設定,但是即使不需要,還是可以選擇備份記錄結尾。You do not need to modify this setting, but you can choose to backup the tail of the log even if it is not required.

  13. 若資料庫有使用中的連接,還原作業可能會失敗。Restore operations may 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. 這個核取方塊會在執行還原作業之前將資料庫設定為單一使用者模式,並在完成後將資料庫設定為多使用者模式。This check box sets the database to single user mode before performing the restore operations, and sets the database to multi-user mode when complete.

  14. 如果想要系統在每個還原作業之間提示您,請選取 [還原每個備份之前先提示]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.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。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

Before you beginBefore you begin

指定的時間一律是從記錄備份中還原。A specified time is always restored from a log backup. 在還原順序的每個 RESTORE LOG 陳述式中,您必須在相同的 STOPAT 子句中指定目標時間或交易。In every RESTORE LOG statement of the restore sequence, you must specify your target time or transaction in an identical STOPAT clause. 您必須先還原其端點早於目標還原時間的完整資料庫備份,當做時間點還原的必要條件。As a prerequisite to a point-in-time restore, you must first restore a full database backup whose end point is earlier than your target restore time. 該完整資料庫備份可以晚於最近的完整資料庫備份,只要您之後還原每個後續的記錄備份即可,最多並包括含有目標時間點的記錄備份。That full database backup can be older than the most recent full database backup as long as you then restore every subsequent log backup, up to and including the log backup that contains your target point in time.

若要協助您識別要還原哪個資料庫備份,可以選擇性地在 RESTORE DATABASE 陳述式中指定 WITH STOPAT 子句,以便在資料庫備份太接近指定的目標時間時引發錯誤。To help you identify which database backup to restore, you can optionally specify your WITH STOPAT clause in your RESTORE DATABASE statement to raise an error if a data backup is too recent for the specified target time. 此時,系統一定會還原完整的資料備份,即使它包含目標時間也一樣。The complete data backup is always restored, even if it contains the target time.

基本 Transact-SQLTransact-SQL 語法Basic Transact-SQLTransact-SQL syntax

RESTORE LOG database_name FROM <backup_device> WITH STOPAT = time , RECOVERY...RESTORE LOG database_name FROM <backup_device> WITH STOPAT =time, RECOVERY...

復原點是在由 時間 指定的 datetime值當時或之前所發生的最新交易認可。The recovery point is the latest transaction commit that occurred at or before the datetime value that is specified by time.

若只要還原特定時間點之前進行的修改,請為您要還原的每個備份指定 WITH STOPAT = timeTo restore only the modifications that were made before a specific point in time, specify WITH STOPAT = time for each backup you restore. 這樣可確保您不會還原到超過目標時間。This makes sure that you do not go past the target time.

將資料庫還原至某個時間點To restore a database to a point in time

注意

如需這個程序的範例,請參閱本節稍後的 範例 (Transact-SQL)For an example of this procedure, see Example (Transact-SQL), later in this section.

  1. 連接至想要在其上還原資料庫的伺服器執行個體。Connect to server instance on which you want to restore the database.

  2. 使用 NORECOVERY 選項執行 RESTORE DATABASE 陳述式。Execute the RESTORE DATABASE statement using the NORECOVERY option.

    注意

    如果部分還原順序排除任何 FILESTREAM 檔案群組,則不支援時間點還原。If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported. 您可以強制還原順序,以繼續進行。You can force the restore sequence to continue. 但是,絕對無法還原 RESTORE 陳述式中省略的 FILESTREAM 檔案群組。However the FILESTREAM filegroups that are omitted from your RESTORE statement can never be restored. 若要強制時間點還原,請指定 CONTINUE_AFTER_ERROR 選項,連同 STOPAT、STOPATMARK 或 STOPBEFOREMARK 選項,而且您也必須在後續的 RESTORE LOG 陳述式中指定這些項目。To force a point-in-time restore, specify the CONTINUE_AFTER_ERROR option together with the STOPAT, STOPATMARK, or STOPBEFOREMARK option, which you must also specify in your subsequent RESTORE LOG statements. 如果您指定 CONTINUE_AFTER_ERROR,則部分還原順序會成功,而 FILESTREAM 檔案群組則會變成無法復原。If you specify CONTINUE_AFTER_ERROR, the partial restore sequence succeeds and the FILESTREAM filegroup becomes unrecoverable.

  3. 還原上一次的差異資料庫備份 (如有),但不復原資料庫 (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY)。Restore the last differential database backup, if any, without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).

  4. 依建立的相同順序,套用每個交易記錄備份,並指定想要停止還原記錄的時間 (RESTORE DATABASE database_name FROM <backup_device> WITH STOPAT = time , RECOVERY)。Apply each transaction log backup in the same sequence in which they were created, specifying the time at which you intend to stop restoring log (RESTORE DATABASE database_name FROM <backup_device> WITH STOPAT**=time,** RECOVERY).

    注意

    RECOVERY 及 STOPAT 選項。The RECOVERY and STOPAT options. 如果交易記錄備份中不含所要求的時間 (例如指定的時間超出交易記錄的結束時間),則會產生警告訊息,且此資料庫會維持未復原狀態。If the transaction log backup does not contain the requested time (for example, if the time specified is beyond the end of the time covered by the transaction log), a warning is generated and the database remains unrecovered.

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

下列範例會將資料庫還原至 12:00 AM April 15, 2020 時的狀態,並顯示含有多個記錄備份的還原作業。The following example restores a database to its state as of 12:00 AM on April 15, 2020 and shows a restore operation that involves multiple log backups. 在備份裝置 AdventureWorksBackups上,要還原的完整資料庫備份是裝置上的第三個備份組 (FILE = 3),第一個記錄備份是第四個備份組 (FILE = 4),而第二個記錄備份是第五個備份組 (FILE = 5)。On the backup device, AdventureWorksBackups, the full database backup to be restored is the third backup set on the device (FILE = 3), the first log backup is the fourth backup set (FILE = 4), and the second log backup is the fifth backup set (FILE = 5).

重要

AdventureWorks2012AdventureWorks2012 資料庫使用簡單復原模式。The AdventureWorks2012AdventureWorks2012 database uses the simple recovery model. 若要允許記錄備份,在執行完整資料庫備份之前,已使用 ALTER DATABASE AdventureWorks SET RECOVERY FULL將資料庫設定為使用完整復原模式。To permit log backups, before taking a full database backup, the database was set to use the full recovery model, using ALTER DATABASE AdventureWorks SET RECOVERY FULL.

RESTORE DATABASE AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=3, NORECOVERY;  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups  
   WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
RESTORE DATABASE AdventureWorks WITH RECOVERY;   
GO  
  

相關工作Related Tasks

另請參閱See Also

backupset (Transact-SQL) backupset (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
RESTORE HEADERONLY (Transact-SQL)RESTORE HEADERONLY (Transact-SQL)