第十六周翻译

此翻译原文来自：http://www.sqlservercentral.com/articles/Stairway+Series/73779/

 

楼梯到事务日志管理SQL Server，3级：事务日志、备份和恢复

由汤尼戴维斯，2011 / 09 / 07

该系列

这篇文章是楼梯系列的一部分：在SQL Server事务日志管理的阶梯

当事情进展顺利，没有需要特别在意日志或它是如何工作的交易。你只需要相信，每一个数据库已经有正确的备份制度。当事情出错时，了解事务日志是重要的采取纠正措施，特别是在一个时间点恢复数据库是必需的，迫切的！Tony Davis只给出正确的细节层次，每个DBA都应该知道。

这不能说太多，除非你的数据库操作简约恢复模式，这是非常重要的定期备份在交易完成的日志。这将控件的大小事务日志中，并确保在灾难发生时，你可以恢复你的数据库有点前不久，灾难发生了。这些事务日志备份将一起进行定期的完整数据库（数据文件）的备份

如果你工作在一个测试系统，你不需要恢复到之前的某个时间点，还是很高兴能够恢复到上次完整数据库备份，那么你应该对数据库的操作简约模式。

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

备份的重要性

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

在最坏的情况下，如果没有备份（复制）这些文件存在于其他地方，那么你将遭受数据损失100%。为了确保您能恢复数据库数据恢复它的存在在一些点在服务器崩溃，或在数据丢失或损坏等原因，DBA需要定期备份的数据和日志文件。

有备份，DBA可以执行三个主要类型（虽然只有前两申请时简约恢复模式）：

全部数据库备份–备份数据库中的所有数据。这基本上是复制的中密度纤维板文件对于一个给定的数据库。

• 备份的差异–自上次完全备份开始，复制任何数据都改变了。
• 事务日志备份–使复制所有日志记录的事务日志插入自上次事务日志备份（或数据库检查点，如果工作简约恢复模式）。当一个日志备份，日志通常地被截断在文件空间可以重复使用，虽然有些因素可以延缓（见8级–的帮助下，我的日志已满）

一些初级DBA和许多开发商，可能误导的“全”，认为一个完整数据库备份的“一切”；无论是数据和事务日志的内容。这是不正确的。从本质上讲，两者的完整备份和差异备份只备份数据，虽然他们也回到了足够的事务日志，使备份数据恢复，再现在备份过程中所做的任何更改。然而，在实际应用中，一个完整的数据库备份不会备份事务日志，因此不会导致截断事务日志。只有一个事务日志备份中的日志截断的结果，所以执行日志备份控制日志文件的大小的唯一正确的方法，在生产系统。一些常见的但不正确的方式将讨论在8级–的帮助下，我的日志已满。

文件和文件组备份

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

恢复模式

SQL Server数据库的备份和还原操作发生的上下文内，数据库恢复模式。恢复模式是数据库的性质是决定你是否需要（或可以）备份事务日志和操作日志。也有关于恢复，可操作的一些差异，对于颗粒的网页和文件恢复，但我们将不涉及这些本系列。

在一般的操作，数据库将在任何操作简约或全部恢复模式和两者之间最重要的区别如下：

• 简单的–事务日志仅用于数据库恢复和回滚操作。它是自动截断在定期检查。不能备份，所以不能用于恢复数据库的状态存在，在过去某个时间。
• 全部的–事务日志不会自动截断在定期检查等都可以备份和恢复数据使用前一个时间点，以及对数据库的恢复和回滚。日志文件只有截断日志备份发生时。

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

操作可以最小日志记录

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

一般来说，一个数据库运行在全部恢复模式可以暂时切换到散装_登录模式以经营此类业务的最小日志记录，然后转换回全部模式。运行永久散装_登录模式不是减少交易规模的一种可行方法的日志。我们将更详细地讨论这个管理登录大容量日志恢复模式。

选择正确的恢复模式

压倒一切的标准操作数据库之间的选择全部恢复模式简约方式如下：有多少数据你愿意冒风险吗？

进入简约恢复模式，只有完整备份和差异备份是可能的。让我们说你完全依靠全备份，执行一个每天早上凌晨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显示无效的直到你做一次全备份）。

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

自动化和验证备份

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

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

• 系统维护计划向导和设计器–两个工具，内置的反舰导弹，这允许您配置和调度等一系列核心数据库的维护任务，包括完整的数据库备份和事务日志备份。DBA还可以运行DBCC完整性检查、安排作业，删除旧的备份文件，等等。这些工具的一个极好的描述，和其局限性，可以在Brad McGhee的书中找到的，Brad确定SQL Server维护计划向导
• T-SQL脚本–你可以编写自定义的T-SQL脚本来自动备份任务。一个完善的和受尊敬的维护脚本是由奥拉hallengren。他的脚本创建各种存储过程，每完成一个特定的数据库维护任务，包括备份和自动使用SQL代理作业。Richard WaymireSQL Server代理楼梯是一个很好的关于这一主题的信息源。
• PowerShell / SMO脚本–更强大和灵活的比T-SQL脚本，但许多DBA陡峭的学习曲线，PowerShell脚本和自动化可用于几乎任何维护任务。看，例如：http://www.simple-talk.com/author/allen-white/。
• 第三方备份工具–几个第三方工具的存在，可以自动备份，以及验证和监控。大多数提供的备份压缩和加密以及附加功能来减轻备份管理，验证备份，等等。例子包括红门的SQL备份任务的LiteSpeed，等等。