日韩欧美国产精品免费一二-日韩欧美国产精品亚洲二区-日韩欧美国产精品专区-日韩欧美国产另-日韩欧美国产免费看-日韩欧美国产免费看清风阁

當我們發現數據庫數據頁損壞了，或者執行 DBCC CHECKDB 發現有損壞的數據頁時，大部分人都執行如下操作進行修復。

ALTER DATABASE db_name SET EMERGENCY;DBCC CHECKDB ('db_name');ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;--{ REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS);ALTER DATABASE db_name SET MULTI_USER;

不過，上面的修復可能不成功，也可能使數據丟失。因為數據頁的損壞也分多鐘情況，如日志損壞、索引損壞、數據損壞、系統對象損壞等?，F在我們考慮的是用戶庫數據損壞的情況修復。

為了能進行數據修復，數據庫須使用完整模式，先進行一次完整備份。

ALTER DATABASE [TestDBSubA] SET RECOVERY FULL WITH NO_WAITBACKUP DATABASE [TestDBSubA] TO DISK = N'E:\DatabaseFile\Backup\TestDBSubA.bak

我們先任意找一個數據頁（如頁ID=179）進行寫入破壞。更改的偏移量為100(96頁頭+前4個數據字符)，替換了10個字符

--找一個數據頁DBCC TRACEON(3604,-1)DBCC IND(TestDBSubA,Test,-1)DBCC PAGE('TestDBSubA', 1, 179,3)
--破壞該數據頁DBCC WRITEPAGE('TestDBSubA', 1, 179, 100, 10, 0x65656565656565656565)
--檢查DBDBCC CHECKDB('TestDBSubA')

DBCC results for 'TestDBSubA'.

…………（此處省略）

DBCC results for 'sys.syssoftobjrefs'.

There are 4 rows in 1 pages for object "sys.syssoftobjrefs".

Msg 8933, Level 16, State 1, Line 1

Table error: Object ID 1019150676, index ID 1, partition ID 72057594051100672, alloc unit ID 72057594059948032 (type In-row data). 

The low key value on page (1:179) (level 0) is not >= the key value in the parent (1:431) slot 6.

…………（此處省略）

CHECKDB found 0 allocation errors and 1 consistency errors in database 'TestDBSubA'.

repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (TestDBSubA).

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

此時我們再看看該數據頁存儲的信息。

DBCC PAGE('TestDBSubA', 1, 179,3)

頁面內容有10個字符被替換了，十六進制為 65，轉換十進制為 101即為ASCII值，對應的字符為字母 e。這10個字符改動的，為該表該行字段 GUID 的部分值。也就是說，該行的字段GUID數據丟失了！

現在使用修改后的值操作該行數據，更改時發生錯誤。

--查看該行記錄,正常SELECT [GUID],[SID],[NAME],[VALUE]FROM [TestDBSubA].[dbo].[Test]WHERE GUID='65656565-6565-6565-6565-005056c00008'
--第一列[SID]被writepage更改了,所以此時更改[SID]將報錯!UPDATE T SET [SID]=SUSER_SID()FROM [TestDBSubA].[dbo].[Test] TWHERE GUID='65656565-6565-6565-6565-005056c00008'

Msg 8646, Level 21, State 1, Line 1

Unable to find index entry in index ID 1, of table 1019150676, in database 'TestDBSubA'. 

The indicated index is corrupt or there is a problem with the current update plan. 

Run DBCC CHECKDB or DBCC CHECKTABLE. If the problem persists, contact product support.

Msg 0, Level 20, State 0, Line 0

當前命令發生了嚴重錯誤。應放棄任何可能產生的結果。

當發生問題時，我們沒能及時發現和修復，其他表或數據又有新的操作，我們模擬如下。

DELETE TOP(10) FROM [TestDBSubA].[dbo].[Test]WHERE GUID<>'65656565-6565-6565-6565-005056c00008' GOUPDATE T SET VALUE=100 FROM [TestDBSubA].[dbo].[Test] TGO

那么該如何修復呢？

修復之前，我們要習慣進行一次日志備份。

BACKUP LOG [TestDBSubA] TO DISK = N'E:\DatabaseFile\Backup\TestDBSubA_LOG.trn'

剛開始之前，我們有進行過一次完整備份。那時的完整備份的數據還沒有損壞，所有我們可以用最近的完整備份進行某個數據頁的修復。

RESTORE DATABASE [TestDBSubA]PAGE = '1:179'FROM DISK = 'E:\DatabaseFile\Backup\TestDBSubA.bak'WITH NORECOVERY

Processed 1 pages for database 'TestDBSubA', file 'TestPub' on file 1.

RESTORE DATABASE ... FILE=<name> successfully processed 1 pages in 0.072 seconds (0.108 MB/sec).

接下來，我們需要還原剛剛備份的事務日志，將數據還原到最近狀態。

RESTORE LOG [TestDBSubA]FROM DISK = 'E:\DatabaseFile\Backup\TestDBSubA_LOG.trn'WITH RECOVERY;

Processed 0 pages for database 'TestDBSubA', file 'TestPub' on file 1.

The roll forward start point is now at log sequence number (LSN) 597000000036800001. 

Additional roll forward past LSN 597000000038800001 is required to complete the restore sequence.

RESTORE LOG successfully processed 0 pages in 0.035 seconds (0.000 MB/sec).

此時再進行對該表操作，發現2個語句都報錯了！是不是有些慌？

UPDATE T SET [SID]=SUSER_SID()FROM [TestDBSubA].[dbo].[Test] TWHERE GUID='65656565-6565-6565-6565-005056c00008'GOSELECT [GUID],[SID],[NAME],[VALUE]FROM [TestDBSubA].[dbo].[Test]GO

Msg 829, Level 21, State 1, Line 1

Database ID 7, Page (1:179) is marked RestorePending, which may indicate disk corruption. 

To recover from this state, perform a restore.

檢查數據庫，仍然報錯。

DBCC CHECKDB('TestDBSubA')

Msg 8939, Level 16, State 98, Line 1

Table error: Object ID 1019150676, index ID 1, partition ID 72057594051100672, alloc unit ID 72057594059948032 (type In-row data), page (1:179).

Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -6.

Msg 8928, Level 16, State 1, Line 1

Object ID 1019150676, index ID 1, partition ID 72057594051100672, alloc unit ID 72057594059948032 (type In-row data): Page (1:179) could not be processed.

See other errors for details.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 1019150676, index ID 1, partition ID 72057594051100672, alloc unit ID 72057594059948032 (type In-row data).

Page (1:419) is missing a reference from previous page (1:179). Possible chain linkage problem.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 1019150676, index ID 1, partition ID 72057594051100672, alloc unit ID 72057594059948032 (type In-row data).

Page (1:179) was not seen in the scan although its parent (1:431) and previous (1:420) refer to it. Check any previous errors.

怎么解決呢？很簡單，再進行一次事務日志的備份和還原即可！

USE MASTERGOBACKUP LOG [TestDBSubA]TO DISK = N'E:\DatabaseFile\Backup\TestDBSub_LOG.trn'WITH INIT,FORMATGORESTORE LOG [TestDBSubA]FROM DISK = 'E:\DatabaseFile\Backup\TestDBSub_LOG.trn'WITH RECOVERY;GO

到這里，數據頁的修復也就完成了！這一過程你是否發現，備份是很重要的，所以日常的備份及備份的完整性檢查要做到位。

閱讀原文：原文鏈接