還原交易記錄備份 (SQL Server)Restore a Transaction Log Backup (SQL Server)

適用於: 是SQL Server 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

此主題描述如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio ,在 Transact-SQLTransact-SQL中還原交易記錄備份。This topic describes how to restore a transaction log backup in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

本主題內容In This Topic

開始之前Before You Begin

必要條件Prerequisites

  • 備份必須依照它們建立的順序還原。Backups must be restored in the order in which they were created. 在還原特定交易記錄備份之前,您必須先還原下列先前的備份,而不必回復未認可的交易,即 WITH NORECOVERY:Before you can restore a particular transaction log backup, you must first restore the following previous backups without rolling back uncommitted transactions, that is WITH NORECOVERY:

    • 在特定交易記錄備份之前產生的完整資料庫備份與前次差異備份 (如果有的話)。The full database backup and the last differential backup, if any, taken before the particular transaction log backup. 在建立完整或差異資料庫備份之前,資料庫必須已在使用完整復原模式或大量記錄復原模式。Before the most recent full or differential database backup was created, the database must have been using the full recovery model or bulk-logged recovery model.

    • 在完整資料庫備份或差異備份 (如果您有還原其中一個) 之後及特定交易記錄備份之前產生的所有交易記錄備份。All transaction log backups taken after the full database backup or the differential backup (if you restore one) and before the particular transaction log backup. 必須依建立記錄備份的順序來套用記錄備份,且記錄鏈中沒有任何間距。Log backups must be applied in the sequence in which they were created, without any gaps in the log chain.

      如需交易記錄備份的詳細資訊,請參閱交易記錄備份 (SQL Server )套用交易記錄備份 (SQL Server )For more information about transaction log backups, see Transaction Log Backups (SQL Server) and Apply Transaction Log Backups (SQL Server).

安全性Security

權限Permissions

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

警告

還原的一般程序是在 [還原資料庫] 對話方塊中選取記錄備份,還有資料與差異備份。The normal process of a restore is to select the log backups in the Restore Database dialog box along with the data and differential backups.

還原交易記錄備份To restore a transaction log backup

  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, point to Restore, and then click Transaction Log, which opens the Restore Transaction Log dialog box.

    注意

    如果 [交易記錄] 呈灰色,您可能需要先還原完整備份或差異備份。If Transaction Log is grayed out, you may need to restore a full or differential backup first. 使用 [資料庫備份] 對話方塊。Use the Database backup dialog box.

  4. [一般] 頁面的 [資料庫] 清單方塊中,選取資料庫名稱。On the General page, in the Database list box, select the name of a database. 只會列出處於正在還原狀態的資料庫。Only databases in the restoring state are listed.

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

    • 從先前的資料庫備份From previous backups of 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.

    • 從檔案或磁帶From file or tape

      按一下瀏覽 ( ... ) 按鈕,開啟 [選取備份裝置] 對話方塊。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.

  6. [選取要還原的交易記錄備份] 方格中,選取要還原的備份。In the Select the transaction log backups to restore grid, select the backups to restore. 此方格會列出選取的資料庫可用的交易記錄備份。This grid lists the transaction log backups available for the selected database. 只有當資料庫的 [第一個 LSN] 大於 [最後一個 LSN] 時,才能使用記錄備份。A log backup is available only if its First LSN greater than the Last LSN of the database. 記錄備份會依它們所含的記錄序號 (LSN) 順序列出,而且必須按這個順序還原。Log backups are listed in the order of the log sequence numbers (LSN) they contain, and they must be restored in this order.

    下表列出方格的各資料行標頭,並描述各標頭的值。The following table lists the column headers of the grid and describes their values.

    標頭Header ReplTest1Value
    RestoreRestore 選取的核取方塊表示要還原的備份組。Selected check boxes indicate the backup sets to be restored.
    名稱Name 備份組的名稱。Name of the backup set.
    元件Component 備份的元件:資料庫 檔案,或 <空白> (適用於交易記錄)。Backed-up component: Database, File, or <blank> (for transaction logs).
    [資料庫備份]Database 執行備份所涉及的資料庫名稱。Name of the database involved in the backup operation.
    開始日期Start Date 備份作業開始的日期和時間,以用戶端的區域設定表示。Date and time when the backup operation began, presented in the regional setting of the client.
    完成日期Finish Date 備份作業完成的日期和時間,以用戶端的區域設定表示。Date and time when the backup operation finished, presented in the regional setting of the client.
    [第一個 LSN]First LSN 備份組內第一筆交易的記錄序號。Log sequence number of the first transaction in the backup set. 針對檔案備份為空白。Blank for file backups.
    [最後一個 LSN]Last LSN 備份組內最後一個交易的記錄序號。Log sequence number of the last transaction in the backup set. 針對檔案備份為空白。Blank for file backups.
    檢查點 LSNCheckpoint LSN 在建立備份時,最近的檢查點之記錄序號。Log sequence number of the most recent checkpoint at the time the backup was created.
    完整 LSNFull LSN 最近的完整資料庫備份之記錄序號。Log sequence number of the most recent full database backup.
    ServerServer 執行備份作業之 Database Engine 執行個體的名稱。Name of the Database Engine instance that performed the backup operation.
    使用者名稱User Name 執行備份作業之使用者的名稱。Name of the user who performed the backup operation.
    大小Size 備份組的大小,以位元組為單位。Size of the backup set in bytes.
    位置Position 備份組在磁碟區中的位置。Position of the backup set in the volume.
    到期Expiration 備份組到期的日期和時間。Date and time the backup set expires.
  7. 選取下列其中一項:Select one of the following:

    • 時間點Point in time

      保留預設值 ([最近可能的] ),或按一下瀏覽按鈕,開啟 [還原時間點] 對話方塊,選取特定的日期和時間。Either retain the default (Most recent possible) or select a specific date and time by clicking the browse button, which opens the Point in Time Restore dialog box.

    • 標示的交易Marked transaction

      還原資料庫至先前標示的交易。Restore the database to a previously marked transaction. 選取此選項會啟動 [選取標示的交易] 對話方塊,顯示一個方格,列出所選交易記錄備份中可用的已標示交易。Selecting this option launches the Select Marked Transaction dialog box, which displays a grid listing the marked transactions available in the selected transaction log backups.

      依預設,會還原到標示的交易,但是不含該交易。By default, the restore is up to, but excluding, the marked transaction. 若也要還原標示的交易,請選取 [包含標示的交易]To restore the marked transaction also, select Include marked transaction.

      下表列出方格的各資料行標頭,並描述各標頭的值。The following table lists the column headers of the grid and describes their values.

      標頭Header ReplTest1Value
      <空白><blank> 顯示選取標示的核取方塊。Displays a checkbox for selecting the mark.
      交易標示Transaction Mark 在認可交易時,由使用者所指定之標示交易的名稱。Name of the marked transaction specified by the user when the transaction was committed.
      日期Date 認可交易的日期和時間。Date and time of the transaction when it was committed. 交易日期和時間是依照 msdbgmarkhistory 資料表中記錄的顯示,而非依照用戶端電腦的日期和時間。Transaction date and time are displayed as recorded in the msdbgmarkhistory table, not in the client computer's date and time.
      說明Description 在認可交易時,由使用者所指定之標示交易的描述 (如果有的話)。Description of marked transaction specified by the user when the transaction was committed (if any).
      LSNLSN 標示之交易的記錄序號。Log sequence number of the marked transaction.
      [資料庫備份]Database 認可標示的交易之資料庫的名稱。Name of the database where the marked transaction was committed.
      使用者名稱User Name 認可標示的交易之資料庫使用者的名稱。Name of the database user who committed the marked transaction.
  8. 若要檢視或選取進階選項,請按一下 [選取頁面] 窗格中的 [選項]To view or select the advanced options, click Options in the Select a page pane.

  9. [還原選項] 區段中,選項如下:In the Restore options section, the choices are:

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

      將發行資料庫還原至並非建立該資料庫的伺服器時,就會保留複寫設定。Preserves the replication settings when restoring a published database to a server other than the server where the database was created.

      這個選項只能搭配 [回復未認可的交易,讓資料庫保持備妥可用...] 選項使用 (稍後會說明),這等於使用 RECOVERY 選項來還原備份。This option is available only with the Leave the database ready for use by rolling back the uncommitted transactions... option (described later), which is equivalent to restoring a backup with the RECOVERY option.

      選取此選項相當於使用 RESTORE 陳述式中的 Transact-SQLTransact-SQLKEEP_REPLICATION 選項。Checking this option is equivalent to using the KEEP_REPLICATION option in a Transact-SQLTransact-SQLRESTORE statement.

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

      還原每個備份組之前 (第一個之後),這個選項會帶出 [繼續還原] 對話方塊,其中會要求您指出是否要繼續還原順序。Before restoring each backup set (after the first), this option brings up the Continue with Restore dialog box, which asks you to indicate whether you want to continue the restore sequence. 這個對話方塊會顯示下一個媒體集名稱 (如果有)、備份組名稱,以及備份組描述。This dialog displays the name of the next media set (if available), the backup set name, and backup set description.

      您必須為不同的媒體集交換磁帶時,這個選項特別有用。This option is particularly useful when you must swap tapes for different media sets. 例如,您可以在伺服器只有一個磁帶裝置時使用。For example, you can use it when the server has only one tape device. 等到您準備好繼續後,才能按一下 [確定]Wait until you are ready to proceed before clicking OK.

      按一下 [否] 會讓資料庫保持在還原狀態。Clicking No leaves the database in the restoring state. 在上次的還原完成之後,您可以隨時繼續還原順序。At your convenience, you can continue the restore sequence after the last restore that completed. 如果下一個備份是資料或差異備份,請再次使用 [還原資料庫] 工作。If the next backup is a data or differential backup, use the Restore Database task again. 如果下一個備份是記錄檔備份,請使用 [還原交易記錄檔] 工作。If the next backup is a log backup, use the Restore Transaction Log task.

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

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

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

  10. 針對 [復原狀態] 選項,指定資料庫在還原作業之後的狀態。For the Recovery state options, specify the state of the database after the restore operation.

    • 回復未認可的交易,讓資料庫保持備妥可用。無法還原其他交易記錄。(RESTORE WITH RECOVERY)Leave the database ready for use by rolling back uncommitted transactions. Additional transaction logs cannot be restored. (RESTORE WITH RECOVERY)

      復原資料庫。Recovers the database. 此選項相當於 RESTORE 陳述式中的 Transact-SQLTransact-SQLRECOVERY 選項。This option is equivalent to the RECOVERY option in a Transact-SQLTransact-SQLRESTORE statement.

      只有當您沒有任何要還原的記錄檔時,才選擇這個選項。Choose this option only if you have no log files you want to restore.

    • 讓資料庫保持不運作,且不回復未認可的交易。可以還原其他交易記錄。(RESTORE WITH NORECOVERY)Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)

      讓資料庫處於無法復原狀態,也就是 RESTORING 的狀態。Leaves the database unrecovered, in the RESTORING state. 此選項相當於使用 RESTORE 陳述式中的 Transact-SQLTransact-SQLNORECOVERY 選項。This option is equivalent to using the NORECOVERY option in a Transact-SQLTransact-SQLRESTORE statement.

      選擇這個選項時,將無法使用 [保留複寫設定] 選項。When you choose this option, the Preserve replication settings option is unavailable.

      重要

      若為鏡像或次要資料庫,請一律選取這個選項。For a mirror or secondary database, always select this option.

    • 讓資料庫保持唯讀模式。恢復未認可的交易,但是將恢復動作儲存在檔案中,以便能夠反轉復原結果。(RESTORE WITH STANDBY)Leave the database in read-only mode. Undo uncommitted transactions, but save the undo actions in a file so that recovery effects can be reversed. (RESTORE WITH STANDBY)

      讓資料庫處於待命狀態。Leaves the database in a standby state. 此選項相當於使用 RESTORE 陳述式中的 Transact-SQLTransact-SQLSTANDBY 選項。This option is equivalent to using the STANDBY option in a Transact-SQLTransact-SQLRESTORE statement.

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

  11. [待命資料庫檔案] 文字方塊中指定待命資料庫檔案 (選擇性)。Optionally, specify a standby file name in the Standby file text box. 如果您讓資料庫處於唯讀模式,就需要指定此選項。This option is required if you leave the database in read-only mode. 您可以瀏覽待命資料庫檔案,或者在文字方塊中輸入其路徑名稱。You can browse for the standby file or type its pathname in the text box.

使用 Transact-SQLUsing Transact-SQL

重要

我們建議您在每一個 RESTORE 陳述式中永遠明確指定 WITH NORECOVERY 或 WITH RECOVERY,以避免模稜兩可。We recommend that you always explicitly specify either WITH NORECOVERY or WITH RECOVERY in every RESTORE statement to eliminate ambiguity. 這在撰寫指令碼時尤其重要。This is particularly important when writing scripts.

還原交易記錄備份To restore a transaction log backup

  1. 執行 RESTORE LOG 陳述式以套用交易記錄備份,請指定: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 where the transaction log backup will be restored from.

    • NORECOVERY 子句。The NORECOVERY clause.

    此陳述式的基本語法如下:The basic syntax for this statement is as follows:

    RESTORE LOG 資料庫名稱 FROM <備份裝置> WITH NORECOVERY。RESTORE LOG database_name FROM <backup_device> WITH NORECOVERY.

    其中,資料庫名稱 為資料庫名稱,而 <備份裝置> 為裝置名稱 (內含要還原的記錄備份)。Where database_name is the name of database and <backup_device>is the name of the device that contains the log backup being restored.

  2. 對於需要套用的每個交易記錄備份,請重複步驟 1。Repeat step 1 for each transaction log backup you have to apply.

  3. 依還原順序還原最後一個備份之後,若要復原資料庫,請使用下列其中一個陳述式:After restoring the last backup in your restore sequence, to recover the database use one of the following statements:

    • 在最後一個 RESTORE LOG 陳述式之中復原資料庫:Recover the database as part of the last RESTORE LOG statement:

      RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY;  
      GO  
      
    • 使用不同的 RESTORE DATABASE 陳述式來等待復原資料庫:Wait to recover the database by using a separate RESTORE DATABASE statement:

      RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY;   
      RESTORE DATABASE <database_name> WITH RECOVERY;  
      GO  
      

      等待復原資料庫,可讓您有機會確認是否已經還原所有必要的記錄備份。Waiting to recover the database gives you the opportunity to verify that you have restored all of the necessary log backups. 在執行時間點還原時,這個方法是較明智的。This approach is often advisable when you are performing a point-in-time restore.

    重要

    如果您建立鏡像資料庫,請省略復原步驟。If you are creating a mirror database, omit the recovery step. 鏡像資料庫必須保留 RESTORING 狀態。A mirror database must remain in the RESTORING state.

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

根據預設, AdventureWorks2012AdventureWorks2012 資料庫使用簡單復原模式。By default, the AdventureWorks2012AdventureWorks2012 database uses the simple recovery model. 此範例需要修改資料庫以使用完整復原模式,如下所示:The following examples require modifying the database to use the full recovery model, as follows:

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;  

A.A. 套用單一交易記錄備份Applying a single transaction log backup

下列範例一開始會使用名為 AdventureWorks2012AdventureWorks2012 備份裝置上的完整資料庫備份來還原 AdventureWorks2012_1資料庫。The following example starts by restoring the AdventureWorks2012AdventureWorks2012 database by using a full database backup that resides on a backup device named AdventureWorks2012_1. 此範例接著套用名為 AdventureWorks2012_log備份裝置上的第一個交易記錄備份。The example then applies the first transaction log backup that resides on a backup device named AdventureWorks2012_log. 最後,此範例復原了資料庫。Finally, the example recovers the database.

RESTORE DATABASE AdventureWorks2012  
   FROM AdventureWorks2012_1  
   WITH NORECOVERY;  
GO  
RESTORE LOG AdventureWorks2012  
   FROM AdventureWorks2012_log  
   WITH FILE = 1,  
   WITH NORECOVERY;  
GO  
RESTORE DATABASE AdventureWorks2012  
   WITH RECOVERY;  
GO  

B.B. 套用多個交易記錄備份Applying multiple transaction log backups

下列範例一開始會使用名為 AdventureWorks2012AdventureWorks2012 備份裝置上的完整資料庫備份來還原 AdventureWorks2012_1資料庫。The following example starts by restoring the AdventureWorks2012AdventureWorks2012 database by using a full database backup that resides on a backup device named AdventureWorks2012_1. 此範例接著逐一套用名為 AdventureWorks2012_log備份裝置上的前三個交易記錄備份。The example then applies, one by one, the first three transaction log backups that reside on a backup device named AdventureWorks2012_log. 最後,此範例復原了資料庫。Finally, the example recovers the database.

RESTORE DATABASE AdventureWorks2012  
   FROM AdventureWorks2012_1  
   WITH NORECOVERY;  
GO  
RESTORE LOG AdventureWorks2012  
   FROM AdventureWorks2012_log  
   WITH FILE = 1,  
   NORECOVERY;  
GO  
RESTORE LOG AdventureWorks2012  
   FROM AdventureWorks2012_log  
   WITH FILE = 2,  
   WITH NORECOVERY;  
GO  
RESTORE LOG AdventureWorks2012  
   FROM AdventureWorks2012_log  
   WITH FILE = 3,  
   WITH NORECOVERY;  
GO  
RESTORE DATABASE AdventureWorks2012  
   WITH RECOVERY;  
GO  

相關工作Related Tasks

另請參閱See Also

RESTORE (Transact-SQL) RESTORE (Transact-SQL)
套用交易記錄備份 (SQL Server)Apply Transaction Log Backups (SQL Server)