SQLServer事务⽇志备份，截断和缩减操作
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 (), 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事务⽇志系列，建议您阅读以前的⽂章（ ），在其中我们描述了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 , 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 , 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:
如果您尝试对使⽤简单恢复模型配置的数据库进⾏事务⽇志备份，则备份操作将失败，并显⽰以下错误消息：
In addition, the Transaction Log backup requires that at least one Full backup is taken from that database as a start point
for the new backup chain. If you try to take a Transaction Log backup from a database with no Full backup taken previously, the backup operation will fail with the error message below:
此外，事务⽇志备份要求从该数据库中⾄少获取⼀个完整备份，作为新备份链的起点。 如果您尝试从以前未进⾏完整备份的数据库中获取事务⽇志备份，则备份操作将失败，并显⽰以下错误消息：
sqlserver备份表语句
Let’s take a Full backup for the database to be able to take Transaction Log backup for that database. We will use the BACKUP DATABASE T-SQL command to perform the database Full backup operation in our example here. For more information about the different ways and options for performing database backups in SQL Server, check the . The Full backup of the database can be taken using the T-SQL script below:
让我们对数据库进⾏完整备份，以便能够对该数据库进⾏事务⽇志备份。 在此处的⽰例中，我们将使⽤BACKUP DATABASE T-SQL命令执⾏数据库完全备份操作。 有关在SQL Server中执⾏数据库备份的不同⽅法和选项的更多信息，请检查 。 可以使⽤以下T-SQL脚本对数据库进⾏完全备份：
BACKUP DATABASE [TSQL]
TO DISK = N'C:\Ahmad Yaseen\TSQL.bak' WITH NOFORMAT, NOINIT,
NAME = N'TSQL-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
Once the database Full backup is performed, we will start taking the Transaction Log backups for the database. The first Transaction Log backup will take a backup for all the transactions that occurred in the database since the last Full backup. The Transaction Log backup can be taken using the BACKUP LOG T-SQL command below:
⼀旦执⾏了数据库完全备份，我们将开始为数据库进⾏事务⽇志备份。 第⼀次事务⽇志备份将备份⾃上次完全备份以来数据库中发⽣的所有事务的备份。 可以使⽤下⾯的BACKUP LOG T-SQL命令进⾏事务⽇志备份：
BACKUP LOG [TSQL]
TO DISK = N'C:\Ahmad Yaseen\TSQL_2.TRN' WITH NOFORMAT, NOINIT,
NAME = N'TSQL-TRN Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
On the other hand, the Transaction Log backups that follows the first Transaction Log backup will take backup for all transactions that occurred in the database since the point that the last Transaction Log backup stopped at. The Full backup and all following Transaction Log backup until a new Full backup is taken is called Backup Chain. This backup chain is important to recover the database to a specific point in time, in the case of any mistakenly performed change or database corruption. The frequency of the Transaction Log backup depends on how important your data is, the size of the database and what type of workload this database serves. In the heavily transactional databases, it is recommended to increase the frequency of the Transaction Log backup, in order to minimize the data loss and truncate the Transaction Logs to make it available for reuse.
在另⼀⽅⾯， 下⾯的第⼀个事务⽇志备份事务⽇志备份将备份以来的最后⼀个事务⽇志备份停在点发⽣在数据库中的所有事务。 完全备份以及随后的所有事务⽇志备份（直到进⾏了新的完全备份）都称为备份链 。 在任何错误执⾏的更改或数据库损坏的情况下，此备份链对于将数据库恢复到特定的时间
点⾮常重要。 事务⽇志备份的频率取决于数据的重要性，数据库的⼤⼩以及该数据库服务的⼯作负载类型。 在事务频繁的数据库中，建议增加事务⽇志备份的频率，以最⼤程度地减少数据丢失并截断事务⽇志以使其可重复使⽤。
If the database is damaged, it is recommended to create a tail-log backup to enable you to restore the database to the current point in time. A is used to capture all log records that have not yet been backed up. This will help in preventing any data loss and to keep the log chain complete.
如果数据库已损坏，建议创建尾⽇志备份，以使您可以将数据库还原到当前时间点。 ⽤于捕获所有尚未备份的⽇志记录。 这将有助于防⽌任何数据丢失并保持⽇志链完整。
Assume that you have executed the below DELETE statement by mistake without providing the WHERE clause. This means that all table records will be deleted:
假定您错误地执⾏了以下DELETE语句，⽽没有提供WHERE⼦句。 这意味着将删除所有表记录：
If you have designed a proper backup solution, the data can be easily recovered by restoring the database back to the specific point in time before executing the DELETE statement. From the Restore Database window, the SQL Server will return the complete backup chain that is taken from that database. If you know the exact file that is taken directly before the data deletion, you can stop at that specific file, as shown below:
如果您设计了适当的备份解决⽅案，则可以通过在执⾏DELETE语句之前将数据库还原到特定的时间点来轻松恢复数据。 在“还原数据库”窗⼝中，SQL Server将返回从该数据库获取的完整备份链。 如果您知道删除数据之前直接获取的确切⽂件，则可以在该特定⽂件处停⽌，如下所⽰：
But if you are aware of the exact time of executing the DELETE statement, you can restore the database back to that specific point in time before the DELETE statement execution, without the need to know which Transaction Log file contains that point in time. This can be achieved by clicking on the Timeline option, and specify the time, as shown below:
但是，如果您知道执⾏DELETE语句的确切时间，则可以将数据库还原到执⾏DELETE语句之前的特定时间点，⽽⽆需知道哪个事务⽇志⽂件包含该时间点。 这可以通过单击“ 时间轴”选项并指定时间来实现，如下所⽰：
事务⽇志截断 (Transaction Log Truncate)
SQL Server Transaction is the process in which all VLFs that are marked as inactive will be deleted from the SQL Server Transaction Log file and become available for reuse. If there is a single active log record in a VLF, the overall VLF will be considered as active log and cannot be truncated.
SQL Server事务是从SQL Server事务⽇志⽂件中删除所有标记为⾮活动的VLF并重新使⽤的过程。 如果VLF中只有⼀个活动⽇志记录，则整个VLF将被视为活动⽇志，不能被截断。
The SQL Server Transaction Log, for the database that is configured with the Simple recovery model, can be truncated automatically if:
在以下情况下，可以⾃动截断针对使⽤简单恢复模型配置的数据库SQL Server事务⽇志：
operator is triggered 运算符被触发
The database transaction is committed
数据库事务已提交
The SQL Server Transaction Log, for the database that is configured with the Full or Bulk-Logged recovery model, can be truncated automatically:
对于使⽤完整或批量记录恢复模型配置的数据库，SQL Server事务⽇志可以被⾃动截断：
After performing a Transaction Log backup process, and the Transaction Log is not waiting for an act
ive transaction or any high availability feature, such as Mirroring, Replication or Always On Availability Group
在执⾏事务⽇志备份过程之后，事务⽇志没有等待活动的事务或任何⾼可⽤性功能，例如镜像，复制或始终可⽤性组
Change the database recovery model to Simple
For example, if we change the recovery model of the below database to Simple and perform a Checkpoint directly, the Transaction log will be truncated automatically and will be available for reuse as shown below:
将数据库恢复模型更改为简单
例如，如果我们将以下数据库的恢复模型更改为“简单”并直接执⾏检查点，则事务⽇志将被⾃动截断并可以重复使⽤，如下所⽰：
TRUNCATE_ONLY Transaction Log backup option, that breaks the database backup chain and truncates the available Transaction Logs. (Available only prior SQL Server 2008.)
If you try to truncate the Transaction Log of the database using the TRUNCATE_ONLY option in a SQL Server instance on version 2008 and later, the statement will fail with the error message below:
TRUNCATE_ONLY事务⽇志备份选项，它会断开数据库备份链并截断可⽤的事务⽇志。 （ 仅 在以前的 SQL Server 2008中可⽤。
）
如果您尝试在版本2008和更⾼版本SQL Server实例中使⽤TRUNCATE_ONLY选项截断数据库的事务⽇志，则该语句将失败，并显⽰以下错误消息：
交易记录缩⼩ (Transaction Log Shrink)
When the database Transaction Log file is truncated, the truncated space will be freed up and become available for reuse. But the Transaction Log file size will not be decreased, as the truncated space will not be deallocated. On the other hand, the process of recovering the Transaction Log space by deallocating the free VLFs and returning it back to the operating system is called a Transaction Log Shrink. operation.
当数据库事务⽇志⽂件被截断时，截断的空间将被释放并可供重⽤。 但是事务⽇志⽂件的⼤⼩不会减少，因为截断的空间不会被释放。 另⼀⽅⾯，通过释放空闲的VLF并将其返回给操作系统来恢复事务⽇志空间的过程称为事务⽇志收缩 。 操作。
The Transaction Log file shrink operation can be performed only if there is free space on the Transaction Log file, that can be available most of the time after truncating the inactive part of the Transaction Log. A shrink operation will be useful after performing an operation that creates a large number of Transaction Logs.
仅当事务⽇志⽂件上有可⽤空间时，才可以执⾏事务⽇志⽂件收缩操作，该空间在⼤多数情况下会在删除事务⽇志的⾮活动部分之后可⽤。在执⾏创建⼤量事务⽇志的操作之后，收缩操作将⾮常有⽤。
The Transaction Log file of a database can be shrunk by right-clicking on the database and choose t
he Shrink -> Files option from the Tasks menu, as shown below:
可以通过右键单击数据库并从“任务”菜单中选择“收缩”->“⽂件”选项来收缩数据库的事务⽇志⽂件，如下所⽰：
In the Shrink File page, change the File Type to Log, and choose the Transaction Log file that you manage to shrink. In this page, you have three options:
在缩⼩⽂件页⾯中，将⽂件类型更改为Log ，然后选择要缩⼩的事务⽇志⽂件。 在此页⾯中，您有三个选项：
Release unused space in the Transaction Log file to the operating system and shrinks the file to the l
ast allocated extent. This reduces the file size without moving any data
将事务⽇志⽂件中未使⽤的空间释放到操作系统，并将⽂件缩⼩到最后分配的程度。 这样可以在不移动任何数据的情况下减⼩⽂件⼤

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。