Server恢复模式与事务日志备份,大容量模式下的

2019-11-16 作者:数据库资讯   |   浏览(194)

一. 概述

  在sql server 备份与恢复系列的第一篇里,有讲到大容量模式下备份与还原的相关知识。这篇重点来演示在大容量模式下常用的备份与还原模式“完整备份 差异备份 日志备份”。 在大容量恢复模式下,特别要注意的是在什么情况下会导致数据还原丢失风险,带着这个问题,来进行演示说明。备份策略如下图所示:

图片 1

在SQL Server中,数据库不能像Oracle数据库一样设置归档模式,但是可以进行事务日志备份,其作用等同于Oracle数据库的日志文件归档。
  SQL Server 备份和还原操作发生在数据库的恢复模式的上下文中。 恢复模式旨在控制事务日志维护。“恢复模式”是一种数据库属性,它控制如何记录事务,事务日志是否需要(以及允许)进行备份,以及可以使用哪些类型的还原操作。可以通过在SSMS里或通过SQL语句进行配置恢复模式:

二.备份

    我这里有TestBulkLogged库,库里新建了一个product空表。备份SQL语句如下所示:

use master
-- 设置大容量模式
ALTER DATABASE TestBulkLogged SET RECOVERY bulk_logged

-- 做一次完整备份到备份设备中(备份基准) 
backup database  TestBulkLogged to BackupTestDevice

-- 新增
insert into TestBulkLogged.dbo.product(model,upbymemberid,brand) values('第一次新增数据',9708,'IT')

-- 做一次日志备份
backup log   TestBulkLogged to BackupTestDevice

-- 批量插入(5998 行受影响)
insert into TestBulkLogged.dbo.product(model,upbymemberid,brand)
select model,upbymemberid,brand from test.dbo.product

-- 做二次日志备份
backup log   TestBulkLogged to BackupTestDevice

-- 第二次日志备份后的新增
insert into TestBulkLogged.dbo.product(model,upbymemberid,brand) values('第二次新增数据',9708,'IT')

-- 做差异备份
backup database  TestBulkLogged to BackupTestDevice with differential 

-- 全部删除(6000 行受影响)
delete from TestBulkLogged.dbo.product

  查看备份集列表如下图所示:

图片 2

图片 3

三. 还原(1)批量插入的是否会丢失

  通过还原查看批量插入操作是否丢失,在备份尾日志时如果报错, 信息如下:"因为数据库正在使用,所以无法获得对数据库的独占访问权" 需要将库设置成单用户模式

use master

-- 先还原完整备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

    图片 4

   在大容量模式下还原时,sql server会检测你是否进行了尾日志备份,也是确保最后一次日志备份后,所做的数据操作在还原后不丢失。(如果尾日志备份失败,则丢失数据)。下面先备份一下尾日志, 使用norecovery 暂不提交

-- 尾日志备份
backup log TestBulkLogged to BackupTestDevice with norecovery 

图片 5

 上图备份了尾日志后,备份集里多出了一个文件号14, 下面在重新还原完整备份

-- (重新)从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

    图片 6

-- 恢复到日志文件11  
restore database TestBulkLogged from BackupTestDevice  with file=11, norecovery

-- 恢复到日志文件12  
restore database TestBulkLogged from BackupTestDevice  with file=12, recovery

    图片 7

 接下来我们来查询下库中的product表,查看数据是否全部恢复。

-- 查询大批量操作的数据,是否已还原出来
select * from TestBulkLogged.dbo.product

  图片 8

  结论:通过上图我们可以了解到,第一次和第二次做的日志备份都完美的还原了过来。 大批量插入操作也得到了还原。证明在大容量模式下,大批量操作的数据, 还原恢复可能存在丢失的风险,但不一定会丢失掉

  SQL Server数据库有3种恢复模式:完整(full)恢复模式、大容量日志(bulk-logged)恢复模式、简单(simple)恢复模式。

 四. 还原(2)打断日志链

  在前面讲述事务日志时提到了, 事务日志链LSN, 在还原的时候必须要保持事务链的顺序,依次的还原。 下面演示跳过日志链文件ID:11 ,直接还原日志链文件ID:12。

-- 尾日志备份
backup log TestBulkLogged to BackupTestDevice with norecovery 

-- 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

-- 跳过日志文件11,恢复到日志文件12  
restore database TestBulkLogged from BackupTestDevice  with file=12, recovery

  图片 9

  结论:如果只有(完整备份和事务日志备份), 在还原时,事务日志必须保持LSN顺序,依次还原,否则还原失败就会丢失数据。

图片 10

五. 还原(3) 基于差异备份下的日志还原

  在生产环境中,由于日志文件备份频繁,导致日志文件太多,如果按日志文件一个一个来还原,需要大量时间和精力。下面演示直接从差异备份还原开始,看后面的日志文件是否能还原成功。

图片 11

-- 尾日志备份
backup log TestBulkLogged to BackupTestDevice with norecovery 

-- 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

-- 恢复到差异备份文件13. 跳过日志文件11,12 
restore database TestBulkLogged from BackupTestDevice  with file=13, recovery

   上面还原是跳过了日志文件,直接使用差异备份文件还原。我们来查看下表中的数据,会发现差异备份完全可以还原正确成功。

  图片 12

下面是差异备份与日志备份组合来还原,结论是日志文件不需要一个一个来还原,可以直接定位到,一个差异备份来还原,再还原,之后的日志文件。

-- 尾日志备份
backup log TestBulkLogged to BackupTestDevice with norecovery 

-- 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestBulkLogged from BackupTestDevice with file=10, norecovery 

-- 恢复到差异备份文件13. 跳过日志文件11,12 
restore database TestBulkLogged from BackupTestDevice  with file=13, norecovery

-- 恢复到日志文件14 
restore database TestBulkLogged from BackupTestDevice  with file=14, recovery

   结论:有了差异备份,在还原时就节省了很多还原时间和精力。可以在完整备份的基准内,直接选择最后一次的差异备份加上之后的日志备份来还原。

简单恢复模式(Simple Recovery Mode)

在simple恢复模式下,日志文件的作用仅仅是保证了SQL Server事务的ACID属性,并不承担具体的恢复数据的角色,正如”simple”这个词的字面意思一样,数据的备份和恢复仅仅是依赖于手动备份和恢复。
  在simple恢复模式下,checkpoint进程启动后,会把数据库日志文件中不包含活动事务(未结束事务)的VLF状态修改为reusable,从而VLF会不断重用(也称为事务日志被截断),执行checkpoint进程后,数据缓冲区中的脏数据会写入磁盘。因为VLF不断被重用,如果没有执行大的事务,日志文件的大小一般不会自动增长。但是因为VLF不断被重用,日志文件中的VLF显然不可能保持一个连续的序列,日志备份也就没有必要了。

在简单恢复模式下,日志几乎是不用进行管理的。每一次CheckPoint都有可能截断日志,从而来回收不活动的VLF以便重复利用空间。因此在简单恢复模式下,日志的空间使用几乎可以不去考虑。

事实上,在simple模式下SQL Server不允许对数据库执行日志备份,而只能进行完整备份及差异备份,这样发生故障时可能会有数据丢失,因此,simple模式一般在开发环境或测试环境下使用,生产数据库很少使用这种模式运行。

图片 13

  我们在每周一0点做一次完整备份,在周三0点和周五0点分别做差异备份。在简单恢复模式下,如果周六数据库崩溃。我们的恢复计划只有根据周一0点的做的完整备份恢复后,再利用周五0点的差异备份进行恢复,而周五0点之后到服务器崩溃期间所有的数据将会丢失。

所以在简单恢复模式下,每次备份后,如果出现严重故障,数据库将有可能丢失工作,每次更新都会增加丢失工作的风险,这种情况将一直持续到下一次备份。这时,工作丢失风险将变为零,并开始新一轮的工作丢失风险。 备份之间的工作丢失风险随着时间的推移而增加。 下图显示了仅使用完整数据库备份的备份策略的工作丢失风险。

图片 14

  对于大容量操作在日志文件中的记录方式,simple模式下与bulk-logged模式相同。
  要注意的是,在simple模式下,日志文件的大小不一定总是很小,当有包含很多操作的事务长时间未结束时,checkpoint不能阶段包含这个事务的VLF,从而日志文件也可能很大。

完整(Full)恢复模式

完整恢复模式通过将对数据库的任何修改记录到日志来给予数据最大程度的保护。在完整恢复模式下,日志的作用不仅仅是保证了数据库事务的ACID,并且还可以使数据恢复到在日志范围内的任何时间点。
  与简单恢复模式相反,在完整恢复模式下,日志作为恢复数据的重要组成部分,日志的管理和对日志空间使用的管理则需要重视。
  大容量操作是指bulk insert、bcp、select into、create index、writetext等操作。执行一个大容量操作命令,会导致大量数据受影响,full模式下,对大容量操作影响的数据都记入事务日志,如执行select into命令对一个表添加了1000条数据,则在事务日志中会记录这1000条记录的所有数据,从而在执行大容量操作时,可能会导致产生大量重做日志,影响运行效率。
  在full与bulk-logged恢复模式下,如果没有对数据库执行完整备份,则与simple恢复模式相同。对设置了full恢复模式或bulk-logged日志恢复模式的数据库执行一次全库备份后,除非执行了事务日志备份,否则VLF不会被重用,从而维持一个连续的日志链,当数据库出现故障时,可以将其恢复至数据库出现故障的时刻。这种情况称为数据库处于完整日志维护状态。但是这样会导致事务日志文件的大小不断增长,如果最后导致没有可用的空间,则可以执行事务日志备份,或把数据库恢复模式修改为simple,以截断日志文件,释放空间。

在完整恢复模式下,CheckPoint不会截断日志。只有对日志的备份才会将MinLSN向后推并截断日志。因此在一个业务量稍大的系统中,日志的增长速度将会变得很快。

图片 15

  如上图,在DB_1处做了完整备份,并且接下来两次分别做了两次日志备份(Log_1和Log_2),在Log_2备份完不久服务器由于数据所在磁盘损坏。这时如果日志文件完好,则可以通过备份尾部日志(Tail of log)后,从DB_1开始恢复,依次恢复Log_1,Log_2,尾部日志来将数据库恢复到灾难发生时的时间点。理论上可以使数据的损失为0。
  下图显示了在完整恢复模式下可以使用的复杂性最小的备份策略。

图片 16

从日志恢复数据的原理是Redo,也就是将日志中记载的事务再重做一遍。这个开销和从完整或差异备份中恢复相比要大很多。因此尽可能的减少利用日志的恢复量。而使用完整或者差异备份来恢复更多的数据。

大容量日志(Bulk-logged)恢复模式

bulk-logged恢复模式与full恢复模式的区别只是对大容量操作记入重做日志的处理方式不同,其他方面都是相同的。
  在full恢复模式下,对数据库的每一项操作都会记录在日志中。而对于某些大量数据的导入导出操作,无疑会在日志中留下大量记录。很多情况下,我们并不需要将这些信息记录在日志中。
  SQL Server中有一类Bulk Changed Map数据页,其中的每个位对应数据库中的一个区,在bulk-logged恢复模式下,只会在Bulk Changed Map数据页中记录大容量操作导致内容发生变化的区的信息,即从上次日志备份以来,数据发生变化的区在Bulk Changed Map数据页中对应的位会被设置为1,从而大容量操作所产生的重做数据量会显著减少,使得执行效率能够有很大提高,这是其优点。

图片 17

  因为在bulk-logged恢复模式下,日志文件中并未记录大容量操作影响的数据,在日志备份时,除了备份日志本身以外,还会备份其中记录的大容量操作影响的那些区的内容,从而会导致日志备份的大小急剧增加,这是其缺点。使用bulk-logged恢复模式下进行的数据库备份或日志备份进行恢复操作时,所花费的时间与full模式类似。

简单来说,full模式下,大容量操作会导致事务日志量急剧增加,在bulk-logged模式下,大容量操作会导致事务日志备份数据量急剧增加。

大容量日志恢复模式作为完整恢复模式的备选方案。微软推荐的最佳实践是在进行大量数据操作时(比如索引的创建和rebuilt,select into操作等),暂时由完整恢复模式切换到大容量恢复模式来节省日志。这个转换并不会破坏日志链。

full与bulk-logged恢复模式下的备份

在创建第一个日志备份之前,必须先创建完整备份(如数据库备份或一组文件备份中的第一个备份)。 仅使用文件备份还原数据库会较复杂。 因此,建议您尽可能从完整数据库备份开始。 此后,必须定期备份事务日志。 这不仅能最小化工作丢失风险,还有助于事务日志的截断。 通常,事务日志在每次常规日志备份之后截断。
  在full恢复模式或bulk-logged日志恢复模式下,以下两种情况视为数据库未处于完整日志维护状态:

  • 从未进行过全库备份
  • 数据库设置为简单模式

未处于完整日志维护状态时,执行checkpoint后,SQL Server会直接重用reusable VLF。
  SQL Server的日志归档(即事务日志备份)要用户手工进行,或者设置为自动执行的作业。SQL Server数据库处于完全恢复模式,而且配置了事务日志备份的自动作业,即SQL Server自动执行事务日志备份,在执行一次完整备份后,其数据库所处状态类似于Oracle数据库的归档模式,区别是SQL Server定时执行事务日志备份,而不是在VLF写满时执行,而Oracle是当前日志组写满时,在日志切换的同时执行日志归档。而简单恢复模式就类似于Oracle的非归档模式。

恢复模式 大容量操作的记录方式 优点 缺点 说明 工作丢失的风险
full 影响的数据都记录 全库备份后,处于日志维护模式,保持连续的日志链,执行数据恢复时,一般不会有数据丢失 日志文件的大小会因为大容量操作而急剧增加 需要日志备份 正常情况下没有。如果日志尾部损坏,则必须重做自最新日志备份之后所做的更改。
bulk-logged 发生变化的区记录在Bulk Changed Map数据页中 通过使用最小方式记录大多数大容量操作,减少日志空间使用量。 有关尽量减少日志量的操作的信息 事务日志备份的数据量会很大 需要日志备份,是完整恢复模式的附加模式,允许执行高性能的大容量复制操作。 如果在最新日志备份后发生日志损坏或执行大容量日志记录操作,则必须重做自该上次备份之后所做的更改。否则不丢失任何工作。
simple 与bulk-logged方式相同 日志文件的VLF会不断重用,日志文件一般不需要自动增长 VLF不能保持连续的日志链,执行数据恢复时会有数据丢失。在发生灾难时,这些更改必须重做。 无日志备份 最新备份之后的更改不受保护。 在发生灾难时,这些更改必须重做。

日志链(Log Chain)

日志备份的连续序列称为“日志链”。 这个概念可以用下图表示:

图片 18

  假设上面两个日志备份可以简单抽象成如上2个备份,则日志备份1的末尾LSN必须小于等于日志备份二的第一个LSN(通常情况下是第一个末尾LSN等于第二个日志备份的第一个LSN),则这两个备份的日志链是连续的。
  下图是一个生产环境下,在SSMS中查看日志链连续的例子:

图片 19

  可以看出,第一次完整备份后,备份多次事务日志,每一个事务日志的开始LSN都等于上一个事务日志的结束LSN。因此可以从第一次完整备份开始,恢复到最后一个日志备份期间的任何时间点。
  完整的日志链以第一次完整备份或由简单恢复模式转为完整或大容量日志模式开始,到当前的时间点结束。除非在创建完整数据库备份时选择覆盖现有备份集,否则现有的日志链将保持不变。所以事务日志备份“日志链” 的序列与数据备份无关。 例如,假设有下列事件顺序:

图片 20

  事务日志备份序列是连续的,从创建初始完整数据库备份的时间(上午 8:00) 到创建最后事务日志备份的时间(晚上8:00)。
  若要将数据库还原到故障点,必须保证日志链是完整的。 也就是说,事务日志备份的连续序列必须能够延续到故障点。

使用日志备份来还原到故障点

假设有下列事件顺序。

图片 21

若要将数据库还原到晚上 9:45(故障点)时的状态, 可以使用以下两种备选过程:

备选过程 1:使用最新的完整数据库备份还原数据库

  1. 失败时创建当前活动事务日志的结尾日志备份。
  2. 不要还原上午 8:00 的 所需的时间长。 相反,应还原下午 6:00 的 这一时间更近的完整数据库备份,然后应用晚上 8:00 的 日志备份和结尾日志备份。

备选过程 2:使用较早的完整数据库备份还原数据库
  注意:如果因某个问题而无法使用下午 6:00 的完整数据库备份,则此备选过程很有用。 所需的时间长。 此过程比从下午 6:00 的完整数据库备份还原 所需的时间长。

  1. 失败时创建当前活动事务日志的结尾日志备份。
  2. 还原上午 8:00 的 完整数据库备份,然后按顺序还原所有四个事务日志备份。 所有完成的事务都将前滚到晚上 9:45。

此备选过程指出了冗余安全性,该安全性通过维护一系列完整数据库备份中的事务日志链备份来获得。

本文由yzc216亚洲城发布于数据库资讯,转载请注明出处:Server恢复模式与事务日志备份,大容量模式下的

关键词: yzc216亚洲城 www.yzc216.c