欧美成人精品手机在线观看_69视频国产_动漫精品第一页_日韩中文字幕网 - 日本欧美一区二区

當(dāng)我們發(fā)現(xiàn)數(shù)據(jù)庫數(shù)據(jù)頁損壞了，或者執(zhí)行 DBCC CHECKDB 發(fā)現(xiàn)有損壞的數(shù)據(jù)頁時(shí)，大部分人都執(zhí)行如下操作進(jìn)行修復(fù)。

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ù)可能不成功，也可能使數(shù)據(jù)丟失。因?yàn)閿?shù)據(jù)頁的損壞也分多鐘情況，如日志損壞、索引損壞、數(shù)據(jù)損壞、系統(tǒng)對象損壞等。現(xiàn)在我們考慮的是用戶庫數(shù)據(jù)損壞的情況修復(fù)。

為了能進(jìn)行數(shù)據(jù)修復(fù)，數(shù)據(jù)庫須使用完整模式，先進(jìn)行一次完整備份。

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

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

--找一個(gè)數(shù)據(jù)頁DBCC TRACEON(3604,-1)DBCC IND(TestDBSubA,Test,-1)DBCC PAGE('TestDBSubA', 1, 179,3)
--破壞該數(shù)據(jù)頁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.

此時(shí)我們再看看該數(shù)據(jù)頁存儲的信息。

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

頁面內(nèi)容有10個(gè)字符被替換了，十六進(jìn)制為 65，轉(zhuǎn)換十進(jìn)制為 101即為ASCII值，對應(yīng)的字符為字母 e。這10個(gè)字符改動的，為該表該行字段 GUID 的部分值。也就是說，該行的字段GUID數(shù)據(jù)丟失了！

現(xiàn)在使用修改后的值操作該行數(shù)據(jù)，更改時(shí)發(fā)生錯(cuò)誤。

--查看該行記錄,正常SELECT [GUID],[SID],[NAME],[VALUE]FROM [TestDBSubA].[dbo].[Test]WHERE GUID='65656565-6565-6565-6565-005056c00008'
--第一列[SID]被writepage更改了,所以此時(shí)更改[SID]將報(bào)錯(cuò)!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

當(dāng)前命令發(fā)生了嚴(yán)重錯(cuò)誤。應(yīng)放棄任何可能產(chǎn)生的結(jié)果。

當(dāng)發(fā)生問題時(shí)，我們沒能及時(shí)發(fā)現(xiàn)和修復(fù)，其他表或數(shù)據(jù)又有新的操作，我們模擬如下。

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

那么該如何修復(fù)呢？

修復(fù)之前，我們要習(xí)慣進(jìn)行一次日志備份。

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

剛開始之前，我們有進(jìn)行過一次完整備份。那時(shí)的完整備份的數(shù)據(jù)還沒有損壞，所有我們可以用最近的完整備份進(jìn)行某個(gè)數(shù)據(jù)頁的修復(fù)。

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).

接下來，我們需要還原剛剛備份的事務(wù)日志，將數(shù)據(jù)還原到最近狀態(tài)。

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).

此時(shí)再進(jìn)行對該表操作，發(fā)現(xiàn)2個(gè)語句都報(bào)錯(cuò)了！是不是有些慌？

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.

檢查數(shù)據(jù)庫，仍然報(bào)錯(cuò)。

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.

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

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

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

閱讀原文：原文鏈接