sql server 备份与恢复系列四 大容量模式下的备份与还原

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: 原文:sql server 备份与恢复系列四 大容量模式下的备份与还原一. 概述   在sql server 备份与恢复系列的第一篇里,有讲到大容量模式下备份与还原的相关知识。这篇重点来演示在大容量模式下常用的备份与还原模式“完整备份+差异备份+日志备份”。
原文: 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

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

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
3月前
|
SQL 数据库
Microsoft SQL Server 2014如何来备份数据库
Microsoft SQL Server 2014如何来备份数据库
275 3
|
3月前
|
SQL 存储 数据库
备份SQL语句
【8月更文挑战第20天】备份SQL语句
33 3
|
3月前
|
SQL 数据库
SQL Server 如何进行备份?
【8月更文挑战第20天】SQL Server 如何进行备份?
47 3
|
3月前
|
SQL 存储 数据库
SQL Server 中的备份类型详解
【8月更文挑战第31天】
62 0
|
3月前
|
数据库 Java 监控
Struts 2 日志管理化身神秘魔法师,洞察应用运行乾坤,演绎奇幻篇章!
【8月更文挑战第31天】在软件开发中,了解应用运行状况至关重要。日志管理作为 Struts 2 应用的关键组件,记录着每个动作和决策,如同监控摄像头,帮助我们迅速定位问题、分析性能和使用情况,为优化提供依据。Struts 2 支持多种日志框架(如 Log4j、Logback),便于配置日志级别、格式和输出位置。通过在 Action 类中添加日志记录,我们能在开发过程中获取详细信息,及时发现并解决问题。合理配置日志不仅有助于调试,还能分析用户行为,提升应用性能和稳定性。
50 0
|
3月前
|
SQL 安全 数据库
|
3月前
|
SQL 安全 测试技术
【数据守护者必备】SQL数据备份与恢复策略全解析:从全量到日志备份,手把手教你确保企业信息万无一失的实战技巧!
【8月更文挑战第31天】数据库是企业核心业务数据的基石,为防止硬件故障、软件错误或人为失误导致的数据丢失,制定可靠的备份与恢复策略至关重要。本文通过一个在线购物平台的案例,详细介绍了使用 SQL Server 进行全量备份、差异备份及事务日志备份的方法,并演示了如何利用 SQL Server Agent 实现自动化备份任务。此外,还提供了数据恢复的具体步骤和测试建议,确保数据安全与业务连续性。
113 0
|
3月前
|
SQL 数据库
SQL Server 还原 备份集中的数据库备份与现有的 'XXX' 数据库不同
SQL Server 还原 备份集中的数据库备份与现有的 'XXX' 数据库不同
84 0
|
2月前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823,附加数据库失败。数据库没有备份,无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有:数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
97 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
|
5天前
|
存储 数据挖掘 数据库
数据库数据恢复—SQLserver数据库ndf文件大小变为0KB的数据恢复案例
一个运行在存储上的SQLServer数据库,有1000多个文件,大小几十TB。数据库每10天生成一个NDF文件,每个NDF几百GB大小。数据库包含两个LDF文件。 存储损坏,数据库不可用。管理员试图恢复数据库,发现有数个ndf文件大小变为0KB。 虽然NDF文件大小变为0KB,但是NDF文件在磁盘上还可能存在。可以尝试通过扫描&拼接数据库碎片来恢复NDF文件,然后修复数据库。