当前位置： 首页 > news >正文

sql server 事务日志备份异常恢复案例---惜分飞

news 2026/6/11 11:38:33

有客户的sql server数据库运行在双机环境中,由于心跳网络异常导致双机频繁切换最终数据库损坏DBCC检查报大量错误

DBCC CHECKDB('OLTP') WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 28147935764938752 (typeUnknown), page ID (1:33059984) contains an incorrect page IDinits page header. The PageIdinthe page header = (68:3276868).

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 11540680206712832 (typeUnknown), page ID (1:33059985) contains an incorrect page IDinits page header. The PageIdinthe page header = (102:6488116).

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 16888988233302016 (typeUnknown), page ID (1:33059986) contains an incorrect page IDinits page header. The PageIdinthe page header = (93:6619252).

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 16888988233302016 (typeUnknown), page ID (1:33059987) contains an incorrect page IDinits page header. The PageIdinthe page header = (93:6619252).

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 16888988233302016 (typeUnknown), page ID (1:33059988) contains an incorrect page IDinits page header. The PageIdinthe page header = (93:6619252).

Msg 8909, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 28147836977938432 (typeUnknown), page ID (1:33059989) contains an incorrect page IDinits page header. The PageIdinthe page header = (73:6619248).

Msg 8909, Level 16, State 1, Line 1

……………………

Object ID 1961110077, index ID 0, partition ID 72057594217627648, alloc unit ID 72057594256687104 (typeIn-row data): Page (1:36535484) could not be processed. See other errorsfordetails.

Msg 8928, Level 16, State 1, Line 1

Object ID 1961110077, index ID 0, partition ID 72057594217627648, alloc unit ID 72057594256687104 (typeIn-row data): Page (1:36535485) could not be processed. See other errorsfordetails.

Msg 8928, Level 16, State 1, Line 1

Object ID 1961110077, index ID 0, partition ID 72057594217627648, alloc unit ID 72057594256687104 (typeIn-row data): Page (1:36535486) could not be processed. See other errorsfordetails.

Msg 8928, Level 16, State 1, Line 1

Object ID 1961110077, index ID 0, partition ID 72057594217627648, alloc unit ID 72057594256687104 (typeIn-row data): Page (1:36535487) could not be processed. See other errorsfordetails.

CHECKDB found 0 allocation errors and 24 consistency errorsintable'CIOMessage'(object ID 1961110077).

CHECKDB found 0 allocation errors and 17955 consistency errorsindatabase'OLTP'.

Completiontime: 2025-11-19T17:13:03.2762122+08:00

客户每天做全库备份,每4小时做事务日志备份,备份类似这样的情况

客户尝试使用全备进行恢复,结果发现只有13日的全备是好的,可以还原出来数据库,其他备份还原直接报错,基于这样的情况,可以希望把数据恢复到11月19日.我接手这个故障之后,先尝试还原13日的备份

然后尝试人工应用事务日志备份,类似命令

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_13_030001_7745248.trn'WITH NORECOVERY

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_13_060001_3581210.trn'WITH NORECOVERY

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_13_090001_2856408.trn'WITH NORECOVERY

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_13_120002_0713663.trn'WITH NORECOVERY

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_13_150001_7305524.trn'WITH NORECOVERY

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_13_180000_9123036.trn'WITH NORECOVERY

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_13_210001_3663138.trn'WITH NORECOVERY

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_14_000001_1605695.trn'WITH NORECOVERY

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_14_030001_7280782.trn'WITH NORECOVERY

………………

RESTORE LOG OLTP1121 FROM DISK ='D:\share\OLTP_backup_2025_11_17_180001_1343952.trn'WITH NORECOVERY

结果在OLTP_backup_2025_11_17_180001_1343952文件位置报错

Processed 0 pagesfordatabase'OLTP_1121',file'OLTP'onfile1.

Processed 10388 pagesfordatabase'OLTP1121',file'OLTP_log'onfile1.

Msg 9004, Level 16, State 3, Line 1

An error occurredwhileprocessing the logfordatabase'OLTP_1121'. If possible, restore from backup.

If a backup is not available, it might be necessary to rebuild the log.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

Completiontime: 2025-11-21T13:41:54.2352031+08:00

通过图形化界面进行事务日志恢复也报错

基于这样的情况,数据库层面的正常恢复途径只能恢复到11月17日18时左右数据,因为后面的日志发生了损坏,无法继续正常恢复,对于这种情况,我们这边使用日志解析工具对剩余事务日志备份进行解析,生成.sql文件


然后客户把解析出来的.sql文件依次在会到11月17日18时的库上面去执行,这样顺利吧客户整体数据库恢复到最新状态,完成本次恢复任务(注意后续可能一些类似序列值需要调整)

http://www.rkmt.cn/news/88679.html

相关文章：

  • 完整教程：【029】智能停车计费系统
  • 【超详细】漏洞挖掘入门教程：零基础从原理到实战，全流程拆解 + 工具清单，精通看这一篇！
  • RulersGuides.js终极指南：快速实现网页精准布局的免费工具
  • 国内可用的免费AI
  • 2025年自主智能体（Agent）架构深度研究报告
  • 从0到1搭建智能分析OBS埋点数据的AI Agent：实战指南
  • MYSQL的安装与介绍
  • Scrypted：智能家居视频集成的终极解决方案
  • 破壁者：授权委托书识别技术如何打通纸质文件与数字系统的鸿沟
  • 智能化拓客工具真的有用吗？技术架构与实践深度解析
  • 5分钟掌握窗口置顶：让多任务处理效率翻倍的秘密武器
  • Discord音乐机器人搭建指南：从零开始的完整部署方案
  • 前端开发者必看：玩转CSS3 3D空间与透视效果实战指南
  • 为什么谷歌云服务器是你的下一个最佳选择？
  • 配个环境搞一天，上线联调花一周：我用这套工作流把部署压到了3分钟
  • WeekToDo：重新定义你的周计划管理方式
  • 面向水工、市政与环保工程的渗流控制：有限元方法、程序修改与参数化分析
  • 深入解析：Gyroflow：视频稳定的商业革新神器
  • AI智能搜索文献：高效精准的学术资源检索与获取新方式
  • JL — 695X — 板级配置文件常用配置
  • SimpleRL-reason：零基础强化学习训练指南
  • VLC播放器UOS ARM版离线部署指南
  • 常用免费文献检索网站推荐与使用指南
  • 5分钟拥有一个无广告、超干净的私人博客，这体验谁用谁知道！
  • 连接器防水设计
  • 【集训游记】北京多校“若痕迹都不曾亲眼见过 若连平凡都显得像个传说”
  • 精通CtrlP正则搜索：7个高效模式匹配技巧深度解析
  • 终极便携：VLC播放器绿色免安装版完整使用指南
  • Excelize终极指南：打造专业级Excel图表与数据可视化
  • 数据库索引深度解析：原理、设计与性能优化