翻译（16w）

SQL Server中事务日志管理，级别3：事务日志、备份和恢复

Tony Davis，2011／09／07

该系列

本文是楼梯系列的一部分：SQL Server中事务日志管理的阶梯。

当事情进行顺利时，没有必要特别注意事务日志是如何工作的，或者它是如何工作的。您只需要确信每个数据库都有正确的备份机制。当事情出错时，理解事务日志对于采取纠正措施是非常重要的，尤其是在需要及时更新数据库时！Tony Davis给出了每个DBA应该知道的正确级别。

不能经常说明，除非数据库以简单的恢复模式运行，否则在事务日志上执行定期备份是非常重要的。这将控制事务日志的大小，并确保在发生灾难时，您能够在灾难发生前不久将数据库恢复到某个点。这些事务日志备份将与常规的完整数据库（数据文件）备份一起执行。

如果您正在测试系统，您不需要恢复到以前的时间点，或者很高兴能够恢复到最后一次完整的数据库备份，那么您应该以简单模式操作数据库。

让我们更详细地讨论这些问题。

备份的重要性

考虑，例如，在这情况一个SQL Server数据库“崩溃”，可能是由于硬件故障，和“活”的数据文件（MDF和NDF文件），随着事务日志文件（mdf文件），不再访问。

在最坏的情况下，如果在其他地方没有这些文件的备份（副本），那么您将遭受100%的数据丢失。为了确保在服务器崩溃前某个时间点恢复数据库并恢复数据，或者由于其他原因导致数据丢失或损坏之前，DBA需要定期备份数据和日志文件。

DBA可以执行三种主要备份类型（虽然只有在简单恢复模式下才使用前两种）：

完整的数据库备份——备份数据库中的所有数据。这实质上是为给定数据库制作一个MDF文件的副本。

差异化数据库备份——复制自上次完整备份以来更改过的任何数据的副本。

事务日志备份-自上次事务日志备份（或数据库检查点，如果在简单恢复模式下工作），插入所有插入到事务日志中的日志记录的副本。当进行日志备份时，日志通常会被截断，以便文件中的空间可以被重用，尽管有些因素可能会延迟这一点（请参阅第8级-帮助，我的日志已满）。

一些初级DBA和许多开发商，可能误导的“全”，认为一个完整数据库备份的“一切”；无论是数据和事务日志的内容。这是不对的。基本上，完整备份和差异备份只备份数据，虽然它们也备份足够的事务日志，以便恢复备份数据，并复制备份期间正在进行的任何更改。然而，实际上，完整的数据库备份不会备份事务日志，因此不会导致事务日志的截断。只有事务日志备份导致截断日志，因此在生产系统中执行日志备份是控制日志文件大小的惟一正确方法。一些常见但不正确的方法将在第8级讨论——帮助，我的日志已经满了。

文件和文件组备份

大型数据库有时被组织成多个文件组和进行全微分的个人文件组备份这是可能的，在这些文件组或文件，而不是整个数据库。这个话题在这个楼梯上不会进一步讨论。

恢复模式

SQLServer数据库备份和恢复操作发生在该数据库的恢复模型的上下文中。恢复模型是一个数据库属性，它决定是否需要（甚至可以）备份事务日志以及如何记录操作。在还原性操作方面也有一些不同，关于粒状页面和文件恢复，但我们不在本系列中讨论这些操作。

在一般操作中，数据库将以简单或完全恢复模式运行，两者之间最重要的区别如下：

简单——事务日志仅用于数据库恢复和回滚操作。它在周期检查点自动截断。它不能被备份，因此不能用于将数据库恢复到过去某个时候存在的状态。

完整——事务日志不会在周期检查点自动截断，因此可以备份并用于将数据恢复到以前的时间点，以及用于数据库恢复和回滚。日志文件只有在日志备份发生时才被截断。

还有一个第三模式，bulk_logged，在一定的操作，通常会产生大量的写入事务日志记录执行少为了不让事务日志。

可以最小化日志记录的操作

可以最小化日志记录的操作包括批量导入操作（例如，使用BCP或大容量插入）、选择/进入操作和某些索引操作，如索引重建。一个完整的列表可以在这里找到：http://msdn.microsoft.com/en-us/library/ms191244.aspx。

一般来说，一个数据库完整恢复模式下运行可暂时切换到bulk_logged模式来运行这些操作以最小的记录，然后切换到完整模式。在bulk_logged永久运行模式不是减少交易规模的一种可行方法的日志。我们将更详细地讨论日志管理中的日志记录恢复模式。

选择正确的恢复模式

在完全恢复模式和简单模式下操作数据库的首要标准如下：您愿意冒多少风险？

在简单恢复模式下，只有完全和差异备份是可能的。让我们说你完全依靠全备份，执行一个每天早上凌晨2点，和服务器经历致命的撞车事故在凌晨一点的一个早晨。在这种情况下，你将能够恢复了前一天凌晨两点的完整数据库备份，并将失去23个小时的数据。

可以在完整备份之间执行差异备份，以减少丢失风险的数据量。所有备份都是I/O密集型进程，但对于完整和较小程度的差异，备份尤其如此。它们很可能影响数据库的性能，因此在用户访问数据库时不应运行数据库。实际上，如果您在简单恢复模式下工作，那么您暴露于数据丢失风险的时间将是几个小时。

如果一个数据库保存了业务关键数据，您希望您的数据丢失暴露在几分钟内而不是几小时内，那么您将需要在完全恢复模式下操作数据库。在这种模式下，您需要进行完整的数据库备份，然后是一系列频繁的事务日志备份，然后是另一个完整备份，等等。

在这种情况下，从理论上讲，您可以还原最近的、有效的完整备份（加上最近的差异备份，如果采取的话），接着是可用的日志文件备份链，这是自上次完整备份或差异备份以来的备份。然后，在恢复过程中，备份日志文件中记录的所有操作将被向前滚动，以便在灾难发生时将数据库恢复到一个时间点。

备份日志文件的频率的问题将再次取决于您准备丢失多少数据，以及服务器上的工作量。在关键的财务或会计应用程序中，对数据丢失的容忍度或多或少是零，那么您可能每隔15分钟就进行一次日志备份，或者更频繁地进行日志备份。在前面的例子中，这就意味着你可以恢复完整备份，然后将每个凌晨2点在打开日志文件，假设你有一个从你使用的数据库为基础的全备份恢复完整的日志链延伸，到一个拍摄结束后凌晨45分时，飞机坠毁前15分钟。事实上，如果当前日志在崩溃后仍然可访问，允许您执行尾部日志备份，则可以将数据丢失减至接近零。

日志链和尾部日志备份…

将在第5级中详细讨论-管理日志中的完全恢复模式

当然，完全恢复会带来更高的维护开销，这是为了创建和监视运行频繁的事务日志备份所需的额外工作，这些备份需要的I/O资源（尽管时间很短），以及存储大量备份文件所需的磁盘空间。在选择给定数据库的适当恢复模式之前，需要在业务级别上适当考虑这一点。

设置和切换恢复模型

恢复模型可以使用清单3.1所示的以下简单命令中的一个来设置。

USE master;

-- set recovery model to FULLALTER DATABASE TestDBSET RECOVERY FULL;

-- set recovery model to SIMPLEALTER DATABASE TestDBSET RECOVERY SIMPLE;

-- set recovery model to BULK_LOGGEDALTER DATABASE TestDBSET RECOVERY BULK_LOGGED;

清单3.1：设置数据库恢复模型

数据库将采用模型数据库指定的默认恢复模型。在许多情况下，这意味着数据库的默认恢复模型是满的，但是不同版本的SQL Server对模型数据库可能有不同的默认值。

发现恢复模型

从理论上讲，我们可以通过执行清单3.2所示的查询来找出给定数据库正在使用的模型。

SELECT   name ,

         recovery_model_descFROM     sys.databasesWHERE    name = 'TestDB' ;

GO

清单3.2：查询sys.databases的恢复模式

但是，要小心这个查询，因为它可能并不总是讲真话。例如，如果我们创建一个全新的数据库，然后立即运行清单3.2中的命令，它将报告数据库处于完全恢复模式中。然而，事实上，在进行完整的数据库备份之前，数据库将以自动截断模式运行（即简单）。

我们可以通过在SQL Server 2008实例上创建一个新的数据库来实现这一点，默认的恢复模型已经满了。我们用一些测试数据创建一个表，然后检查恢复模型，如清单3.3所示。

/* STEP 1: CREATE THE DATABASE*/USE master ;

IF EXISTS ( SELECT  name

            FROM    sys.databases

            WHERE   name = 'TestDB' ) 

    DROP DATABASE TestDB ;

CREATE DATABASE TestDB ON(

  NAME = TestDB_dat,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.mdf') LOG ON(

  NAME = TestDB_log,

  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\TestDB.ldf') ;

/*STEP 2: INSERT A MILLION ROWS INTO A TABLE*/USE TestDB

GOIF OBJECT_ID('dbo.LogTest', 'U') IS NOT NULL 

    DROP TABLE dbo.LogTest ;SELECT TOP 1000000

  SomeID = IDENTITY( INT,1,1 ),

  SomeInt = ABS(CHECKSUM(NEWID())) % 50000 + 1 ,

  SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65)

  + CHAR(ABS(CHECKSUM(NEWID())) % 26 + 65) ,

  SomeMoney = CAST(ABS(CHECKSUM(NEWID())) % 

                     10000 / 100.0 AS MONEY) ,

  SomeDate = CAST(RAND(CHECKSUM(NEWID()))

                   * 3653.0 + 36524.0 AS DATETIME) ,

  SomeHex12 = RIGHT(NEWID(), 12)INTO    dbo.LogTestFROM    sys.all_columns ac1

        CROSS JOIN sys.all_columns ac2 ;

SELECT   name ,

         recovery_model_descFROM     sys.databasesWHERE    name = 'TestDB' ;

GO

 

name                    recovery_model_desc-------------------------------------------

TestDB                  FULL

清单3.3：新创建的库数据库，指定完整恢复模式

这表明我们在完整恢复模式，但现在让我们检查日志空间的使用，力的一个检查站，然后检查日志的使用，如清单3.4所示。

DBCC SQLPERF(LOGSPACE) ;-- DBCC SQLPERF reports a 110 MB log file about 90% full

CHECKPOINT

GO

DBCC SQLPERF(LOGSPACE) ;-- DBCC SQLPERF reports a 100 MB log file about 6% full

清单3.4：日志文件被截断在检查点上！

请注意，日志文件的大小大致相同，但现在只有6%个完整；日志已被截断，空间可供重用。尽管数据库被分配到完全恢复模式，但在第一个完整数据库备份之前，它实际上不在这种模式下工作。有趣的是，这意味着我们可以取得相同的效果，而不是强制检查点，运行库的完整数据库备份。完整备份操作触发一个检查点，日志被截断。

要确定运行中的恢复模式，执行清单3.5所示的查询。

SELECT   db_name(database_id) AS 'DatabaseName' ,

         last_log_backup_lsnFROM     master.sys.database_recovery_statusWHERE    database_id = db_id('TestDB') ;

GO

 

DatabaseName                last_log_backup_lsn-----------------------------------------------

TestDB                      NULL

清单3.5：数据库是否处于完全恢复模式？

如果空值出现在last_log_backup_lsn列，则数据库实际上是自动截断模式，所以将截断数据库检查点发生时。在执行完整数据库备份，你会发现有日志记录的LSN记录备份操作的列，在这一点上，数据库是真正在完整恢复模式。从这一点开始，完整的数据库备份将不会对事务日志产生影响；截断日志的唯一方法是备份日志。

转换模型

如果您将数据库从完整或大容量日志模式切换到简单模式，这将破坏日志链，并且只有在切换之前的最后一次日志备份时才能够恢复数据库。因此，建议在切换前立即进行日志备份。如果随后将数据库从简单返回到完整或大容量日志模式，请记住，在执行另一个完整备份之前，数据库将继续以自动截断模式运行（清单3.5将显示null）。

如果你从全bulk_logged模式并不会破坏日志链。然而，任何批量操作发生在bulk_logged模式不会被完全记录在事务日志中，因此不能通过操作基础操作控制，以同样的方式，完整记录操作。这意味着不可能将数据库恢复到包含大宗操作的事务日志中的某个时间点。您只能恢复到该日志文件的结尾。为了“重新启用”点恢复，在批量操作完成后切换回完整模式，并立即进行日志备份。

自动化和验证备份

Ad Hoc网络数据库和事务日志备份可以通过简单的T-SQL脚本执行的SQL服务器管理工作室。然而，对于生产系统，DBA需要一种自动化这些备份的方法，并验证备份是否有效，并可用于恢复数据。

对这个主题的全面报道超出了本文的范围，但是下面列出了一些可用的选项。由于一些系统维护计划的缺点，最有经验的数据库管理员会选择自己编写脚本和自动化。

系统维护计划向导和设计器–两个工具，内置的反舰导弹，这允许您配置和调度等一系列核心数据库的维护任务，包括完整的数据库备份和事务日志备份。DBA还可以运行DBCC完整性检查、安排作业，删除旧的备份文件，等等。这些工具的一个极好的描述，和其局限性，可以在Brad McGhee的书中找到，布拉德确定SQL Server维护计划向导

–T-SQL脚本可以编写自定义的T-SQL脚本来自动备份任务。一个完善的和受尊敬的维护脚本是由欧拉hallengren提供。他的脚本创建各种存储过程，每个执行一个特定的数据库维护任务，包括备份，并自动使用SQL代理作业。Richard Waymire的楼梯到SQL Server代理是一个很好的关于这一主题的信息源。

PowerShell脚本/ SMO比T-SQL脚本更加强大和灵活的–，但许多DBA陡峭的学习曲线，PowerShell脚本和自动化可用于几乎任何维护任务。看到的，例如：HTTP：/ / www.simple-talk。COM /作者/艾伦/白。

第三方备份工具——可以自动备份的第三个方工具，以及验证和监视它们。大多数提供备份压缩和加密以及附加功能，以便于备份管理、验证备份等。例子包括红门的SQL备份任务的LiteSpeed，等等。

资源:

tlogstairway_level3.sql

本文是SQL Server楼梯中事务日志管理的阶梯的一部分。

注册到我们的RSS提要并在我们发布一个新级别的楼梯时得到通知！RSS

本文链接：

http://fanyi.baidu.com/#en/zh/