楼梯在SQL Server事务日志管理,三级:事务日志,备份和恢复（16周翻译）

楼梯在SQL Server事务日志管理,三级:事务日志,备份和恢复

通过托尼•戴维斯,2011/09/07

该系列

当一切都很顺利,没有需要特别意识到什么事务日志或它是如何工作的。你只需要相信,每个数据库都有正确的备份机制。当事情出错时,事务日志的理解是非常重要的采取纠正行动,特别是在一个时间点恢复的数据库是必需的,紧急! 托尼·戴维斯给正确的细节层次,每一个DBA应该知道。

它不能经常指出,除非您的数据库操作SIMPLE恢复模式,是非常重要的定期备份执行事务日志。这将控制尺寸事务日志,并确保在发生灾难,你可以恢复您的数据库灾难发生之前不久。这些事务日志备份将与定期进行全数据库备份(数据文件)

如果你正在做一个测试系统,你不需要恢复到前一个时间点上,还是很高兴能够恢复到最后全数据库备份,那么您应该运行数据库SIMPLE模式。

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

备份的重要性

考虑,例如,一个SQL Server数据库的情况“崩溃”,也许由于硬件故障,和“活”的数据文件(mdf和ndf文件),连同事务日志文件法律辩护基金(文件),不再是可访问的。

在最坏的情况下,如果没有备份这些文件(副本)的存在,那么你将会受到100%的数据损失。为了确保您可以恢复数据库和恢复数据的存在在某种程度上在服务器崩溃之前,或数据丢失或损坏其他原因之前,DBA需要定期备份的数据和日志文件。

有三个主要类型的备份,DBA可以执行(尽管只有前两个应用时SIMPLE恢复模式):

完整的数据库备份——备份所有的数据在数据库中。这就是制作的一个副本MDF为一个给定的数据库文件(年代)。
微分数据库备份——复制任何数据改变了自上次完全备份。
事务日志备份——复制的所有日志记录插入到自上次事务日志备份事务日志(或数据库检查点,如果在工作SIMPLE恢复模式)。当一个日志备份,日志一般被截断,文件可以被重用的空间,虽然有些因素可以推迟(见水平8 -帮助,我的日志已满)

一些初级dba和许多开发人员,可能误导了“全部”这个词,认为一个完整的数据库备份备份“一切”; 数据和事务日志的内容。这是不正确的。从本质上讲,完全和微分备份只备份数据,尽管他们也足够的事务日志备份到启用备份数据的恢复,和繁殖的任何更改备份是在进步。然而,在实践中,一个完整的数据库备份不备份事务日志,所以不会导致截断事务日志。只有导致截断事务日志备份的日志,所以执行日志备份是唯一正确的方法来控制日志文件的大小,在生产系统。将讨论一些常见的但不正确的方式水平8 -帮助,我的日志已满。

文件和Filegroup备份

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

恢复模型

SQL Server数据库备份和恢复操作的上下文中发生恢复模式的数据库。复苏模型是数据库属性,决定你是否需要(甚至可以)备份事务日志和操作是如何记录的。也有一些差异关于可用的恢复操作,对细粒度的页面和文件恢复,但是我们不会覆盖这些在本系列。

一般操作,将数据库操作SIMPLE或FULL复苏模式,两者之间最重要的区别如下:

简单的——事务日志只是用于数据库恢复和回滚操作。它是自动截断在周期性的检查点。不能备份,因此不能用于恢复数据库状态的存在在某种程度上在过去。
完整的——不会自动截断事务日志在周期性的检查点,所以可以备份和恢复数据到前一个时间点,以及对数据库恢复和回滚。日志文件只是截断日志备份时发生。

还有第三个模式,BULK_LOGGED,在这种特定的操作,通常会产生大量的写入事务日志执行更少的日志记录为了不淹没事务日志。

可以最低限度的操作记录

可以最低限度的操作记录的例子包括批量导入操作(使用,例如,bcp或BULK INSERT),SELECT/INTO操作和某些索引操作,如索引的重建。一个完整的列表可以在这里找到:http://msdn.microsoft.com/en-us/library/ms191244.aspx。

一般来说,一个数据库中运行FULL恢复模式可能暂时转向BULK_LOGGED模式为了运行这些操作以最小的日志,然后转回FULL模式。永久运行在BULK_LOGGED模式不是一个可行的方式来减少事务日志的大小。我们将更详细地讨论这个问题管理登录 BULK LOGGED恢复模式。

选择正确的恢复模型

最重要的选择标准操作数据库FULL复苏模式,SIMPLE模式如下:多少钱你愿意失去的数据?

在SIMPLE恢复模式,只有完整和微分备份是可能的。假设你完全依赖完整的备份,执行一个每天早上2点,致命的崩溃和服务器的经验在一天早上1点。在这种情况下,您可以恢复完整数据库备份在2点之前的早晨,和将失去价值23小时的数据。

可以执行微分备份之间的完整备份,减少数据损失的风险。所有的备份都是I / O密集型进程,但尤其如此,和一定程度上的差异,备份。他们可能会影响数据库的性能,所以不应该运行在用户访问数据库的时候。实际上,如果你在工作SIMPLE恢复模式,你的数据丢失的风险敞口的几个小时。

如果一个数据库业务关键型数据,你更喜欢接触数据丢失以几分钟而不是几小时,那么你将需要操作数据库FULL恢复模式。在这种模式下,你将需要一个完整的数据库备份,紧随其后的是一系列频繁的事务日志备份,紧随其后的是另一个完全备份,等等。

理论上,在这种情况下,您可以恢复最近的,有效的完整备份(+最近的微分备份,如果拍摄),其次是链式可用的日志文件备份,自上次完全或微分备份。在复苏过程中,所有的备份日志文件中记录的操作将向前滚为了将数据库恢复到一个时间点非常接近灾难的时间。

的问题经常备份日志文件如何靠,再一次,你准备失去多少数据,加上你的服务器上的工作负载。在关键的财务或会计应用程序,数据丢失或多或少的零容忍,那么你可能日志备份每15分钟,甚至更频繁。在我们之前的例子中,这意味着您可以恢复2点完全备份,然后应用的每个日志文件,假设你有一个完整的日志链从你使用的完整备份数据库恢复的基础,一个在12.45点,15分钟前崩溃。事实上,如果当前日志崩溃后仍然可以访问,允许您执行尾日志备份,你可以减少数据损失接近于零。

日志链和尾日志备份…

…将详细讨论5级-管理登录 FULL 复苏模型

当然,与FULL复苏是更高的维护开销,额外的努力的创造和监控运行所需的工作非常频繁的事务日志备份,这些备份的I / O资源需要(虽然时间短),和所需的磁盘空间来存储大量的备份文件。由于需要考虑这一点,在业务层面上,在选择合适的恢复模式对于一个给定的数据库。

设置和切换恢复模型

经济复苏模型下面可以设置使用一个简单的命令,如清单3.1所示。

使用主;——设置完全恢复模型改变数据库TestDB集复苏完整的;——设置恢复模型简单改变数据库TestDB集恢复简单;——设置BULK_LOGGED恢复模型改变数据库TestDB集复苏BULK_LOGGED;

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

数据库将采用默认恢复模型指定的model数据库。在许多情况下,这将意味着“默认”恢复数据库模型FULL,但不同版本的SQL服务器可能有不同的默认值模型数据库。

发现经济复苏模型

理论上,我们可以找出哪些模型给定的数据库使用通过执行查询,如清单3.2所示。

选择的名字,recovery_model_desc从sys。数据库在哪里的名字=“TestDB”;去

清单3.2:查询sys.databases恢复模型

然而,小心这个查询,它可能并不总是告诉真相。例如,如果我们创建一个新的数据库,然后立即运行该命令清单3.2中,将报告的数据库FULL恢复模型。然而,事实上,直到一个完整的数据库备份数据库将在auto-truncate操作模式(即。SIMPLE)。

我们可以看到在行动上创建一个新的数据库SQL Server 2008实例,在默认恢复模型FULL。我们与一些测试数据创建一个表,然后检查恢复模型,如清单3.3所示。

/ *第1步:创建数据库* /使用主;如果存在(选择的名字从sys。数据库在哪里的名字=“TestDB”)下降数据库TestDB;创建数据库TestDB在(的名字=TestDB_dat,文件名=“C: Program Files  Microsoft SQL Server  MSSQL10.MSSQLSERVER 该 Data  TestDB.mdf”)日志在(的名字=TestDB_log,文件名=“C: Program Files  Microsoft SQL Server  MSSQL10.MSSQLSERVER 该 Data  TestDB.ldf”);/ *第二步:一百万行插入表* /使用TestDB
去如果OBJECT_ID(“dbo.LogTest”,“U”)是不零下降表dbo。LogTest;选择前1000000SomeID=身份(INT,1,1),SomeInt=腹肌(校验和(NEWID()))%50000年+1,SomeLetters2=字符(腹肌(校验和(NEWID()))%26+65年)+字符(腹肌(校验和(NEWID()))%26+65年),SomeMoney=投(腹肌(校验和(NEWID()))%10000年/100.0作为钱),SomeDate=投(兰德(校验和(NEWID()))*3653.0+36524.0作为DATETIME),SomeHex12=正确的(NEWID(),12)成dbo。LogTest从sys。all_columns ac1交叉加入sys。all_columns ac2;选择的名字,recovery_model_desc从sys。数据库在哪里的名字=“TestDB”;去

名字recovery_model_desc- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -TestDB完整的

清单3.3:一个新创建的TestDB数据库,分配FULL恢复模式

这表明我们在FULL复苏模式,但现在让我们检查日志空间使用,迫使CHECKPOINT,然后再检查日志使用,如清单3.4所示。

DBCCSQLPERF(LOGSPACE);——DBCC SQLPERF报告110 MB的日志文件满约90%检查点去DBCCSQLPERF(LOGSPACE);——DBCC SQLPERF报告100 MB的日志文件满约6%

清单3.4:日志文件被截断在关卡!

注意,日志文件是大致相同的大小,但现在只有6%的完整; 日志截断和可用的空间以便重用。尽管分配给数据库FULL恢复模式,它不是实际操作模式,直到第一个完整数据库备份。有趣的是,这意味着我们可以达到同样的效果,而不是显式地迫使CHECKPOINT的,运行一个完整的备份TestDB数据库。完整的备份操作触发CHECKPOINT和日志截断。

告诉肯定复苏模型操作,执行查询,如清单3.5所示。

选择db_name(database_id)作为“数据库名”,last_log_backup_lsn从主。sys。database_recovery_status在哪里database_id=db_id(“TestDB”);去

数据库名last_log_backup_lsn- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -TestDB零

清单3.5:是数据库真的在FULL恢复模式?

如果一个值NULL出现在last_log_backup_lsn列,那么数据库实际上是在auto-truncate模式,所以数据库检查点发生时将被截断。执行一个完整的数据库备份后,你会发现列填充的LSN日志记录,记录备份操作,此时数据库是真正的FULL恢复模式。从这一点上,一个完整的数据库备份没有影响事务日志; 唯一办法截断日志将备份日志。

切换模式

如果你换一个数据库FULL或BULK LOGGED模式SIMPLE模式,这将打破日志链,你只能恢复数据库的切换之前最后一个日志备份。因此,建议立即进行日志备份之前切换。如果你随后切换数据库回来SIMPLE来FULL或BULK LOGGED模式,记住,数据库会继续操作将显示在auto-truncate模式(清单3.5NULL),直到您执行另一个完整的备份。

如果你从FULL来BULK_LOGGED模式,那么这将不打破日志链。然而,任何批量操作时发生BULK_LOGGED模式不能完全事务日志记录,所以不能控制operation-by-operation基础上,以同样的方式,完全可以登录操作。这意味着恢复一个数据库事务日志中的时间点包含批量操作不可能的。你只能恢复到结束的日志文件。为了“启用”时间点恢复,切换回FULL模式批量操作完成后,立即进行日志备份。

自动化和验证备份

临时数据库和事务日志备份可以通过简单的t - SQL脚本,执行SQL Server Management Studio。然而,对于生产系统,DBA将需要一种方法来自动化这些备份,确认备份是有效的,可以用来恢复您的数据。

全面覆盖这个主题超出了本文的范围,但是下面列出了一些可用的选项。由于一些缺点的地对地导弹维修计划,最有经验的dba将选择编写自己的脚本和自动化。

地对地导弹维修计划向导和设计师——两个工具,内置地对地导弹,它允许您配置和安排一系列核心数据库维护任务,包括完整的数据库备份和事务日志备份。 DBA还可以运行DBCC完整性检查,安排工作要删除旧的备份文件,等等。一个很好的描述这些工具,及其局限性,可以在布莱德McGhee找到的书,布拉德的确定指南SQL Server维护计划
t - sql脚本——你可以编写自定义t - sql脚本来自动化备份任务。一套完善的和尊重的维护提供的脚本Ola Hallengren。他的脚本创建一个各种各样的存储过程,每个执行一个特定的数据库维护任务,包括备份、自动使用SQL代理工作。理查德·Waymire的楼梯SQL Server代理是一个很好的关于这个主题的信息来源。
Powershell / SMO脚本——更加强大和灵活的t - sql脚本,但许多dba陡峭的学习曲线,可以使用Powershell脚本和自动化几乎所有维护任务。看到的,例如:http://www.simple-talk.com/author/allen-white/。
第三方备份工具——存在一些第三方工具可以自动备份,以及验证和监控。大多数提供备份压缩和加密以及附加功能缓解备份管理,验证备份,等等。例子包括红门的SQL备份,追求LiteSpeed,等等。