如果数据库或数据库备份受损,在检查数据库完整性的时候,会发现各种各样的错误。在这一节里,我们针对不同的损坏部位,给出不同的应对方法。首先,我们创建一个测试数据库。
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, 虽然备份文件受损