您所在的位置:首页 > 新闻中心 > 大容量模式下的备份与还原,完整模式下的备份与还原
公司要闻
大容量模式下的备份与还原,完整模式下的备份与还原
发布时间:2020-03-18 22:35
访问量:359

一. 概述

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

澳门新濠登录网址 1

一.概述

  前面介绍了简单恢复模式和大容量恢复模式,这篇继续写完整恢复模式下的备份与还原。在完整恢复模式里最大的优点是只要能成功备份尾日志,就可以还原到日志备份内包含的任何时点("时点恢复")。当然对比前二种模式它是牺牲了磁盘I/O性能。

恢复模式

备份策略

数据安全性

I/O性能

简单恢复

完整备份+差异备份

安全最差。最后一次备份之后,所有数据操作丢失。

最优

大容量恢复

完整备份+差异备份+日志备份

折中。批量操作有丢失风险。尾日志备份失败。最后一次备份之后,所有数据操作丢失

折中

完整恢复

完整备份+差异备份+日志备份

相比上面二种最安全。尾日志备份失败。最后一次备份之后,所有数据操作丢失

最差

  在完整恢复模式下,最常见的备份策略,如下图所示:澳门新濠登录网址 2

二.备份

    我这里有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

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

澳门新濠登录网址 3

二. 备份

  在前章中讲到了大容量恢复模式下的备份。备份策略与大容量模式是一样的,同样是完整备份+差异备份+日志备份。这里要突出点是:当误操作发生后,如何还原到误操作之前的一分钟,找出误操作之前的数据。
在"sql server 日志文件结构及误操作数据找回"中有介绍误操作数据找回,但是基于第三方工具ApexSQL Log。虽然该工具方便,但要收费哟。

  我这里有一个BackupTest库,库里有个Employees表

use master
--设置完全模式
ALTER DATABASE BackupTest SET  RECOVERY FULL  
--创建备份设备(有就不要执行)
use master
exec sp_addumpdevice 'disk', 'BackupTestDevice','F:\SqlService\backup\BackupTestBackup.bak'
go
--做一次完整备份到备份设备中(备份基准)
backup database  BackupTest to BackupTestDevice

--新增数据
insert BackupTest.dbo.Employees values('湖南长沙')
insert BackupTest.dbo.Employees values('湖南湘潭')
--日志备份
backup log BackupTest to BackupTestDevice

 备份集如下所示:

澳门新濠登录网址 4

-- 误操作发生, 忘记加where条件,操作时间是:2018-8-12 10:55  
delete from BackupTest.dbo.Employees 

澳门新濠登录网址,三. 还原(1)批量插入的是否会丢失

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

use master

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

    澳门新濠登录网址 5

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

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

澳门新濠登录网址 6

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

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

    澳门新濠登录网址 7

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

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

    澳门新濠登录网址 8

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

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

  澳门新濠登录网址 9

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

三.还原(1)

  当误操作发生后,是需要找管理员来进行数据还原。 如果数据库太大,还原是需要很长时间(注意使用副本,不要使用生产库)。 这种情况下就需要等待了。 避免的方法:(1)是做sql审核,不在Managemnet studio里直接操作,避免此类事情发生.(2)是使用粒度更小的备份方式,但相应的复杂些。

--步骤1 备份尾日志
use master
go
backup log BackupTest to BackupTestDevice with norecovery 

澳门新濠登录网址 10

go
--步骤2 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database BackupTest from BackupTestDevice with file=19, norecovery --事务不恢复

--步骤3 
restore log BackupTest from BackupTestDevice  with file=20,  norecovery --事务不恢复

--步骤4 用stopat恢复到10:54
restore log BackupTest from BackupTestDevice  with file=21, stopat='2018/8/12 10:54', recovery --事务恢复

--数据又回来了
select * from  BackupTest.dbo.Employees 

  澳门新濠登录网址 11

 四. 还原(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

  澳门新濠登录网址 12

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

四.还原(2)

  在前面介绍中,有讲过,完整恢复模式切换到大容量模式,日志链是不会中断。下面来验证

--从完整恢复模式切换到大容量模式
ALTER DATABASE BackupTest SET  RECOVERY bulk_logged 
-- 新增
insert BackupTest.dbo.Employees values('湖南株洲')
--日志备份
backup log BackupTest to BackupTestDevice
-- 删除
delete from BackupTest.dbo.Employees 

-- 尾日志
backup log BackupTest to BackupTestDevice with norecovery 

 备份集如下所示,日志文件ID:22是在大容量模式下备份的,23是尾日志

澳门新濠登录网址 13

restore database BackupTest from BackupTestDevice with file=19, norecovery --事务不恢复
restore log BackupTest from BackupTestDevice  with file=20,  norecovery --事务不恢复
restore log BackupTest from BackupTestDevice  with file=21,  norecovery --事务不恢复
restore log BackupTest from BackupTestDevice  with file=22,  recovery 

  当日志还原到文件ID:22时,报错,如下图所示

澳门新濠登录网址 14

   跳过文件ID:22, 使用23来提交事务,也会报错,如下所示:

restore log BackupTest from BackupTestDevice  with file=23,  recovery

澳门新濠登录网址 15

   经过测试,还原失败,错误是指:与上一次还原到指定时间点有关系。

  下面在测试一个新库TestFULLToBulk

--设置完全模式
ALTER DATABASE TestFULLToBulk SET  RECOVERY FULL  
--做一次完整备份到备份设备中(备份基准)
backup database  TestFULLToBulk to BackupTestDevice
insert TestFULLToBulk.dbo.product values('湖南株洲')
--日志备份
backup log TestFULLToBulk to BackupTestDevice
--设置大容量
ALTER DATABASE TestFULLToBulk SET RECOVERY bulk_logged   

insert TestFULLToBulk.dbo.product values('湖南湘潭')
--日志备份
backup log TestFULLToBulk to BackupTestDevice

  备份集如下:文件ID28是在大容量下进行的备份

  澳门新濠登录网址 16

backup log TestFULLToBulk to BackupTestDevice with norecovery 
go
restore database TestFULLToBulk from BackupTestDevice with file=26, norecovery 
go
restore log TestFULLToBulk from BackupTestDevice  with file=27,  norecovery 
go
restore log TestFULLToBulk from BackupTestDevice  with file=28,  recovery 

  上面还原成功,证明了完整恢复模式切换到大容量模式,日志链是不会中断。

 

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

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

澳门新濠登录网址 17

-- 尾日志备份
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

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

  澳门新濠登录网址 18

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

-- 尾日志备份
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

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