SQL大型事务日志备份与修复问题

问:我在备份时注意到一些异常现象,希望您能解释一下。我们需要经常备份 62GB 的生产数据库,以刷新应用程序开发人员所使用的数据。我们总是先删除旧副本然后再还原新副本。还原的副本与生产数据库大小相同,而且数据看起来也一样,但是与备份过程相比,还原过程所需的时间要长很多。这是怎么回事?为什么还原比备份需要更长的时间?

答:其实这不属于异常现象。根据您所处的环境,这种现象通常是预料之中的。备份和还原所需的时间之所以不同,是由于每个流程必须执行的步骤不同。

备份数据库包含两个步骤。主要就是对数据库执行 IO 读取操作以及对备份设备执行 IO 写入操作:

备份步骤 1 读取数据文件中所有分配的数据,然后将其写入备份设备。

备份步骤 2 读取某些事务日志,然后将其写入备份设备。

所需的事务日志数量可能会差异很大,但其数量一定能将还原的数据库恢复到相同的时间点

而还原数据库最多可能包含四个步骤。涉及的工作要比读写 IO 复杂得多:

还原步骤 1 如果数据库文件不存在,则创建它们。

还原步骤 2 从备份中读取所有数据和事务日志,然后将其写入相关的数据库文件。

还原步骤 3 对事务日志运行恢复过程的“重做”阶段。

还原步骤 4 对事务日志运行恢复过程的“撤消”阶段。

两个备份步骤所需时间与还原步骤 2 所需时间大致相同(假定硬件配置类似并且服务器上没有用户活动)。如果数据文件较大并且需要进行零初始化(这在 SQL Server 2000 中是需要执行的操作,在 SQL Server 2005 中是默认操作),则还原步骤 1 可能需要较长时间。

为避免花费较长时间,请不要在开始还原之前删除现有文件。或者,也可以启用即时初始化,以便快速创建这些文件(有关详细信息,请访问 msdn.microsoft.com/­library/ms175935.aspx)。

还原步骤 3 和 4 是对还原的数据库进行恢复,以便确保事务一致性;此流程与崩溃恢复期间对数据库执行的操作流程相同。恢复操作所需时间取决于需要处理的事务日志量。例如,如果在进行备份时恰好有一个长时间运行的事务处于活动状态,则该事务的所有事务日志都会被备份进来,因此届时不得不进行回滚。

问:我打算在日志传送和数据库镜像之间选择一种提供生产数据库冗余副本的方法。我很担心需要在两个服务器之间传送的事务日志数量,尤其是对于我们每晚都需要执行的索引重建操作。听说镜像功能发送的是实际的重建命令而非事务日志,而重建是在镜像中完成的。真的是这样吗?如果是这样,那么对于 BULK_LOGGED 恢复模式,镜像解决方案要优于日志传送,对吗?

答:您听说的观点是错误的。数据库镜像过程是通过将实际的事务日志记录从主体数据库发送到镜像服务器来完成的,这些记录在镜像数据库中将被“重播”。对于镜像的数据库,既不存在任何类型的转换或筛选,也不存在任何类型的 T-SQL 命令拦截。

数据库镜像仅支持 FULL 恢复模式,这意味着始终会完全记录索引重建操作。根据涉及的索引大小的不同,这可能意味着会生成大量事务日志,从而导致主体数据库的日志文件很大,在将日志记录发送到镜像时需要占用大量的网络带宽。

您可以将数据库镜像视为实时日志传送(实际上,这正是早期在 SQL Server 2005 开发期间该功能所使用的名称)。在日志传送过程中,主数据库的事务日志备份会定期传送到辅助服务器上,并在辅助数据库中进行还原。

日志传送功能支持 FULL 和 BULK_LOGGED 恢复模式。对于使用 FULL 恢复模式在日志传送数据库中所执行的索引重建操作,生成的事务日志量将与镜像数据库中生成的数量完全相同。但是,在日志传送数据库方案中,数据是以日志备份(或系列日志备份)而非连续流的形式发送到冗余数据库的。

如果在索引重建完毕后在日志传送数据库中使用 BULK_LOGGED 恢复模式,则只会生成少量的事务日志。但是在下次事务日志备份时,还将会包含被所记录的最低限度索引重建操作改变的全部数据文件范围。这意味着无论是纳入在 BULK_LOGGED 恢复模式下重建的索引的日志备份还是纳入在 FULL 恢复模式下重建的索引的日志备份,其大小都几乎完全相同。

因此,对于镜像数据库与日志传送数据库中的索引重建而言,需要发送到冗余数据库的信息量几乎完全相同。实际的差别仅在于发送信息的方式 — 是连续发送还是成批发送。

在这两种方法之间进行选择时需要考虑许多其他因素(因素太多,仅在一次 SQL 问题解答中无法全部讨论)。您应该先了解所有这些因素与您的需求的关联程度(例如,可接受的数据丢失限制和允许的停机时间),然后再做决定。

时间: 2016-04-28

SQL大型事务日志备份与修复问题的相关文章

SQL SERVER完整、差异和事务日志备份及还原(脚本和GUI实现)

原文:SQL SERVER完整.差异和事务日志备份及还原(脚本和GUI实现) [原创] 一.完整备份.差异备份和事务日志备份的脚本 --完整备份数据库 BACKUP DATABASE Test_Bak TO DISK = 'E:\20150609_75\bak\Test_bak_full.bak' WITH INIT --差异备份数据库 BACKUP DATABASE Test_Bak TO DISK = 'E:\20150609_75\bak\Test_bak_diff.bak' WITH I

SQL Server事务日志被填满的原因是什么

SQL Server事务日志可能会被填满,这会阻止之后的数据库操作,包括UPDATE, DELETE, INSERT 和CHECKPOINT. 事务日志填满会导致1105错误: Can't allocate space for object syslogs in database dbname because the logsegment is full. If you ran out of space in syslogs, dump the transaction log. Otherwis

人人都是 DBA(VI)SQL Server 事务日志

原文:人人都是 DBA(VI)SQL Server 事务日志 SQL Server 的数据库引擎通过事务服务(Transaction Services)提供事务的 ACID 属性支持.ACID 属性包括: 原子性(Atomicity) 一致性(Consistency) 隔离性(Isolation) 持久性(Durability) 事务日志(Transaction Log) 事务日志(Transaction Log)存储的是对数据库所做的更改信息,让 SQL Server 有机会恢复数据库.而恢复

SQL Server事务日志的几个常用操作

我们知道,SQL Server事务日志主要是用来记录所有事务对数据库所做的修改,如果系统出现故障,它将成为最新数据的唯一来源.日志的操作常有以下几个应用: 一.事务日志文件LDF的丢失 当我们不小删除或者LDF文件丢失的时候,数据库只剩下MDF文件,此时直接通过附加MDF是无法恢复数据库的,那我们怎么样才能恢复数据库呢?我们可以把SQL Server的日志文件分为两种形式:一类是无活动事务的日志,另一类是有活动事务的日志,我们分别根据两种情况来进行数据库恢复. 1.无活动事务的日志恢复 当文件并

SQL SERVER 事务日志

  谈到事务日志就不得不谈下检查点 Checkpoint ,简称 CKP. 事务日志和检查点都是为了数据库的快速恢复而存在的.我们需要知道检查点是干什么的,它和事务日志有什么关系. CKP是干什么的? 总所周知,数据的更改并没有直接写入mdf/ndf 数据文件中,而是写入了buffer pool中; 当CKP被触发后,检查点将"全部"脏数据页从当前数据库的buffer pool刷新到数据文件上.所谓的脏数据,是那些被更改的 data page ,在内存中却没有写入磁盘.全部脏数据包括已

SQL Server 2008事务日志传送备份的实施过程详解

熟悉微软企业级数据库软件朋友,了解作为微软一个重大的产品版本,SQL Server 2008除了许多新的特性和关键的改进,使得它成为至今为止的最强大和最全面的SQL Server版本外,其实SQL Server 2008中的备份方式也是其一大亮点,SQL Server 2008使用的备份一个数据库有多种方法,如差异备份和事物日志备份.事务日志备份将复制上次完全或以前的事务日志备份的所有数据变化.事物日志备份通常是非常快并且非常小,仅次于镜像的高可靠性备份方案,可以达到分钟级的灾难恢复能力. 下面

如何防止SQL Server数据库的事务日志异常增长

server|数据|数据库 当事务日志扩展到无法接受的限度时您必须执行的步骤.事务日志的扩展会导致 Microsoft SQL Server 数据库无法使用. 在 SQL Server 2000 中,每个数据库都至少包含一个数据文件和一个事务日志文件.SQL Server 2000 在该数据文件中以物理方式存储数据.事务日志文件存储您对 SQL Server 数据库执行的所有修改的详细信息,以及执行每个修改的事务的详细信息.由于事务完整性被视为 SQL Server 的一个基本而固有的特点,因此

SQL Server误区:有关大容量事务日志恢复模式的误区

误区 #28:有关大容量事务日志恢复模式的几个误区 28 a)常见的DML操作可以被"最小记录日志" 不是.在大容量事务日志恢复模式下只有一小部分批量操作可以被"最小记录日志",这类操作的列表可以在Operations That Can Be Minimally Logged找到.这是适合SQL Server 2008的列表,对于不同的SQL Server版本,请确保查看正确的列表. 28 b)使用大容量事务日志恢复模式不会影响灾难恢复 首先,在上次事务日志备份之后

SQL Server误区:在破坏日志备份链后需要完整备份来重新开始日志链

误区 #20:在破坏日志备份链之后,需要一个完整备份来重新开始日志链 错误 事务日志备份会备份自上次事务日志备份以来所有的事务日志(如果从来没有过日志备份的话,那就从上一次完整备份开始).有好几种类型的操作会中断事务日志的连续性,也就是说除非重新开始新的日志链,SQL Server无法再进行日志备份.下面这几种操作都有可能引起日志链断裂: 由完整恢复模式或大容量事务日志恢复模式转为简单恢复模式 从数据库镜像进行恢复 备份日志时指定了NO_LOG 或 WITH TRUNCATE_ONLY(还好在S