SQL Server备份–模型和类型

SQL Server backups provide the possibility to recover a damaged database or to restore a database to a point-in-time. There are a number of scenarios which can cause database corruption or inaccurate data which require data recovery. However, each environment, in which SQL Server operates, may require a different backup and recovery strategy, adapted to available resources. The strategy should minimize data loss possibilities and maximize data availability at the same time, while considering specific enterprise requirements (e.g. which and how much data should be backed up, how long backups should be retained, etc.).

SQL Server备份提供了恢复损坏的数据库或将数据库还原到某个时间点的可能性。 在许多情况下，可能会导致数据库损坏或数据不正确，从而需要恢复数据。 但是，每个运行SQL Server的环境都可能需要适用于可用资源的不同备份和恢复策略。 该策略应在考虑特定企业要求（例如，应备份哪些数据以及应备份多少数据，应保留多长时间的备份等）的同时，最小化数据丢失的可能性并同时最大化数据可用性。

SQL Server数据库恢复模型 (SQL Server database recovery models)

When it comes to SQL Server backups, it is not possible to plan a backup strategy without fully understanding database recovery models. The recovery model define how SQL Server logs transactions performed on a database, which determines available backup operation types for the database backup process.

对于SQL Server备份，如果不完全了解数据库恢复模型，则无法计划备份策略。 恢复模型定义SQL Server如何记录在数据库上执行的事务，从而确定数据库备份过程的可用备份操作类型。

The database recovery model option is available in the Database properties dialog, within the Options page. There are three available recovery models: Full, Bulk-logged, and Simple.

数据库恢复模型选项在“ 选项”页面内的“ 数据库属性”对话框中可用。 共有三种可用的恢复模型： 完整，大容量日志记录和简单 。

SQL Server system databases are specific regarding recovery models. For example, master, msdb, and tempdb databases are tied to the Simple recovery model, while the model database can use any of the recovery models. Note that the recovery model used for the model database determines what recovery model will be used for newly created user databases by default.

SQL Server系统数据库特定于恢复模型。 例如， master，msdb和tempdb数据库与简单恢复模型绑定在一起，而模型数据库可以使用任何恢复模型。 请注意，用于模型数据库的恢复模型默认情况下将确定将哪种恢复模型用于新创建的用户数据库。

完全恢复模式 (Full recovery model)

If the Full recovery model is set for a database, all transactions are fully logged into the database transaction log. The Full recovery model offers the widest flexibility for backup strategies.

如果为数据库设置了完全恢复模型，则所有事务都将完全记录到数据库事务日志中。 完全恢复模型为备份策略提供了最大的灵活性。

批量记录恢复模型 (Bulk-logged recovery model)

The Bulk-logged recovery model is very similar to the Full recovery model with the difference in the way some specific operations are logged into a database transaction log. Such operations are minimally logged, resulting in a reduced disk space usage by the database transaction log. Other transactions are fully logged. In addition, this recovery model improves SQL Server performance comparing to the Full recovery model since only the extent allocations are logged. However, there is an important downside of this recovery model. There is an increased data loss risk since it’s not possible to perform a database point-in-time recovery if a transaction log backup that contains bulk-logged operations is used for recovery. If that’s the case, the whole transaction log backup must be restored.

批量记录恢复模型与完全恢复模型非常相似，区别在于某些特定操作被记录到数据库事务日志中的方式不同。 最少记录了此类操作，从而减少了数据库事务日志的磁盘空间使用量。 其他事务已完全记录。 此外，由于该恢复模型仅记录扩展区分配 ，因此与完全恢复模型相比，它提高了SQL Server性能。 但是，此恢复模型有一个重要的缺点。 由于如果使用包含大容量日志记录操作的事务日志备份进行恢复，则无法执行数据库时间点恢复，因此存在数据丢失风险增加的风险。 在这种情况下，必须还原整个事务日志备份。

The following operations are considered as bulk operations and are minimally logged when using the Bulk-logged recovery model: BULK INSERT, operations executed via BCP command prompt utility, partial updates to large data types using the .WRITE clause, CREATE INDEX, ALTER INDEX REBUILD, DROP INDEX (in case a heap rebuild is required), and OPENROWSET with the BULK rowset provider

以下操作被视为批量操作，并且在使用批量记录的恢复模型时最少记录：BULK INSERT，通过BCP命令提示符实用程序执行的操作，使用.WRITE子句，CREATE INDEX，ALTER INDEX REBUILD对大型数据类型的部分更新，DROP INDEX（如果需要重建堆）和带有BULK行集提供程序的OPENROWSET

简单恢复模型 (Simple recovery model)

The Simple recovery model should not be used in production environments where database recovery using transaction log backups may be required. If used, this kind of recovery model does not allow creation of transaction log backups. This is caused by the fact that the transaction log holds only the records which describe applied changes until a checkpoint has occurred, and the data is written to disk.

在可能需要使用事务日志备份进行数据库恢复的生产环境中，不应使用简单恢复模型。 如果使用此类型的恢复模型，则不允许创建事务日志备份。 这是由于以下事实引起的：事务日志仅保留描述已应用更改的记录，直到发生检查点，然后将数据写入磁盘为止。

While not being suitable for production databases, this recovery model, on the other hand, provides maximum SQL Server performance gain of all three recovery models.

另一方面，虽然此恢复模型不适用于生产数据库，但可以在所有三种恢复模型中最大程度地提高SQL Server性能。

Note that the combination of the Full and Bulk-logged recovery models can be used. While using the Full recovery model, you can switch to Bulk-logged (e.g. prior to executing large bulk operations), and then switch back to Full. However, you should backup the database transaction log each time prior to changing the recovery model. This method improves SQL Server performance during a bulked operation execution, decreases required disk space needed for transaction log backups, and provides higher number of transaction log backups that can be used for a point-in-time recovery.

请注意，可以使用完全和大容量日志记录恢复模型的组合。 在使用完全恢复模型时，您可以切换到大容量日志记录（例如，在执行大批量操作之前），然后切换回完整模式。 但是，您应该在更改恢复模型之前每次备份数据库事务日志。 此方法在批量操作执行期间提高了SQL Server性能，减少了事务日志备份所需的磁盘空间，并提供了可用于时间点恢复的大量事务日志备份。

SQL数据库备份类型 (SQL database backup types)

There are several SQL Server database backup types. A database backup can, but does not have to, contain entire database. This depends on the backup type and your backup strategy which can involve different backup types. It’s recommended to start any backup strategy by creating a full database backup.

有几种SQL Server数据库备份类型。 数据库备份可以但不必包含整个数据库。 这取决于备份类型和可能涉及不同备份类型的备份策略。 建议通过创建完整的数据库备份来启动任何备份策略。

完整SQL数据库备份 (Full SQL database backup)

A full backup type holds the entire database and the part of the transaction log containing uncommitted transactions that will be rolled back or replayed during the recovery process in order to make data consistent upon restore. This is required as SQL Server does not pause activities in the database while it is being backed up, which means that the database can be modified during the backup process.

完全备份类型包含整个数据库和事务日志的一部分，其中包含未提交的事务，这些事务将在恢复过程中回滚或重播，以使数据在还原时保持一致。 这是必需的，因为SQL Server在备份时不会暂停数据库中的活动，这意味着可以在备份过程中修改数据库。

The full backup serves as the starting point for subsequent differential and transaction log backups. Moreover, differential and transaction log backups cannot be created if the full database backup has never been created.

完整备份是后续差异备份和事务日志备份的起点。 此外，如果从未创建完整的数据库备份，则无法创建差异和事务日志备份。

A typical scenario for a full database backup creation is once per day, followed by transaction log backups during the day. In case full database backups are large, full backups should be taken in larger time frames (e.g. once per week), and supplemented with differential backups the rest of the time. Whatever strategy you choose, it starts with the full database backup.

创建完整数据库备份的典型方案是每天一次，然后是一天中的事务日志备份。 如果完整的数据库备份很大，则应在较大的时间范围内（例如，每周一次）进行完整备份，并在其余时间补充差异备份。 无论选择哪种策略，都将从完整数据库备份开始。

Note that SQL Server backs up only the active/used data pages, meaning the backup can be significantly smaller than the actual online database.

请注意，SQL Server仅备份活动/已使用的数据页，这意味着备份可能比实际的在线数据库小得多。

差异SQL数据库备份 (Differential SQL database backups)

In case only the changes applied after the last full backup was taken need to be backed up, use the differential database backup type. This is exactly what a differential database backup contains. It uses a bitmap page where each bit contains information about every extent in order to track the changes.

如果仅需要备份上次完整备份后应用的更改，请使用差异数据库备份类型。 这正是差异数据库备份所包含的内容。 它使用位图页面，其中每个位包含有关每个范围的信息，以便跟踪更改。

Each differential backup contains all changes that occurred since the last full backup. This means that other previously created differential backups may be considered as obsolete. Also, in case of databases with a large number of changes, differential backups can easily become larger than the database itself. To overcome this and to benefit from the use of differential backups, it’s recommended to take full backups in intervals short enough to keep differential backups smaller than the full backup.

每个差异备份包含自上次完整备份以来发生的所有更改。 这意味着其他先前创建的差异备份可能被认为已过时。 同样，在数据库进行大量更改的情况下，差异备份很容易变得比数据库本身更大。 为了克服这个问题并从差异备份中受益，建议以足够短的间隔进行完整备份，以使差异备份小于完整备份。

Differential backups are recommended for databases with infrequent changes. It’s recommended to start with the full database backup followed by scheduled transaction log and differential backups. So, in case the recovery is needed, it’s enough to recover only the full backup, the last differential backup, and transaction log backups created after the differential backup up to the required point in time.

对于不经常更改的数据库，建议使用差异备份。 建议从完整的数据库备份开始，然后再安排计划的事务日志和差异备份。 因此，在需要恢复的情况下，仅恢复完整备份，最后的差异备份以及差异备份之后创建的事务日志备份就足够了，直到所需的时间点为止。

SQL Server事务日志备份 (SQL Server Transaction log backups)

Transaction log backups provide maximum data protection in case of a database disaster. These backups should be used along with the full and bulk-logged backups. Along with the data protection, transaction log backups allow transaction log to be truncated enabling reuse of unused space for new transactions. In case the transaction log has never been backed up, it can grow until it occupies free disk space.

事务日志备份可在数据库灾难时提供最大的数据保护。 这些备份应与完整备份和批量记录备份一起使用。 除数据保护外，事务日志备份还允许截断事务日志，从而可以将未使用的空间重新用于新事务。 万一从未备份过事务日志，它可以增长直到占用可用磁盘空间。

Note that you cannot create a transaction log backup if the database uses the Simple recovery model. In such case, SQL Server truncates the log on checkpoints automatically.

请注意，如果数据库使用简单恢复模型，则无法创建事务日志备份。 在这种情况下，SQL Server会自动截断登录检查点。

A transaction log backup contains only transactions that occurred after the last transaction log backup was taken. A sequence of transaction log backups taken one after another, after the full of bulk-logged database backup creates a full log chain. The chain allows database restoration to a point-in-time, presuming the last transaction log backup doesn’t contain any of minimally logged operations.

事务日志备份仅包含上次执行事务日志备份之后发生的事务。 在完整的大容量日志记录数据库备份之后，依次执行一系列事务日志备份，从而创建了完整的日志链。 该链允许将数据库还原到某个时间点，并假定最后的事务日志备份不包含任何最少记录的操作。

If, for any reason (e.g. a corrupted backup, missing backup file, or used Simple recovery model in the log chain time frame) the chain is broken, later transactions in the chain cannot be recovered.

如果由于任何原因（例如，备份损坏，备份文件丢失或日志链时间范围内使用的简单恢复模型）导致链中断，则无法恢复链中的后续事务。

翻译自: https://www.sqlshack.com/sql-server-backup-models-types/