事务日志备份 (SQL Server)Transaction Log Backups (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否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

事务日志备份“日志链” 的序列与数据备份无关。The sequence of transaction log backups log chain is independent of data backups. 例如,假设有下列事件顺序。For example, assume the following sequence of events.

TimeTime 事件Event
上午 8:008:00 AM 备份数据库。Back up database.
中午Noon 备份事务日志。Back up transaction log.
下午 4:004:00 PM 备份事务日志。Back up transaction log.
晚上 6:006:00 PM 备份数据库。Back up database.
晚上 8:008: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).

建议Recommendations

  • 如果事务日志损坏,则最新有效备份以后执行的工作将丢失。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. 实现恢复策略时,请考虑必需的 RTORPO,特别是日志备份频率。Think 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.
同样,实现恢复策略时,请考虑所需 RTORPO,尤其是完整和差异的数据库备份频率。Again, 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 WizardTo 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)