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, 虽然备份文件受损࿰