专栏首页逸鹏说道在SQL Server里如何进行页级别的恢复

在SQL Server里如何进行页级别的恢复

在今天的文章里我想谈下每个DBA应该知道的一个重要话题:在SQL Server里如何进行页级别还原操作。假设在SQL Server里你有一个损坏的页,你要从最近的数据库备份只还原有问题的页,而不是还原整个数据库。

我们来破坏一个页

第一步我想向你展示下如何建立表(或索引)里有个特定页损坏的情景,这里我们会进行一些魔术,因为开箱即用(out-of-box)的SQL Server本身不会引入任何损坏的页(如果有的话,恭喜你找到了一个BUG)。我们从创建一个新的数据库,往新建的表插入一些记录开始。

 1 USE master 2 GO 3  4 CREATE DATABASE PageLevelRestores 5 GO 6  7 USE PageLevelRestores 8 GO 9 10 -- Create a table where every record fits onto 1 page of 8kb11 CREATE TABLE Test12 (13     Filler CHAR(8000)14 )15 GO16 17 -- Insert 4 records18 INSERT INTO Test VALUES (REPLICATE('A', 8000))19 INSERT INTO Test VALUES (REPLICATE('B', 8000))20 INSERT INTO Test VALUES (REPLICATE('C', 8000))21 INSERT INTO Test VALUES (REPLICATE('D', 8000))22 GO23 24 -- Retrieve the selected records25 SELECT * FROM Test26 GO

下一步我进行完整数据库备份。这就是说这个备份包含了属于Test表的所有页。这非常重要,因为接下来我们会破坏这个表的一个特定页。为了找出属于Test表的页,我用DBCC IND命令来返回所有属于这个表的页。

1 -- Perform a full database backup2 BACKUP DATABASE PageLevelRestores TO DISK = N'C:\Backups\PageLevelRestores.bak'3 GO4 5 -- Retrieve the first data page for the specified table (columns PageFID and PagePID)6 DBCC IND(PageLevelRestores, Test, -1)7 GO

为了破坏一个特定的页,我使用未公开的DBCC WRITEPAGE命令。是的,在SQL Server里有个可用的DBCC WRITEPAGE命令,但请不要告诉任何人……

 1 ALTER DATABASE PageLevelRestores SET SINGLE_USER WITH ROLLBACK IMMEDIATE 2 GO 3  4 -- Let's corrupt page 90... 5 DBCC WRITEPAGE(PageLevelRestores, 1, 90, 0, 1, 0x41, 1) 6 DBCC WRITEPAGE(PageLevelRestores, 1, 90, 1, 1, 0x41, 1) 7 DBCC WRITEPAGE(PageLevelRestores, 1, 90, 2, 1, 0x41, 1) 8 GO 9 10 ALTER DATABASE PageLevelRestores SET MULTI_USER11 GO

为了使用DBCC WRITEPAGE,问题数据库必须设置如代码所示的单用户模式(Single-User mode)。这里我模拟了有个存储错误,写了一些垃圾到存储的页里(是的,这个在你工作中也会碰到!)。现在当你从表再次读取数据库,SQL Server会返回你一个824 I/O错误,因为对损坏页的校验失败了。

1 -- Retrieve the selected records2 SELECT * FROM Test3 GO

一旦SQL Server在I/O访问期间检测到一个损坏的页,在msdb.dbo.suspect_pages里,损坏的页也会被记录,如下图所示。

1 SELECT * FROM msdb.dbo.suspect_pages

对msdb里对特定表进行监控是个很好的想法,可以得到你的数据库里是否有损坏的页。现在我们让事情变得更糟糕,下列代码会往表里插入另外一条记录。

1 -- Now we have additional transaction that we don't want to loose...2 INSERT INTO Test VALUES (REPLICATE('E', 8000))3 GO

我们来还原损坏的页

现在你是DBA,你想恢复这个数据库到正确状态且不丢失数据(像我们在最后一步插入的记录)。你会怎么做?首先你要进行所谓的尾日志备份(Tail-Log Backup):你要备份自上次事务日志备份后的已发生的事务。

1 -- Backup the transaction log2 BACKUP LOG PageLevelRestores TO3     DISK = 'C:\Backups\PageLevelRestores_LOG1.bak'4     WITH INIT5 GO

在这里还没有进行过事务日志备份,因此我们的备份会包含自完整备份后,所有已执行的事务。现在我们可以在SQL Server里初始页级别还原操作。这里你使用传统的RESTORE DATABASE的T-SQL命令,但你只要指定想要还原的页,不用还原整个数据库,我们只要还原有问题的页。如果你在处理很大的数据库,这会有很大的区别。

1 USE master2 -- Restore full database backup3 RESTORE DATABASE PageLevelRestores4     PAGE = '1:90'5     FROM DISK = 'C:\Backups\PageLevelRestores.bak'6     WITH NORECOVERY7 GO

现在到了棘手的部分:在RESTORE DATABASE的T-SQL命令后,你要进行另一个事务日志备份。这个另外的日志备份是需要的,因为接下来你要保证在这个可用页进行的所有改变用作还原。没有这个额外的日志备份,SQL Server不能把你的页重新上线。

1 -- Backup the tail of the log...2 BACKUP LOG PageLevelRestores TO3     DISK = 'C:\Backups\PageLevelRestores_LOG_TAIL.bak'4     WITH INIT5 GO

进行完这个额外日志备份后,最后你可以在正确的顺序里恢复所有的日志备份,最后你可以把数据库恢复上线。

 1 -- Restore all available log backups in the correct order 2 RESTORE LOG PageLevelRestores FROM 3     DISK = 'C:\Backups\PageLevelRestores_LOG1.bak' 4     WITH NORECOVERY 5 GO 6  7 -- Finally restore the tail log backup 8 RESTORE LOG PageLevelRestores FROM 9     DISK = 'C:\Backups\PageLevelRestores_LOG_TAIL.bak'10     WITH NORECOVERY11 GO12 13 -- Finally finish with the restore sequence14 RESTORE DATABASE PageLevelRestores WITH RECOVERY15 GO

现在当你再次查询表时,你会看到SELECT语句成功执行没有任何I/O错误,在这个表里没有丢失任何数据。还是很简单,是不是?

1 USE PageLevelRestores2 GO3 4 -- Retrieve the selected records5 SELECT * FROM Test6 GO

小结

在SQL Server里如何进行页级别恢复操作是每个DBA应该知道的。它是你工具箱里最重要的工具之一——尤其当你在处理很大的数据库时。不用恢复整个数据库,你只恢复有问题的页,整个恢复过程会非常快。

最后给你一个问题,各位看官:你是否有过在SQL Server进行页级别的恢复,如果是的话——对你来说它有多难/简单?请畅所欲言!

感谢关注!

本文分享自微信公众号 - 我为Net狂(dotNetCrazy),作者:WoodyTu

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2016-03-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 小侃 SQL加密和性能

    细说SQL Server中的加密 简介 加密是指通过使用密钥或密码对数据进行模糊处理的过程。在SQL Server中,加密并不能替代其他的安全设置,比如...

    逸鹏
  • 搭建个性化人脸分析系统~Face And Face++

    不过多的介绍了,API调用,很简单的,看看官方文档即可~ Face++ http://www.faceplusplus.com.cn/uc_home/ 您只需要...

    逸鹏
  • Python3 与 C# 扩展之~基础拓展

    看着小张准备回家换衣服了,小明有点失落,又有点孤单,于是说道:“逗逼张,你还要听吗?我准备讲类相关的知识了,这些可是我课后自学的哦~”

    逸鹏
  • 摄像头智能云组网EasyNTS网络穿透设备如何进行设备配置?

    智能云组网EasyNTS是一套集中管理所有接入的云终端设备,实现接入与管控、动态组网、远程运维的设备组网,安装方便且部署简单,在很多需要进行组网或者网络穿透的现...

    EasyNVR
  • SpringBoot之JPA

    前言:用Spring全家桶,大家常用的应该是jpa,上次我看过一次调查统计,歪果人使用持久化框架jpa比较多,目前国内已知互联网公司mybatis会比较多,可能...

    王念博客
  • VB.NET 实现LED效果源码

    巴西_prince
  • Python爬虫抓取csdn博客

            昨天晚上为了下载保存某位csdn大牛的全部博文,写了一个爬虫来自动抓取文章并保存到txt文本,当然也可以 保存到html网页中。这样就可以不用C...

    py3study
  • Flash之后是不是该IE浏览器了

    Adoebe官方网站发布了公告,2020年12月30日起终止支援Flash。目前Chrome、Edge等浏览器都会有提醒。用户默认不开启Flash,如果页面视频...

    八哥
  • linux下自动备份脚本并上传到ftp服务器

    #!/bin/bash #设置日志文件,前提建好了/backup/log目录 LogFile=/backup/log/`date +"%Y-%m"`.log...

    三杯水Plus
  • Python-EEG工具库MNE中文教程(4)-MNE中数据结构Evoked及其对象创建

    本教程为脑机学习者Rose发表于公众号:脑机接口社区(微信号:Brain_Computer),QQ交流群:903290195

    脑机接口社区

扫码关注云+社区

领取腾讯云代金券