SQL Server 损坏修复 之三 不同部位损坏的应对

2024-01-15 15:48
文章标签 sql 不同 server 修复 database 损坏 应对 之三 部位

本文主要是介绍SQL Server 损坏修复 之三 不同部位损坏的应对，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

如果数据库或数据库备份受损，在检查数据库完整性的时候，会发现各种各样的错误。在这一节里，我们针对不同的损坏部位，给出不同的应对方法。首先，我们创建一个测试数据库。

 

CREATE DATABASE TESTDB

GO

USE TESTDB

GO

CREATE TABLE TESTTABLE

(ID int,

NAME nvarchar(50)

)

 

 --建立两个索引，其中一个是聚集索引，另外一个是非聚集索引

CREATE CLUSTERED INDEX idx1 on TESTTABLE (ID)

CREATE INDEX idx2 on TESTTABLE(NAME)

 

 -- 插入300行数据

DECLARE @i INT

SET @i = 1

WHILE (@i <= 300)

BEGIN

INSERT INTO TESTTABLE VALUES(@i, CONCAT('name_', @i))

SET @i = @i + 1

END

 

备份文件损坏

 

对前面建立的测试数据库做一个全备份，名为TESTDB.BAK。因为数据库比较小，所以备份文件也会比较小，用二进制文件编辑器如UltraEdit更改此文件一些地方的内容，以模拟该文件受损的情形，另存为TESTDB_BAD.BAK。执行如下命令，对数据库进行恢复：

RESTORE DATABASE TESTDB FROM DISK='D:\temp\TESTDB_BAD.bak'

 

在做恢复的时候，可能会碰到如下的错误，并且恢复过程会中断。

Processed 312 pages for database 'TESTDB', file 'TESTDB' on file 1.

Processed 3 pages for database 'TESTDB', file 'TESTDB_log' on file1.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'TESTDB'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrectchecksum (expected: 0x9cb7ecfe; actual: 0x6f316d79). It occurred during a readof page (1:19) in database ID 8 at offset 0x00000000026000 in file 'C:\ProgramFiles\Microsoft SQL Server\MSSQL11.DENALI\MSSQL\DATA\TESTDB.mdf'.  Additional messages in the SQL Server errorlog or system event log may provide more detail. This is a severe errorcondition that threatens database integrity and must be corrected immediately.Complete a full database consistency check (DBCC CHECKDB). This error can becaused by many factors; for more information, see SQL Server Books Online.

 

这时，可以使用WITH CONTINUE_AFTER_ERROR参数尝试再次恢复：

RESTORE DATABASE TESTDB FROM DISK='D:\temp\TESTDB_BAD.bak'

 WITH CONTINUE_AFTER_ERROR

 

加CONTINUE_AFTER_ERROR, 虽然备份文件受损，但是会尽最大可能来继续恢复，显示恢复完成。

Processed 312 pages for database 'TESTDB', file 'TESTDB' on file 1.

Processed 3 pages for database 'TESTDB', file 'TESTDB_log' on file1.

Restore was successful but deferred transactions remain. Thesetransactions cannot be resolved because there are data that is unavailable.Either use RESTORE to make that data available or drop the filegroups if younever need this data again. Dropping the filegroup results in a defunctfilegroup.

RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage wasencountered. Inconsistencies in the database are possible.

RESTORE DATABASE successfully processed 315 pages in 0.271 seconds(9.055 MB/sec).

 

不幸的是，对数据库做完恢复后，该数据库有可能会马上处于Suspect(质疑)状态。我们还是无法使用该数据库。 我们把该数据库设置为紧急状态，并执行数据库修复命令:

ALTER DATABASE TESTDB SET EMERGENCY

DBCC CHECKDB(TESTDB)

DBCC CHECKDB(TESTDB, REPAIR_ALLOW_DATA_LOSS)

use TESTDB

SELECT * FROM TESTTABLE

 

上述命令都无法执行成功，并且会报如下错误：

Msg 945, Level 14, State 2, Line 4

Database 'TESTDB' cannot be opened due to inaccessible files orinsufficient memory or disk space.  Seethe SQL Server errorlog for details.

 

碰到这种情况，即使在恢复数据库的时候使用CONTINUE_AFTER_ERROR参数，但是还是无济于事。对于这个受损的备份文件，我们只能遗弃。

 

根据备份文件受损的地方不同，有时候还是能够使用上述方法找回一部分数据的。这很大部分依赖于在备份文件中受损的位置。所以CONTINUE_AFTER_ERROR这个参数，对于受损的数据库备份恢复，虽然是一个不错的尝试，但也并不是万能的。

 

日志文件损坏

下面是一个模拟日志文件损坏的测试。请运行如下的脚本操作。第一个UPDATE语句是更新ID=295的数据，并且马上做一个CHECKPOINT的动作，这会使得数据的更新，立刻写入数据文件中。第二个UPDATE语句打算要更新ID=296的数据，这两个操作是放在同一个事务内进行的。但是在第一个UPDATE语句结束后，不等WAITFOR语句结束，请马上杀掉数据库服务进程(Sqlservr.exe)。这样，在数据文件里，ID=295的记录已经被修改，ID=296的记录还没被修改。如果日志文件不损坏，下次SQLServer重新启动时，SQL会发现这个做到一半的事物，将ID=295的记录修改回滚。

BEGIN TRANSACTION

UPDATE TESTTABLE SET NAME = 'xxxx' where ID = 295

CHECKPOINT

WAITFOR DELAY '0:1:0'

UPDATE TESTTABLE SET NAME = 'xxxx' where ID = 296

COMMIT TRANSACTION

 

在这个测试里，我们编辑该数据库的日志文件，人为对数据库的日志文件造成损坏。当该数据库服务重新启动的时候，该数据库会处于RECOVERY_PENDING状态，查看数据库错误日志，有如下的错误，表明日志文件受损。

2012-04-15 22:49:57.930    spid26s       Error: 824, Severity: 24, State: 2.

2012-04-15 22:49:57.930    spid26s       SQL Server detected a logicalconsistency-based I/O error: incorrect checksum (expected: 0x6c1e7b9c; actual:0xd6a87d4a). It occurred during a read of page (2:0) in database ID 8 at offset0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL11

2012-04-15 22:49:57.930    spid26s       Error: 5105, Severity: 16, State: 1.

2012-04-15 22:49:57.930    spid26s       A file activation error occurred. Thephysical file name 'C:\Program Files\Microsoft SQLServer\MSSQL11.DENALI\MSSQL\DATA\TESTDB_log.ldf' may be incorrect. Diagnose andcorrect additional errors, and retry the operation.

2012-04-15 22:49:57.950    spid26s       File activation failure. The physicalfile name "C:\Program Files\Microsoft SQLServer\MSSQL11.DENALI\MSSQL\DATA\TESTDB_log.ldf" may be incorrect.

2012-04-15 22:49:57.950    spid26s       The log cannot be rebuilt because therewere open transactions/users when the database was shutdown, no checkpointoccurred to the database, or the database was read-only. This error could occurif 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\xxxx_log.LDF')

ALTER DATABASE TESTDB SET MULTI_USER

 

在重建数据库日志以后，一定要运行DBCC CHECKDB以确保没有一致性错误。

 

有一个要注意的地方，对数据库日志进行重建以后，我们会发现ID为295的NAME变成了xxxx，而ID为296的NAME依旧是name_296。如下图10-2所示。这从数据库的逻辑上来讲是没有问题的。但是从应用的角度，可能会大有问题。应用程序可能有逻辑需求，要求ID为295和ID为296的名字要么同时改变，要么同时不改变，在应用程序中，我们开启了事务以确保这一点。但是经过日志重建后，这点不能得到保证了。

图 10- 2 重建数据库日志后，保存下来的数据

因此，重建数据库的日志文件，是迫不得已的办法，会破坏数据的一致性。还是建议从好的数据库备份中恢复数据，这样能保证数据在业务逻辑上的一致。

 

用户数据文件损坏

让我们接着模拟数据库文件损坏的各种情形。新建测试数据库后，把TESTDB下线，编辑该数据库的数据文件，以模拟该数据文件受损的情形。然后把该数据库上线，运行DBCC CHECKDB命令，会报告发现数据库受损。根据受损的部位，有以下几个情形:

·        非聚集索引页面受损

具体的错误信息如下:

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 245575913, index ID 2, partition ID72057594039173120, alloc unit ID 72057594043564032 (type In-row data), page(1:228). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129and -4.

Msg 8928, Level 16, State 1, Line 1

Object ID 245575913, index ID 2, partition ID 72057594039173120,alloc unit ID 72057594043564032 (type In-row data): Page (1:228) could not beprocessed.  See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 245575913, index ID 2, partition ID72057594039173120, alloc unit ID 72057594043564032 (type In-row data). Indexnode page (1:264), slot 0 refers to child page (1:228) and previous child(0:0), but they were not encountered.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 245575913, index ID 2, partition ID72057594039173120, alloc unit ID 72057594043564032 (type In-row data). Page(1:265) is missing a reference from previous page (1:228). Possible chainlinkage problem.

DBCC results for 'TESTTABLE'.

There are 300 rows in 2 pages for object "TESTTABLE".

CHECKDB found 0 allocation errors and 4 consistency errors in table'TESTTABLE' (object ID 245575913).

 

读者会发现数据库受损是发生在表格TESTTABLE，而且是索引编号为2的存储上面。索引编号大于1的都是非聚集索引。因此，读者可以重建非聚集索引来尝试对数据库进行修复。

 

DROP INDEX idx2 ON TESTTABLE

GO

CREATE INDEX idx2 ON TESTTABLE(NAME)

 

再次运行DBCC CHECKDB(TESTDB)命令，检查结果显示，数据库中的数据是一致的。由于受损的地方恰好在非聚集索引页上，所以我们对数据库可以进行不丢失数据修复，如果受损在其他地方，如聚集索引页，则丢失数据难以避免。

·        聚集索引页面受损

 

根据第八章数据库空间管理中提到的方法，我们执行下面一系列查询，然后对聚集索引页面进行篡改:

 

SELECT * FROM sys.objects WHERE NAME = 'TESTTABLE'

-- 结果为245575913

 

SELECT * FROM sys.partitions WHERE object_id = '245575913'

-- 有两个索引，其中聚集索引所在地partition_id为72057594039107584

 

SELECT * FROM sys.system_internals_allocation_units WHERE

container_id = '72057594039107584'

-- 得知第一个数据页为(1, 226)

 

DBCC TRACEON(3604)

DBCC PAGE('TESTDB', 1, 226, 1)

DBCC TRACEOFF(3604)

-- 得知数据页的具体内容，编辑数据文件，找到相应位置，对该数据页面的内容进行篡改

 

得知数据页的具体内容后，把该数据库下线, 根据第一个数据页的查询得到的字符串，编辑数据文件，对该数据页内容进行篡改。然后上线该数据库，并对数据库做一致性检查，会发现如下错误:

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 245575913, index ID 1, partition ID72057594039107584, alloc unit ID 72057594043498496 (type In-row data). Page(1:231) is missing a reference from previous page (1:226). Possible chainlinkage problem.

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 245575913, index ID 1, partition ID72057594039107584, alloc unit ID 72057594043498496 (type In-row data), page(1:226). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129and -4.

Msg 8928, Level 16, State 1, Line 1

Object ID 245575913, index ID 1, partition ID 72057594039107584,alloc unit ID 72057594043498496 (type In-row data): Page (1:226) could not beprocessed.  See other errors for details.

Msg 8980, Level 16, State 1, Line 1

Table error: Object ID 245575913, index ID 1, partition ID72057594039107584, alloc unit ID 72057594043498496 (type In-row data). Indexnode page (1:230), slot 0 refers to child page (1:226) and previous child(0:0), but they were not encountered.

DBCC results for 'TESTTABLE'.

There are 63 rows in 1 pages for object "TESTTABLE".

CHECKDB found 0 allocation errors and 4 consistency errors in table'TESTTABLE' (object ID 245575913).

 

这次，数据页受损是在聚集索引页面上(索引编号为1)，也就是在数据页面上，因此我们无法通过重建索引的方法对数据进行修复。我们采用REPAIR_ALLOW_DATA_LOSS的方法，对数据库进行修复。

 

ALTER DATABASE TESTDB set EMGERGENCY

ALTER DATABASE TESTDB set single_user withrollback immediate

GO

DBCC CHECKDB(TESTDB, 'REPAIR_ALLOW_DATA_LOSS')

GO

ALTER DATABASE TESTDB set multi_user

 

检索修复后的数据库，读者会发现，原来有300条记录的，现在只剩下了63条。有237条数据在修复的过程中丢失了。这是因为修复程序发现页内数据有问题，采用比较保守的办法，是把整个页面的数据删除，以维护数据的一致性。

 

·        系统页面受损

 

数据库有一些系统页面或系统表格非常重要，如在第八章数据库空间管理提及的PFS页面，GAM页面和SGAM页面。如果这些页面受损，那么整个数据库基本上很难修复。下面是一个示例。首先查询TESTDB的GAM页面信息，得到Slot 1里面的数据0000381f。

 

 

把数据库TESTDB下线，用二进制文件编辑器打开数据文件，对字符串0000381f进行篡改，然后把该数据库上线。数据库一上线后，TESTDB马上处于置疑(Suspect)状态，如图10-5所示。

 

我们把该数据库设为紧急模式，然后执行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 allocationintegrity checks in database ID 5 pages from (1:0) to (1:517631). See othererrors 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 rebuild thelog.

DBCC results for 'TESTDB'.

CHECKDB found 0 allocation errors and 0 consistency errors indatabase 'TESTDB'.

Msg 7909, Level 20, State 1, Line 3

The emergency-mode repair failed.You must restore from backup.

 

因此，如果系统页面或者系统表受损，则只能从备份当中恢复数据。

 

10.3.4 系统数据库损坏

 

系统数据库包括master数据库，tempdb数据库，MSDB数据库和MODEL数据库。由于tempdb数据库会在数据库重新启动时，再次创建，因此对于tempdb数据库的损坏，我们可以重新启动数据库对其进行恢复。

 

master数据库，MSDB数据库和MODEL数据库一般情况下不会进行很多改动，所以如果碰到系统数据库损坏，从备份中恢复系统数据库是最好的办法。如果我们没有对系统数据库做备份，那么修复系统数据库的方法，则是从相同版本的数据库上，拷贝系统数据库的数据文件和日志文件，然后替换受损的系统数据库。具体方法，可参考前面章节对移动数据库的介绍。这样，损失的数据，包括master里面的数据和MSDB里面的数据。用户可以通过重建登录以及重建Job来进行恢复。

这篇关于SQL Server 损坏修复 之三 不同部位损坏的应对的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/609413。

相关文章

MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...
前端导出Excel文件出现乱码或文件损坏问题的解决办法

前端导出Excel文件出现乱码或文件损坏问题的解决办法

《前端导出Excel文件出现乱码或文件损坏问题的解决办法》在现代网页应用程序中,前端有时需要与后端进行数据交互,包括下载文件,：本文主要介绍前端导出Excel文件出现乱码或文件损坏问题的解决办法,... 目录1. 检查后端返回的数据格式2. 前端正确处理二进制数据方案 1：直接下载（推荐）方案 2：手动构造
阅读更多...
一文详解MySQL索引(六张图彻底搞懂)

一文详解MySQL索引(六张图彻底搞懂)

《一文详解MySQL索引(六张图彻底搞懂)》MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度,：本文主要介绍MySQL索引的相关资料,文中通过代码介绍的... 目录一、什么是索引？为什么需要索引？二、索引该用哪种数据结构？1. 哈希表2. 跳表3. 二叉排序树4.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2