sql server 备份与恢复系列四 大容量模式下的备份与还原
一. 概述
在sql server 备份与恢复系列的第一篇里，有讲到大容量模式下备份与还原的相关知识。这篇重点来演示在大容量模式下常用的备份与还原模式“完整备份+差异备份+日志备份”。 在大容量恢复模式下，特别要注意的是在什么情况下会导致数据还原丢失风险，带着这个问题，来进行演示说明。备份策略如下图所示：
二.备份
我这里有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
查看备份集列表如下图所示：
三. 还原(1)批量插入的是否会丢失
通过还原查看批量插入操作是否丢失，在备份尾日志时如果报错, 信息如下："因为数据库正在使用，所以无法获得对数据库的独占访问权" 需要将库设置成单用户模式
use master
-- 先还原完整备份 ,norecovery(正在还原...)不可读写. file指备份集位置号 restore database TestBulkLogged from BackupTestDevice with file=10, norecovery
在大容量模式下还原时，sql server会检测你是否进行了尾日志备份，也是确保最后一次日志备份后，所做的数据操作在还原后不丢失。（如果尾日志备份失败，则丢失数据）。下面先备份一下尾日志, 使用norecovery 暂不提交
-- 尾日志备份 backup log TestBulkLogged to BackupTestDevice with norecovery
上图备份了尾日志后，备份集里多出了一个文件号14, 下面在重新还原完整备份
-- (重新)从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号 restore database TestBulkLogged from BackupTestDevice with file=10, norecovery
-- 恢复到日志文件11 restore database TestBulkLogged from BackupTestDevice with file=11, norecovery -- 恢复到日志文件12 restore database TestBulkLogged from BackupTestDevice with file=12, recovery
接下来我们来查询下库中的product表，查看数据是否全部恢复。
-- 查询大批量操作的数据,是否已还原出来 select * from TestBulkLogged.dbo.product
结论：通过上图我们可以了解到，第一次和第二次做的日志备份都完美的还原了过来。 大批量插入操作也得到了还原。证明在大容量模式下，大批量操作的数据, 还原恢复可能存在丢失的风险，但不一定会丢失掉。
四. 还原(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
结论：如果只有（完整备份和事务日志备份）， 在还原时，事务日志必须保持LSN顺序,依次还原，否则还原失败就会丢失数据。
五. 还原(3) 基于差异备份下的日志还原
在生产环境中，由于日志文件备份频繁，导致日志文件太多，如果按日志文件一个一个来还原，需要大量时间和精力。下面演示直接从差异备份还原开始，看后面的日志文件是否能还原成功。
-- 尾日志备份 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
上面还原是跳过了日志文件，直接使用差异备份文件还原。我们来查看下表中的数据,会发现差异备份完全可以还原正确成功。
下面是差异备份与日志备份组合来还原，结论是日志文件不需要一个一个来还原，可以直接定位到，一个差异备份来还原，再还原，之后的日志文件。
-- 尾日志备份 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
结论：有了差异备份，在还原时就节省了很多还原时间和精力。可以在完整备份的基准内，直接选择最后一次的差异备份加上之后的日志备份来还原。
