資料庫損毀時備份交易記錄 (SQL Server)Back Up the Transaction Log When the Database Is Damaged (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 back up a transaction log when the database is damaged 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

  • 在明確或隱含的交易中,並不允許使用 BACKUP 陳述式。The BACKUP statement is not allowed in an explicit or implicit transaction.

建議Recommendations

  • 如果是使用完整復原模式或大量記錄復原模式的資料庫,則在開始還原資料庫之前,您通常需要先備份記錄結尾。For a database that uses either the full or bulk-logged recovery model, you generally need to back up the tail of the log before beginning to restore the database. 在容錯移轉記錄傳送組態之前,您也應該先備份主要資料庫的記錄結尾。You also should back up the tail of the log of the primary database before failing over a log shipping configuration. 在復原資料庫之前將結尾記錄備份還原為最終的記錄備份可在失敗之後避免工作遺失的狀況。Restoring the tail-log backup as the final log backup before recovering the database avoids work loss after a failure. 如需結尾記錄備份的詳細資訊,請參閱結尾記錄備份 (SQL Server)For more information about tail-log backups, see Tail-Log Backups (SQL Server).

安全性Security

權限Permissions

BACKUP DATABASE 和 BACKUP LOG 權限預設為 sysadmin 固定伺服器角色以及 db_ownerdb_backupoperator 固定資料庫角色的成員。BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

備份裝置實體檔案的擁有權和權限問題可能會干擾備份作業。Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL ServerSQL Server 必須能夠讀取和寫入裝置;執行 SQL ServerSQL Server 服務的帳戶必須具備寫入權限。must be able to read and write to the device; the account under which the SQL ServerSQL Server service runs must have write permissions. 不過,在系統資料表中加入備份裝置項目的 sp_addumpdevice並不會檢查檔案存取權限。However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. 當您嘗試備份或還原時,存取實體資源之前不一定會出現備份裝置實體檔案的這些問題。Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

使用 SQL Server Management StudioUsing SQL Server Management Studio

若要備份交易記錄的結尾To back up the tail of the transaction log

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

  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, and then click Back Up. 會出現 [備份資料庫] 對話方塊。The Back Up Database dialog box appears.

  4. [資料庫] 清單方塊中確認資料庫名稱。In the Database list box, verify the database name. 您可以選擇性從清單中選取不同的資料庫。You can optionally select a different database from the list.

  5. 確認復原模式是否為 [FULL][BULK_LOGGED]Verify that the recovery model is either FULL or BULK_LOGGED.

  6. [備份類型] 清單方塊中,選取 [交易記錄]In the Backup type list box, select Transaction Log.

  7. 保留 [只複製備份] 的未選取狀態。Leave Copy Only Backup deselected.

  8. [備份組] 區域,接受 [名稱] 文字方塊中建議的預設備份組名稱,或者輸入不同的備份組名稱。In the Backup set area, either accept the default backup set name suggested in the Name text box, or enter a different name for the backup set.

  9. 在 [描述] 文字方塊中,輸入結尾記錄備份的描述。In the Description text box, enter a description for the tail-log backup.

  10. 指定備份組會在何時過期:Specify when the backup set will expire:

    • 若要讓備份組在特定的天數後過期,請按一下 [之後] (預設選項),然後輸入備份組建立之後將會過期的天數。To have the backup set expire after a specific number of days, click After (the default option), and enter the number of days after set creation that the set will expire. 這個值可以介於 0 到 99999 日之間;值為 0 日意指備份組永遠不會過期。This value can be from 0 to 99999 days; a value of 0 days means that the backup set will never expire.

      預設值會在 [伺服器屬性] 對話方塊 ( [資料庫設定] 頁面) 的 [預設備份媒體保留 (以天為單位)] 選項中設定。The default value is set in the Default backup media retention (in days) option of the Server Properties dialog box (Database Settings page). 若要存取此對話方塊,請以滑鼠右鍵按一下 [物件總管] 中的伺服器名稱並選取 [屬性],然後選取 [資料庫設定] 頁面。To access this dialog box, right-click the server name in Object Explorer and select properties; then select the Database Settings page.

    • 若要讓備份組在特定日期過期,請按一下 [於] ,然後輸入備份組將過期的日期。To have the backup set expire on a specific date, click On, and enter the date on which the set will expire.

  11. 按一下 [磁碟][磁帶] ,以選擇備份目的地的類型。Choose the type of backup destination by clicking Disk or Tape. 若要選取包含單一媒體集的磁碟或磁帶機 (最多 64 個) 的路徑,請按一下 [加入]To select the paths of up to 64 disk or tape drives containing a single media set, click Add. 選取的路徑會在 [備份至] 清單方塊中顯示。The selected paths are displayed in the Backup to list box.

    若要移除備份目的地,請選取目的地,然後按一下 [移除]To remove a backup destination, select it and click Remove. 若要檢視備份目的地的內容,請選取目的地,然後按一下 [內容]To view the contents of a backup destination, select it and click Contents.

  12. [選項] 頁面上,按一下下列其中一個項目,選取 [覆寫媒體] 選項:On the Options page, select an Overwrite Media option, by clicking one of the following:

    • 備份至現有的媒體集Back up to the existing media set

      針對這個選項,按一下 [附加至現有的備份組][覆寫所有現有的備份組]For this option, click either Append to the existing backup set or Overwrite all existing backup sets.

      另外,也可以選取 [檢查媒體集名稱及備份組是否逾期] ,以讓備份作業確認媒體集及備份組逾期的日期和時間。Optionally, select Check media set name and backup set expiration to cause the backup operation to verify the date and time at which the media set and backup set expire.

      另外,也可以在 [媒體集名稱] 文字方塊中輸入名稱。Optionally, enter a name in the Media set name text box. 如果未指定名稱,就會建立一個空白名稱的媒體集。If no name is specified, a media set with a blank name is created. 如果您指定媒體集名稱,就會檢查媒體 (磁帶或磁碟),以查看實際名稱是否與您在此處輸入的名稱相符。If you specify a media set name, the media (tape or disk) is checked to see whether the actual name matches the name you enter here.

      如果您讓媒體名稱保持空白,並核取方塊以針對媒體進行檢查,成功也會使媒體上的媒體名稱保持空白。If you leave the media name blank and check the box to check it against the media, success will equal the media name on the media also being blank.

    • 備份至新的媒體集,並清除所有現有的備份組Back up to a new media set, and erase all existing backup sets

      針對這個選項,在 [新媒體集名稱] 文字方塊中輸入名稱,然後選擇性在 [新媒體集描述] 文字方塊中描述媒體集。For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box.

    如需媒體集選項的詳細資訊,請參閱媒體集、媒體家族與備份組 (SQL Server)For more information about media set options, see Media Sets, Media Families, and Backup Sets (SQL Server).

  13. (選擇性) 在 [可靠性] 區段中選取:In the Reliability section, optionally, check:

    • [完成後驗證備份]Verify backup when finished.

    • 寫入媒體之前執行總和檢查碼Perform checksum before writing to media.

    • 發生總和檢查碼錯誤時繼續Continue on checksum error

    如需總和檢查碼的相關資訊,請參閱在備份和還原期間可能的媒體錯誤 (SQL Server)For information on checksums, see Possible Media Errors During Backup and Restore (SQL Server).

  14. [交易記錄] 區段中,按一下 [備份記錄的結尾,並讓資料庫保持在還原狀態]In the Transaction log section, check Back up the tail of the log, and leave database in the restoring state.

    這相當於指定以下 BACKUP 陳述式:This is equivalent to specifying the following BACKUP statement:

    BACKUP LOG <database_name> TO <backup_device> WITH NORECOVERY

    重要

    在還原時,[還原資料庫] 對話方塊會將結尾記錄備份的類型顯示為 [交易記錄 (只複製)] 。At restore time, the Restore Database dialog box displays the type of a tail-log backup as Transaction Log (Copy Only).

  15. 如果是備份至磁帶機 (在 [一般] 頁面的 [目的地] 區段中指定),[備份後卸載磁帶] 選項會啟用供選擇。If you are backing up to a tape drive (as specified in the Destination section of the General page), the Unload the tape after backup option is active. 按一下這個選項會啟動 [卸載之前倒轉磁帶] 選項。Clicking this option activates the Rewind the tape before unloading option.

  16. SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 和更新的版本支援 備份壓縮and later supports backup compression. 依預設,備份壓縮與否取決於 備份壓縮預設 伺服器組態選項的值。By default, whether a backup is compressed depends on the value of the backup-compression default server configuration option. 不過,不論目前的伺服器層級預設值為何,您都可以透過核取 [壓縮備份] 壓縮備份,而且可以透過核取 [不要壓縮備份] 防止壓縮。However, regardless of the current server-level default, you can compress a backup by checking Compress backup, and you can prevent compression by checking Do not compress backup.

    檢視目前的 backup compression defaultTo view the current backup compression default

使用 Transact-SQLUsing Transact-SQL

若要為目前使用中的交易記錄建立備份To create a backup of the currently active transaction log

  1. 執行 BACKUP LOG 陳述式,備份目前使用中的交易記錄,方法為指定:Execute the BACKUP LOG statement to back up the currently active transaction log, specifying:

    • 欲備份之交易記錄所屬的資料庫名稱。The name of the database to which the transaction log to back up belongs.

    • 將寫入交易記錄備份的備份裝置。The backup device where the transaction log backup will be written.

    • NO_TRUNCATE 子句。The NO_TRUNCATE clause.

      這個子句允許交易記錄作用中的部份也進行備份,即使資料庫無法存取,只要交易記錄可以存取而且沒有損毀即可。This clause allows the active part of the transaction log to be backed up even if the database is inaccessible, provided that the transaction log file is accessible and undamaged.

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

注意

這個範例使用 AdventureWorks2012AdventureWorks2012,而這個資料庫使用簡單復原模式。This example uses the AdventureWorks2012AdventureWorks2012, which uses the simple recovery model. 為了允許記錄備份,在執行完整資料庫備份之前,此資料庫已設定為使用完整復原模式。To permit log backups, before taking a full database backup, the database was set to use the full recovery model. 如需詳細資訊,請參閱檢視或變更資料庫的復原模式 (SQL Server)For more information, see View or Change the Recovery Model of a Database (SQL Server).

在資料庫已損毀且無法存取時,只要交易記錄沒有損毀且可存取,這個範例就會備份目前使用中的交易記錄。This example backs up the currently active transaction log when a database is damaged and inaccessible, if the transaction log is undamaged and accessible.

BACKUP LOG AdventureWorks2012  
   TO MyAdvWorks_FullRM_log1  
   WITH NO_TRUNCATE;  
GO  

另請參閱See Also

還原交易記錄備份 (SQL Server) Restore a Transaction Log Backup (SQL Server)
將 SQL Server 資料庫還原至某個時間點 (完整復原模式) Restore a SQL Server Database to a Point in Time (Full Recovery Model)
備份資料庫 (備份選項頁面) Back Up Database (Backup Options Page)
備份資料庫 (一般頁面) Back Up Database (General Page)
套用交易記錄備份 (SQL Server) Apply Transaction Log Backups (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
檔案還原 (簡單復原模式) File Restores (Simple Recovery Model)
檔案還原 (完整復原模式)File Restores (Full Recovery Model)