備份交易記錄 (SQL Server)Back Up a Transaction Log (SQL Server)

此主題描述如何使用 SQL Server 2014SQL Server 2014Transact-SQLSQL Server Management Studio或 PowerShell,在 Transact-SQLTransact-SQL中備份交易記錄。This topic describes how to back up a transaction log in SQL Server 2014SQL Server 2014 by using Transact-SQLSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell.

本主題內容In This Topic

開始之前Before You Begin

限制事項Limitations and Restrictions

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

建議Recommendations

  • 如果資料庫使用完整復原模式或大量記錄復原模式,您必須定期備份交易記錄,使其足以保護您的資料,並讓交易記錄不要被填滿。If a database uses either the full or bulk-logged recovery model, you must back up the transaction log regularly enough to protect your data and to keep the transaction log from filling. 這會截斷記錄並支援將資料庫還原到特定時間點。This truncates the log and supports restoring the database to a specific point in time.

  • 根據預設,每項成功的備份作業都會在 [SQL Server]SQL Server 錯誤記錄檔與系統事件記錄檔中,加入一個項目。By default, every successful backup operation adds an entry in the [SQL Server]SQL Server error log and in the system event log. 如果您經常備份記錄檔,這些成功訊息可能會快速累積,因而產生龐大的錯誤記錄檔,讓您難以尋找其他訊息。If back up the log very frequently, these success messages accumulate quickly, resulting in huge error logs that can make finding other messages difficult. 在這類情況下,如果沒有任何指令碼相依於這些記錄項目,您就可以使用追蹤旗標 3226 來隱藏這些記錄項目。In such cases you can suppress these log entries by using trace flag 3226 if none of your scripts depend on those entries. 如需詳細資訊,請參閱追蹤旗標(Transact-SQL)For more information, see Trace Flags (Transact-SQL).

SecuritySecurity

PermissionsPermissions

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 Server]SQL Server 必須能夠讀取和寫入裝置;執行 [SQL Server]SQL Server 服務的帳戶必須具備寫入權限。must be able to read and write to the device; the account under which the [SQL Server]SQL 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 a transaction log

  1. 連接到適當的 SQL Server Database EngineSQL Server Database Engine執行個體之後,請在 [物件總管] 中按一下伺服器名稱以展開伺服器樹狀目錄。After connecting to the appropriate instance of the 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. 或者,您也可以選取 [僅複製備份] 來建立僅複製備份。Optionally, you can select Copy Only Backup to create a copy-only backup. 「只複製備份」 (Copy-only backup) 是與傳統 [SQL Server]SQL Server 備份順序無關的 [SQL Server]SQL Server 備份。A copy-only backup is a [SQL Server]SQL Server backup that is independent of the sequence of conventional [SQL Server]SQL Server backups. 如需詳細資訊,請參閱只複製備份 (SQL Server)For more information, see Copy-Only Backups (SQL Server).

    注意

    選取 [差異] 選項時,您無法建立只複製備份。When the Differential option is selected, you cannot create a copy-only backup.

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

  9. (選擇性) 在 [描述] 文字方塊中輸入備份組的描述。Optionally, in the Description text box, enter a description of the backup set.

  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. 按一下 [磁碟][URL][磁帶] ,以選擇備份目的地的類型。Choose the type of backup destination by clicking Disk, URL 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. 若要檢視或選取進階選項,請按一下 [選取頁面] 窗格中的 [選項]To view or select the advanced options, click Options in the Select a page pane.

  13. 按下列項目之一,以選取 [覆寫媒體] 選項: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. 如需詳細資訊,請參閱媒體集、媒體家族與備份組 (SQL Server)For more information, see Media Sets, Media Families, and Backup Sets (SQL Server).

      另外,也可以選取 [檢查媒體集名稱及備份組是否逾期] ,以讓備份作業確認媒體集及備份組逾期的日期和時間。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, see Media Sets, Media Families, and Backup Sets (SQL Server).

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

  15. [交易記錄] 區段中:In the Transaction log section:

    • 對於例行的記錄備份,請保留預設選項 [移除非使用中的項目以截斷交易記錄]For routine log backups, keep the default selection, Truncate the transaction log by removing inactive entries.

    • 若要備份記錄的結尾 (亦即,使用中的記錄),請勾選 [備份記錄檔的結尾,並讓資料庫保持在還原狀態]To back up the tail of the log (that is, the active log), check Back up the tail of the log, and leave database in the restoring state.

      結尾記錄備份是在發生失敗後進行的,會備份記錄的結尾,以防止遺失工作資料。A tail-log backup is taken after a failure to back up the tail of the log in order to prevent work loss. 在以下兩種情況時應備份使用中的記錄 (結尾記錄備份):發生失敗後開始還原資料庫之前,或是容錯移轉到次要資料庫時。Back up the active log (a tail-log backup) both after a failure, before beginning to restore the database, or when failing over to a secondary database. 選取此選項相當於在 Transact-SQL 的 BACKUP LOG 陳述式中,指定 NORECOVERY 選項。Selecting this option is equivalent to specifying the NORECOVERY option in the BACKUP LOG statement of Transact-SQL. 如需結尾記錄備份的詳細資訊,請參閱結尾記錄備份 (SQL Server)For more information about tail-log backups, see Tail-Log Backups (SQL Server).

  16. 如果是備份至磁帶機 (在 [一般] 頁面的 [目的地] 區段中指定), [備份後卸載磁帶] 選項會啟用供選擇。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.

  17. SQL Server 2008 EnterpriseSQL Server 2008 Enterprise 和更新的版本支援 備份壓縮and later supports backup compression. 備份壓縮與否預設是取決於 backup-compression default 伺服器組態選項的值。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

加密Encryption

若要加密備份檔案,請核取 [加密備份] 核取方塊。To encrypt the backup file check the Encrypt backup check box. 選取要用於加密備份檔案的加密演算法,並提供憑證或非對稱金鑰。Select an encryption algorithm to use for encrypting the backup file and provide a Certificate or Asymmetric key. 可用於加密的演算法包括:The available algorithms for encryption are:

  • AES 128AES 128

  • AES 192AES 192

  • AES 256AES 256

  • Triple DESTriple DES

使用 Transact-SQLUsing Transact-SQL

備份交易記錄檔To back up a transaction log

  1. 執行 BACKUP LOG 陳述式以備份交易記錄,並指定下列項目:Execute the BACKUP LOG statement to back up the transaction log, specifying the following:

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

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

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

重要

這個範例使用 AdventureWorks2012AdventureWorks2012 資料庫,而該資料庫則是使用簡單復原模式。This example uses the AdventureWorks2012AdventureWorks2012 database, 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).

這個範例會在先前所建立的具名備份裝置 AdventureWorks2012AdventureWorks2012 上建立 MyAdvWorks_FullRM_log1資料庫的交易記錄備份。This example creates a transaction log backup for the AdventureWorks2012AdventureWorks2012 database to the previously created named backup device, MyAdvWorks_FullRM_log1.

BACKUP LOG AdventureWorks2012  
   TO MyAdvWorks_FullRM_log1;  
GO  

使用 PowerShellUsing PowerShell

使用 Backup-SqlDatabase 指令程式,並且為 Log 參數指定 -BackupAction 值。Use the Backup-SqlDatabase cmdlet and specify Log for the value of the -BackupAction parameter.

下列範例會在伺服器執行個體 MyDB 的預設備份位置,建立 Computer\Instance資料庫的記錄備份。The following example creates a log backup of the MyDB database to the default backup location of the server instance Computer\Instance.

```powershell
Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Log  
```  

若要設定及使用 SQL Server PowerShell 提供者,請參閱SQL Server PowerShell 提供者To set up and use the SQL Server PowerShell provider, see SQL Server PowerShell Provider.

相關工作Related Tasks

另請參閱See Also

BACKUP (Transact-SQL) BACKUP (Transact-SQL)
套用交易記錄備份 (SQL Server) Apply Transaction Log Backups (SQL Server)
維護計畫 Maintenance Plans
完整檔案備份 (SQL Server)Full File Backups (SQL Server)