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

一. 概述

  在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,从而日志文件也可能很大。

网站地图xml地图