SQL Server事务日志备份，截断和缩减操作

In this article, we will cover SQL Server Transaction log backups, truncate and shrink operations with an overview and examples covering everything discussed

在本文中，我们将介绍SQL Server事务日志备份，截断和收缩操作，并提供概述和涵盖所有讨论内容的示例

If this article is your first visit to the SQL Server Transaction Log series, I recommend you to check the previous articles (see the TOC below), in which we described the internal structure of the SQL Server Transaction Log, the vital role that the Transaction Log plays in keeping the database in a consistent state and recovering the corrupted database or mistakenly modified table to a specific point in time. We discussed also in this series the three recovery models, Full, Simple and Bulk-Logged, that controls how the transactions will be written to the SQL Server Transaction Log file and finally how to manage and monitor the SQL Server Transaction Log growth.

如果本文是您第一次访问SQL Server事务日志系列，建议您阅读以前的文章（ 请参见下面的TOC ），在其中我们描述了SQL Server事务日志的内部结构，这是事务处理的重要角色。日志的作用是使数据库保持一致状态，并将损坏的数据库或错误地修改的表恢复到特定的时间点。 在本系列中，我们还讨论了三种恢复模型，即完全，简单和大容量日志记录，它们控制如何将事务写入SQL Server事务日志文件，以及最终如何管理和监视SQL Server事务日志的增长。

Building all the basic information from the previous articles, we are ready now to discuss deeply in this article the difference between the SQL Server Transaction Log backup, truncate and shrink concepts and how to perform these operations.

从前面的文章中构建所有基本信息，我们现在准备在本文中深入讨论SQL Server事务日志备份，截断和收缩概念以及如何执行这些操作之间的区别。

交易记录备份 (Transaction Log Backup)

When configuring your database with the Simple recovery model, the SQL Server Transaction Log will be marked as inactive and truncated automatically after committing the active transaction. This is not the case with the Full and Bulk-Logged database recovery models. When the database is configured with Full recovery model, the SQL Server Transaction Log in the Transaction Log file will be marked as inactive after committing the transaction, without being truncated automatically, as it will be waiting for a Transaction Log backup to be performed. Recall that only the Transaction Log backup, but NOT the database Full backup, will truncate the Transaction Logs from the Transaction Log file and makes it available for reuse. If no Transaction Log backup is taken from the database, the Transaction Log file will grow continuously, without truncation, until it runs out of free space.

使用简单恢复模型配置数据库时，SQL Server事务日志将被标记为非活动状态，并在提交活动事务后自动被截断。 完全和大容量记录数据库恢复模型不是这种情况。 当数据库配置为完全恢复模型时，提交事务后，事务日志文件中SQL Server事务日志将被标记为非活动状态，不会被自动截断，因为它将等待执行事务日志备份 。 回想一下，只有事务日志备份，而不是数据库完全备份会截断事务日志文件中的事务日志，并使其可供重用。 如果没有从数据库中获取事务日志备份，则事务日志文件将连续增长 ，并且不会被截断，直到可用空间用尽。

The SQL Server Transaction Log backup can be taken only from the database when the recovery model of that database is Full or Bulk-Logged. The recovery model of the database can be checked form the Options tab of the Database Properties window, as below:

仅当该数据库的恢复模型为“完全”或“批量记录”时，才可以从该数据库中获取SQL Server事务日志备份。 可以从“数据库属性”窗口的“ 选项”选项卡中检查数据库的恢复模型，如下所示：

If you try to take Transaction Log backup for a database that is configured with the Simple recovery model, the backup operation will fail with the error message below:

如果您尝试对使用简单恢复模型配置的数据库进行事务日志备份，则备份操作将失败，并显示以下错误消息：