交易記錄備份 (SQL Server)Transaction Log Backups (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 Server 資料庫有關。This topic is relevant only for SQL ServerSQL Server databases that are using the full or bulk-logged recovery models. 本主題討論 SQL ServerSQL Server 資料庫的交易記錄備份。This topic discusses backing up the transaction log of a SQL ServerSQL Server database.

您至少要在建立任何記錄備份之前,必須建立一個完整備份。Minimally, you must have created at least one full backup before you can create any log backups. 之後,除非交易記錄已正在備份中,否則任何時候皆可以備份交易記錄。After that, the transaction log can be backed up at any time unless the log is already being backed up.

建議您時常進行記錄備份,以將工作損失風險降至最低,同時也讓記錄能夠截斷。We recommend you take log backups frequently, both to minimize work loss exposure and to truncate the transaction log.

一般而言,資料庫管理員有時會建立完整資料庫備份,例如每週一次;並且會選擇性地於較短的間隔建立一系列差異資料庫備份,例如每日一次。A database administrator typically creates a full database backup occasionally, such as weekly, and, optionally, creates a series of differential database backup at a shorter interval, such as daily. 除了資料庫備份之外,資料庫管理員會為交易記錄採取高頻率備份。Independent of the database backups, the database administrator backs up the transaction log at frequent intervals. 每種備份類型的最佳間隔取決於幾項因素,如資料的重要性、資料庫大小及伺服器負載。For a given type of backup, the optimal interval depends on factors such as the importance of the data, the size of the database, and the workload of the server. 如需實作良好策略的詳細資訊,請參閱本主題中的建議For more information about implementing a good strategy, see Recommendations in this topic.

記錄備份順序的運作方式How a sequence of log backups works

交易記錄備份 「記錄檔鏈結」 (Log chain) 的順序與資料備份無關。The sequence of transaction log backups log chain is independent of data backups. 例如,假設發生以下一連串事件:For example, assume the following sequence of events.

TimeTime 事件Event
8:00 AM8:00 AM 備份資料庫。Back up database.
中午Noon 備份交易記錄。Back up transaction log.
4:00 PM4:00 PM 備份交易記錄。Back up transaction log.
6:00 PM6:00 PM 備份資料庫。Back up database.
8:00 PM8:00 PM 備份交易記錄。Back up transaction log.

在晚上 8:00 建立的交易記錄備份包含從下午 4:00 到晚上 8:00 的交易記錄,其跨越在下午 6:00 建立完整資料庫備份的時間。這一連串的交易記錄備份會從上午 8:00 時建立的初始完整資料庫備份開始,一直持續到晚上 8:00 時建立的最後一個交易記錄備份。The transaction log backup created at 8:00 PM contains transaction log records from 4:00 PM through 8:00 PM, spanning the time when the full database backup was created at 6:00 PM The sequence of transaction log backups is continuous from the initial full database backup created at 8:00 AM to the last transaction log backup created at 8:00 PM. 如需有關如何套用這些記錄備份的資訊,請參閱 套用交易記錄備份 (SQL Server)中的範例。For information about how to apply these log backups, see the example in Apply Transaction Log Backups (SQL Server).


  • 如果異動記錄損毀,則最近一次有效備份之後所執行的工作都會遺失。If a transaction log is damaged, work that is performed since the most recent valid backup is lost. 因此,我們強烈建議您將記錄檔存放於容錯的儲存體中。Therefore we strongly recommend that you put your log files on fault-tolerant storage.

  • 若資料庫損毀或您準備還原資料庫時,建議您建立 結尾記錄備份 ,使您可以將資料庫還原至目前的時間點。If a database is damaged or you are about to restore the database, we recommend that you create a tail-log backup to enable you to restore the database to the current point in time.

  • 根據預設,每項成功的備份作業都會在 SQL ServerSQL Server 錯誤記錄檔與系統事件記錄檔中,加入一個項目。By default, every successful backup operation adds an entry in the SQL ServerSQL 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).

  • 請經常進行充分的記錄備份來支援商務需求,特別是您對工作損失 (例如可能因損壞的記錄儲存體而引起) 的耐受性。Take frequent enough log backups to support your business requirements, specifically your tolerance for work loss such as might be caused by a damaged log storage.

  • 進行記錄備份的頻率如何才適當,視您在工作損失風險的耐受性,與儲存、管理及可能還原記錄備份的容量之間所做的取捨而定。The appropriate frequency for taking log backups depends on your tolerance for work-loss exposure balanced by how many log backups you can store, manage, and, potentially, restore. 實作復原策略,以及特別是記錄備份頻率時,考慮使用必要的 RTORPOThink about the required RTO and RPO when implementing your recovery strategy, and specifically the log backup cadence.

  • 每 15 到 30 分鐘進行一次記錄備份可能就足夠了。Taking a log backup every 15 to 30 minutes might be enough. 如果您的業務需要將工作損失風險減至最低,請考慮更頻繁地進行記錄備份。If your business requires that you minimize work-loss exposure, consider taking log backups more frequently. 較頻繁的記錄備份還會帶來另一優點,就是增加記錄截斷的頻率,從而產生較小的記錄檔。More frequent log backups have the added advantage of increasing the frequency of log truncation, resulting in smaller log files.


若要限制您需要還原的記錄備份數目,定期備份資料是基本作業。To limit the number of log backups that you need to restore, it is essential to routinely back up your data. 例如,您可能會排程每週的完整資料庫備份和每日的差異資料庫備份。For example, you might schedule a weekly full database backup and daily differential database backups.
同樣地,實作復原策略,以及特別是完整和差異資料庫備份頻率時,考慮使用必要的 RTORPOAgain, think about the required RTO and RPO when implementing your recovery strategy, and specifically the full and differential database backup cadence.

相關工作Related Tasks

若要建立交易記錄備份To create a transaction log backup

若要排程備份作業,請參閱< Use the Maintenance Plan Wizard>。To schedule backup jobs, see Use the Maintenance Plan Wizard.

另請參閱See Also

交易記錄 (SQL Server) The Transaction Log (SQL Server)
SQL Server 中的交易記錄備份交易記錄架構與管理指南 Transaction Log Backups in the SQL Server Transaction Log Architecture and Management Guide
SQL Server 資料庫的備份與還原 Back Up and Restore of SQL Server Databases
結尾記錄備份 (SQL Server) Tail-Log Backups (SQL Server)
套用交易記錄備份 (SQL Server)Apply Transaction Log Backups (SQL Server)