不同部位损坏的应对
不同部位损坏的应对
摘自《SQL2012实施与管理实战指南》第十章 数据库损坏修复
P424
备份文件损坏
备份文件损坏，用二进制文件编辑器UE更改bak文件的一些地方，以模拟bak文件受损的情形，另存为TESTDB_BAD.BAK
这时候还原数据库会碰到如下错误，并且恢复过程会中断
restore database is terminating abnormally sqlserver detected a logical consistency-based I/O error:incorrect checksum
这时，可以使用WITH CONTINUE_AFTER_ERROR参数尝试再次还原
RESTORE DATABASE testdb FROM DISK='d:\temp\TESTDB_BAK.BAK' WITH CONTINUE_AFTER_ERROR
加CONTINUE_AFTER_ERROR虽然备份文件受损，但是会尽最大可能来继续还原，显示恢复完成
restore with CONTINUE_AFTER_ERROR was successful but some damage was encountered inconsistencies in the database are possible restore database successfully processed 315 pages in 0.271 seconds(9.055MB/sec)
不幸的是，对数据库做完恢复后，该数据库有可能会马上处于suspect（质疑）状态，我们还是无法使用该数据库。
我们使用下面命令设置数据库为紧急模式，并执行数据库修复命令
ALTER DATABASE TESTDB SET EMERGENCY DBCC CHECKDB(TESTDB) DBCC CHECKDB(TESTDB,REPAIR_ALLOW_DATA_LOSS) USE TESTDB SELECT * FROM TESTTABLE
上述命令无法执行成功，并且报错
database ‘testdb’ cannot be opened due to inaccessible files or insufficient memory or disk space
see the sqlserver errorlog for details
遇到这种情况，即使在恢复数据库的时候使用CONTINUE_AFTER_ERROR参数，也无济于事。
对于这个受损的备份文件，我们只能遗弃
根据备份文件受损的地方不同，有时候还是能够使用上述方法找回一部分数据的，这很大部分依赖在
备份文件中受损的位置。所以CONTINUE_AFTER_ERROR参数对于受损的数据库备份恢复，虽然是一个
不错的尝试，但是也不是万能的。
日志文件损坏
BEGIN TRAN UPDATE TESTTABLE SET NAME='XXX' WHERE ID=295 CHECKPOINT WAITFOR DELAY '0:1:0' UPDATE TESTTABLE SET NAME ='XX' WHERE ID=296 COMMIT TRAN
在第一个update语句结束后，不等WAITFOR语句结束，马上杀掉sqlserver进程sqlservr.exe。这样，在数据文件里
ID=295的记录已经修改，ID=296的记录还没被修改。如果日志文件损坏，下次SQL启动时，SQL会发现这个做到一半的数据
将ID=295的记录修改回滚
我们编辑该数据库的日志文件，人为对日志文件造成损坏。当数据库重新启时，该数据库处于RECOVERY_PEDING状态
在错误日志里可以看到spid26s the log cannot be rebuilt because there were open transactions/users when the database
was shutdown，no checkpoint occurred to the database,or the database was read-only.
this error could occur if the transaction log file was manually deleted or lost due to a....
针对这种日志损坏，读者可以用下面方法，重建数据库日志，以恢复数据库的正常访问
ALTER DATABASE TESTDB SET EMERGENCY ALTER DATABASE TESTDB REBUILD LOG ON (NAME=XXXX_LOG,FILENAME='D:\TEMP\XXX_LOG.LDF') ALTER DATABASE TESTDB SET MULTI_USER
在重建数据库日志后，一定要运行DBCC CHECKDB以确保没有一致性错误
有一个重要的地方，对数据库日志进行重建后，我们会发现ID为295的NAME变成了xxxx，而ID为296的NAME依旧为name_296
这从数据库的逻辑来讲是没有问题的，但是从应用的角度可能会有大问题。
重建数据库日志文件是迫不得已的办法，会破坏数据的一致性。建议从好的数据库备份中恢复数据库
用户数据文件损坏
新建测试数据库后，把数据库OFFLINE，编辑数据库数据文件，以模拟数据文件受损的情形，然后把数据库上线，
运行DBCC CHECKDB，会报告数据库受损，根据受损部位，会有以下几种情形
1、非聚集索引页面受损
table error :object id 244654368 ,index id 2, partition id 89664765456585,alloc unit id 388621557893
(type in-row data) page(1:256) is missing a reference from previous page(1:228).possible chain linkage problem
检测到0个分配错误和4个一致性错误
读者会发现数据库受损是发生在表TESTTABLE，而且是索引编号为2的存储上面。索引编号大于1的都是非聚集索引。
读者可以重建非聚集索引来尝试对数据库进行修复
DROP INDEX IDX2 ON TESTTABLE GO CREATE INDEX IDX2 ON TESTTABLE(NAME)
再次运行DBCC CHECKDB(TESTDB)命令，检查结果显示，数据库中的数据是一致的。
由于受损的地方恰好在非聚集索引页上，我们对数据库可以进行不丢失数据修复，如果受损在其他地方，如聚集索引，则丢失数据难以避免
2、聚集索引页面受损
根据第8章数据库空间管理提到的方法，执行下面一系列查询，然后对聚集索引页面进行篡改
SELECT * FROM [sys].[objects] WHERE name ='TESTTABLE' --结果为24557936 SELECT * FROM [sys].[partitions] WHERE [object_id]='24557936' --有两个索引（聚集和非聚集），其中聚集索引所在的partitions_id为7205758968693 SELECT * FROM sys.[system_internals_allocation_units] WHERE [container_id]='7205758968693' --得知第一个数据页为（1,226） DBCC TRACEON(3604) DBCC PAGE('TESTDB',1,226,1) DBCC TRACEOFF(3604) --得知数据页具体内容，编辑数据文件，找到相应位置，对该数据页面的内容进行篡改
得知数据页面的具体内容，把该数据库下线，根据第一个数据页面查询得到的字符串，编辑数据文件
对该数据页面内容进行篡改，然后上线该数据库，并对数据库做一致性检查
table error :object id 244654368 ,index id 1, partition id 7205758968693,alloc unit id 3886698763126
(type in-row data) page(1:226) is missing a reference from previous page(1:228).possible chain linkage problem
检测到0个分配错误和4个一致性错误
这次，数据页面受损是在聚集索引页面上（索引编号为1），也就是在数据页面上，因此我们无法通过重建索引的方法对数据进行修复
我们采用REPAIR_ALLOW_DATA_LOSS的方法，对数据库进行修复
ALTER DATABASE TESTDB SET EMERGENCY ALTER DATABASE TESTDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO DBCC CHECKDB(TESTDB,REPAIR_ALLOW_DATA_LOSS) GO ALTER DATABASE TESTDB SET MULTI_USER
检索修复后的数据库，读者会发现，原来有300条记录，现在只剩下63条，有237条数据在修复过程中丢失
这是因为修复程序发现页内数据有问题，采用比较保守的方法，把整个页面的数据删除以维护数据的一致性！！
SELECT * FROM TESTTABLE
3、系统页面受损
数据库有一些系统页面和系统表非常重要，PFS、GAM、SGAM
这些页面损坏，那么整个数据库基本上很难修复
下面是一个示例，首先查询TESTDB的GAM页面信息，得到SLOT 1里面的数据00000381f
把数据库下线，用二进制文件编辑器打开数据文件，对字符串00000381f进行篡改，然后把该数据库上线
数据库上线后，TESTDB马上处于suspect（置疑）状态
我们把数据库设置为紧急模式，执行 DBCC CHECKDB(TESTDB)
ALTER DATABASE TESTDB SET EMERGENCY DBCC CHECKDB(TESTDB)
会报如下错误
DBCC RESULTS FOR 'TESTDB' MSG 8998 ,LEVEL 16,STATE 1 ,LINE 1 PAGE ERRORS ON THE GAM,SGAM,OR PFS PAGES PREVENT ALLOCATION INTEGRITY CHECKS IN DATABASE ID 5 PAGES FROM(1:0) TO (1:517631) SEE OTHER ERRORS FOR CAUSE
用下面命令尝试对数据库进行修复：
ALTER DATABASE TESTDB SET EMERGENCY ALTER DATABASE TESTDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CHECKDB(TESTDB,REPAIR_ALLOW_DATA_LOSS)
由于系统页面损坏，所以即使使用REPAIR_ALLOW_DATA_LOSS也无法对数据库进行修复。
修复程序提示如下错误：
msg 5028,level 16,state 4,line 3
the system could not activate enough of the database to the rebuild the log
dbcc results for 'TESTDB'
CHECKDB 发现0个分配错误和0个一致性错误
msg 7909 ,level 20,state 1,line 3
the EMERGENCY-mode repair failed.you must restore from backup
因此，如果系统页面或者系统表受损，可以从备份中恢复数据库或者导数据
系统数据库损坏
系统数据库包括：master、tempdb、msdb、model，由于tempdb数据库会在数据库重启时再次重建，
因此对tempdb数据库的损坏，可以重新启动SQL进行恢复
master、tempdb、model数据库在一般情况下不会进行很多改动，所以如果遇到损坏，从备份中恢复系统数据库是最好的办法
如果没有备份，那么
1、从相同版本的数据库上复制系统数据库的数据文件和日志文件
2、替换受损的系统数据库
这样，损失的数据，包括master数据库里的数据和msdb里的数据。
用户可以通过重建登录以及重建job来恢复
ALTER DATABASE TESTDB SET EMERGENCY ALTER DATABASE TESTDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO DBCC CHECKDB(TESTDB,REPAIR_ALLOW_DATA_LOSS) GO ALTER DATABASE TESTDB SET MULTI_USER
上面的脚本修复后，数据库只剩下63条记录，其他的237条记录为了维护数据库的一致性而被删除了
就是说修改的某个数据页之后，那个数据页后面的页面的数据都会被删除
为什麽叫“REPAIR_ALLOW_DATA_LOSS”就是这个原因：为了维护数据库的一致性，而删除部分数据
有时候导数据可能比直接DBCC CHECKDB能挽回更多数据。
要注意的是，使用这种方法，数据的正确性难以得到保证，因为那时数据库已经有一致性问题了。读者要权衡哪种方法好
当查询到有问题的数据页的时候，SQLSERVER会报I/O一致性错误，所以导数据不一定行得通
导数据的时候，可以使用TOP[number]的方式或者加where的查询，尽量导出
导数据局限性：如果在扫描过程中，碰到数据受损严重，那么扫描是无法进行下去的，有时不得不用REPAIR_ALLOW_DATA_LOSS
对数据库进行修复或者从备份中还原
原来数据页A后面是页面B，现在由于A指向B的连续指针受损，指向另一个错误页面。
为了维护数据一致性，CHECKDB修复程序会吧数据页A认为是最后一页。这时修复程序无从知道数据页B的存在
因而修复程序自己不知道他搞丢了哪些数据，而且数据库修复程序会根据不同的受损情况对数据库做各种各样的修复
没有一个统一的规则
P439
镜像和AlwaysOn的页面自动修复功能
镜像：如果主库或者从库的页面出现问题，会从主库/从库获取页面来进行修复，仅当从库重做线程重做完LSN之后，才会去修复主库的错误或者
修复从库的错误
AlwaysOn：跟镜像差不多，主副本或者备用副本都可以相互进行修复，而且是自动的
使用下面的SQL语句可以查看，当出现页面错误的时候，镜像库/主库，主副本/备用副本是否已经自动修复了
USE [msdb] GO SELECT * FROM [dbo].[suspect_pages]
当event_type列为5的时候表示那个页面是由SQLSERVER自动修复的
广东13
管理 suspect_pages 表 (SQL Server)
本主题介绍如何使用 SQL Server Management Studio 或 Transact-SQL 来在 SQL Server 2014 中管理 suspect_pages 表。 suspect_pages 表可用来维护有关可疑页的信息，并且还有助于确定有无必要进行还原。 suspect_pages 表位于 msdb 数据库中。
如果 SQL Server 数据库引擎在试图读取某个数据页时遇到下列错误之一，该页面将被视为“可疑”：
每个可疑页的页 ID 将记录在 suspect_pages 表中。 数据库引擎将记录在常规处理过程中发现的所有可疑页，例如在下列情况下：
-
查询必须读取页。
-
在运行 DBCC CHECKDB 的过程中。
-
在备份操作的过程中。
当执行还原操作、DBCC 修复操作或删除数据库操作时，suspect_pages 表也会根据需要进行更新。
本主题内容
-
开始之前：
-
若要管理 suspect_pages 表，可使用：
建议
-
suspect_pages 表中记录的错误
在 suspect_pages 表中，由于出现 824 错误而失败的每页占一行，最多为 1,000 行。 下表显示了记录在 suspect_pages 表的 event_type 列中的错误。
错误说明
event_type 值
由操作系统 CRC 错误造成的 823 错误，或者校验和错误或页撕裂以外的 824 错误（例如，页 ID 错误）
1
错误的校验和
2
残缺页
3
已还原（页在标记为错误后已还原）
4
已修复（DBCC 修复了页）
5
已由 DBCC 释放
7
暂时性的错误也会记录在 suspect_pages 表中。 暂时性错误的来源包含 I/O 错误（例如电缆断开连接）或暂时未通过重复校验和测试的页。
-
数据库引擎如何更新 suspect_pages 表
数据库引擎对 suspect_pages 表执行下列操作：
-
如果表未满，则每出现一个 824 错误，该表都会更新以指明出现了错误，且错误计数器也将相应递增。 如果通过修复、还原或释放操作修复后的页仍有错误，则其 number_of_errors 计数将会递增，其 last_update 列也会更新
-
列出的页通过还原或修复操作修复之后，该操作将更新 suspect_pages 行，以指示此页已修复 (event_type = 5) 或已还原 (event_type = 4)。
-
如果运行 DBCC 检查，则该检查会将所有未出错页标记为已修复 (event_type = 5) 或已释放 (event_type = 7)。
-
-
自动更新 suspect_pages 表
尝试读取数据文件中的某一页由于以下原因之一失败后，数据库镜像伙伴或 AlwaysOn 可用性副本将更新 suspect_pages 表。
-
由操作系统 CRC 错误导致的 823 错误。
-
824 错误（像页撕裂这样的逻辑损坏）。
以下操作还自动更新 suspect_pages 表中的行。
-
DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS 更新 suspect_pages 表，以指示已释放或已修复的各页。
-
完整还原、文件还原或页面还原将页面项标记为已还原。
以下操作将自动从 suspect_pages 表中删除行。
-
ALTER DATABASE REMOVE FILE
-
DROP DATABASE
-
-
数据库管理员的维护角色
数据库管理员负责管理表（主要通过删除旧的行实现）。 suspect_pages 表有大小限制，如果此表已满，则不会记录新的错误。 若要防止此表填满，数据库管理员或系统管理员必须通过删除行来手动清除此表中的旧条目。 因此，我们建议您定期删除或存档 event_type 为已还原或已修复的行或具有旧 last_update 值的行。
若要监视对 suspect_pages 表执行的操作，可使用 Database Suspect Data Page 事件类。 有时会因暂时性错误而向 suspect_pages 表添加行。 如果正在向该表添加很多行，则 I/O 子系统可能出了问题。 如果您注意到正向该表添加的行数突然增加，我们建议您检查一下 I/O 子系统是不是出现了问题。
数据库管理员还可以插入或更新记录。 例如，如果数据库管理员知道某个特定的可疑页实际上没问题但想要暂时保留记录，更新行可能就很有用。
安全性
管理 suspect_pages 表
-
连接到数据库引擎。
-
在标准菜单栏上，单击“新建查询”。
-
将以下示例复制并粘贴到查询窗口中，然后单击“执行”。 此示例将删除 suspect_pages 表中的一些行。
-- Delete restored, repaired, or deallocated pages. DELETE FROM msdb..suspect_pages WHERE (event_type = 4 OR event_type = 5 OR event_type = 7); GO
此示例将返回 suspect_pages 表中的错误网页。
-- Select nonspecific 824, bad checksum, and torn page errors. SELECT * FROM msdb..suspect_pages WHERE (event_type = 1 OR event_type = 2 OR event_type = 3); GO
