਍㰀琀椀琀氀攀㸀䄀氀戀攀爀琀 瘀愀渀 搀攀爀 匀攀氀 㨀 匀漀氀瘀椀渀最 匀儀䰀 匀攀爀瘀攀爀 挀漀爀爀甀瀀琀椀漀渀㰀⼀琀椀琀氀攀㸀ഀഀ ਍ഀഀ

Some notes on handling SQL Server corruption

਍ഀഀ Version : 1.1
਍㰀䈀㸀䐀愀琀攀㰀⼀䈀㸀ऀऀ㨀 ㈀㌀⼀　㤀⼀㈀　㄀　㰀戀爀㸀ഀഀ By : Albert van der Sel
਍㰀䈀㸀吀礀瀀攀 漀昀 搀漀挀㰀⼀䈀㸀ऀ㨀 䤀琀✀猀 樀甀猀琀 愀 昀攀眀 渀漀琀攀猀 漀渀 匀儀䰀 匀攀爀瘀攀爀 挀漀爀爀甀瀀琀椀漀渀 椀渀 瘀攀爀礀 猀椀洀瀀氀攀 眀漀爀搀猀⸀ 䤀琀✀猀 渀漀 洀漀爀攀 琀栀愀渀 ∀攀渀琀爀礀 氀攀瘀攀氀∀⸀㰀戀爀㸀ഀഀ For who : For anyone who likes a short orientation on the subject.
਍ഀഀ
਍㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ Although you do not need to be an expert, it is assumed that you have at least a "reasonable" level of knowledge of SQL Server.
਍㰀戀爀㸀ഀഀ Also, I did my best to put trustworthy and correct information in this note, but due to the "nature of the game",
਍甀猀攀 椀琀 愀琀 礀漀甀爀 漀眀渀 爀椀猀欀⸀㰀戀爀㸀ഀഀ
਍䌀栀愀瀀琀攀爀 ㄀ 樀甀猀琀 搀攀猀挀爀椀戀攀猀 琀栀攀 挀爀攀愀琀椀漀渀 漀昀 愀 䐀攀洀漀 搀愀琀愀戀愀猀攀 眀栀椀挀栀 眀椀氀氀 戀攀 甀猀攀搀 琀漀 猀椀洀甀氀愀琀攀 猀攀瘀攀爀愀氀 猀漀爀琀猀 漀昀 挀漀爀爀甀瀀琀椀漀渀⸀㰀戀爀㸀ഀഀ But, as of Chapter 2, real scenario's are described.
਍㰀戀爀㸀ഀഀ ਍㰀䈀㸀䌀漀渀琀攀渀琀猀㨀㰀戀爀㸀ഀഀ
਍䌀栀愀瀀琀攀爀 ㄀⸀ 䌀爀攀愀琀椀渀最 琀栀攀 攀砀愀洀瀀氀攀 搀愀琀愀戀愀猀攀Ⰰ 愀渀搀 最攀琀琀椀渀最 愀 栀攀砀 攀搀椀琀漀爀⸀㰀戀爀㸀ഀഀ
਍ⴀⴀ 㰀䤀㸀一漀琀攀猀 漀渀 漀戀樀攀挀琀ⴀ 漀爀 搀愀琀愀 瀀愀最攀 挀漀爀爀甀瀀琀椀漀渀㨀㰀⼀䤀㸀㰀戀爀㸀ഀഀ Chapter 2: "Soft" corruption on (ordinary) data and index pages (corrupt table).
਍䌀栀愀瀀琀攀爀 ㌀㨀 匀攀瘀攀爀攀 挀漀爀爀甀瀀琀椀漀渀 漀渀 ⠀漀爀搀椀渀愀爀礀⤀ 搀愀琀愀 愀渀搀 椀渀搀攀砀 瀀愀最攀猀 ⠀挀漀爀爀甀瀀琀 琀愀戀氀攀⤀⸀㰀戀爀㸀ഀഀ
਍ⴀⴀ 㰀䤀㸀一漀琀攀猀 漀渀 昀椀氀攀ⴀ 漀爀 愀搀洀椀渀 瀀愀最攀 挀漀爀爀甀瀀琀椀漀渀㨀㰀⼀䤀㸀㰀戀爀㸀ഀഀ Chapter 4: Inaccesible or Suspect database due to a Missing or Corrupt Transaction log.
਍䌀栀愀瀀琀攀爀 㔀㨀 䤀渀愀挀挀攀猀椀戀氀攀 漀爀 匀甀猀瀀攀挀琀 搀愀琀愀戀愀猀攀 搀甀攀 琀漀 匀攀瘀攀爀攀 挀漀爀爀甀瀀琀椀漀渀 漀渀 愀 ⸀渀搀昀 昀椀氀攀⸀㰀戀爀㸀ഀഀ
਍ⴀⴀ 㰀䤀㸀伀琀栀攀爀 渀漀琀攀猀㨀㰀⼀䤀㸀㰀戀爀㸀ഀഀ Chapter 6: Overview of Microsoft SQL Server Repair commands.
਍䌀栀愀瀀琀攀爀 㜀㨀 唀猀椀渀最 焀甀攀爀椀攀猀 琀漀 猀愀氀瘀愀最攀 搀愀琀愀 昀爀漀洀 挀漀爀爀甀瀀琀 琀愀戀氀攀猀⸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ
਍㰀栀㈀㸀䌀栀愀瀀琀攀爀 ㄀⸀ 䌀爀攀愀琀椀渀最 琀栀攀 攀砀愀洀瀀氀攀 搀愀琀愀戀愀猀攀Ⰰ 愀渀搀 最攀琀琀椀渀最 愀 栀攀砀 攀搀椀琀漀爀⸀㰀⼀栀㈀㸀㰀戀爀㸀ഀഀ ਍㰀䈀㸀䔀砀愀洀瀀氀攀 䐀愀琀愀戀愀猀攀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
਍䤀渀 琀栀攀 昀漀氀氀漀眀椀渀最 猀攀挀琀椀漀渀猀Ⰰ 眀攀 眀椀氀氀 甀猀攀 琀栀攀 昀漀氀氀漀眀椀渀最 攀砀愀洀瀀氀攀 搀愀琀愀戀愀猀攀⸀㰀戀爀㸀ഀഀ With the script below, it's easy to create the demo database. Note that we have several filegroups and multiple logfiles,
਍椀渀 漀爀搀攀爀 琀漀 ∀猀椀洀甀氀愀琀攀∀ 愀 挀攀爀琀愀椀渀 氀攀瘀攀氀 漀昀 ∀挀漀洀瀀氀攀砀椀琀礀∀⸀㰀戀爀㸀ഀഀ Also, we create a few sample tables, and index, on different filegroups.
਍㰀戀爀㸀ഀഀ To follow this note, you do not need to create this sample database. You can just browse through, or just read this note.
਍䈀甀琀Ⰰ 椀昀 礀漀甀 氀椀欀攀 琀漀 爀攀瀀攀愀琀 琀栀攀 愀挀琀椀漀渀猀 礀漀甀爀猀攀氀昀Ⰰ 椀琀 椀猀 爀攀愀氀氀礀 愀搀瘀椀猀愀戀氀攀 琀漀 挀爀攀愀琀攀 椀琀⸀㰀戀爀㸀ഀഀ And, since we have a script, it's not too bad if you hose up the database. Creating it again is just a matter of seconds.
਍㰀戀爀㸀ഀഀ I really advise you to create the database on SQL Server 2005 or 2008, and follow along with me, starting at Chapter 2,
਍愀渀搀 眀漀爀欀 礀漀甀爀 眀愀礀 琀漀 攀愀挀栀 昀漀氀氀漀眀椀渀最 挀栀愀瀀琀攀爀⸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ ਍ഀഀ create database SALES
਍漀渀 倀刀䤀䴀䄀刀夀㰀戀爀㸀ഀഀ (
਍渀愀洀攀㴀✀匀䄀䰀䔀匀✀Ⰰ㰀戀爀㸀ഀഀ filename='c:\mssql\data\SALES.mdf',
਍猀椀稀攀㴀㐀　䴀䈀Ⰰ㰀戀爀㸀ഀഀ filegrowth= 10MB,
਍洀愀砀猀椀稀攀㴀 ㄀　　䴀䈀㰀戀爀㸀ഀഀ ),
਍䘀䤀䰀䔀䜀刀伀唀倀 匀䄀䰀䔀匀䐀䄀吀䄀　㄀㰀戀爀㸀ഀഀ (
਍渀愀洀攀㴀✀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀✀Ⰰ㰀戀爀㸀ഀഀ filename='c:\mssql\data\SALES_DATA_01.ndf',
਍猀椀稀攀㴀 㐀　䴀䈀Ⰰ㰀戀爀㸀ഀഀ filegrowth= 10MB,
਍洀愀砀猀椀稀攀㴀 ㄀　　䴀䈀㰀戀爀㸀ഀഀ ),
਍䘀䤀䰀䔀䜀刀伀唀倀 匀䄀䰀䔀匀䤀一䐀䔀堀　㄀㰀戀爀㸀ഀഀ (
਍渀愀洀攀㴀✀匀䄀䰀䔀匀开䤀一䐀䔀堀开　㄀✀Ⰰ㰀戀爀㸀ഀഀ filename='c:\mssql\data\SALES_INDEX_01.ndf',
਍猀椀稀攀㴀 㐀　䴀䈀Ⰰ㰀戀爀㸀ഀഀ filegrowth= 10MB,
਍洀愀砀猀椀稀攀㴀 ㄀　　䴀䈀㰀戀爀㸀ഀഀ )
਍䰀伀䜀 伀一㰀戀爀㸀ഀഀ (
਍渀愀洀攀㴀✀匀䄀䰀䔀匀开䰀伀䜀开　　㄀✀Ⰰ㰀戀爀㸀ഀഀ filename='c:\mssql\data\SALES_LOG_001.ldf',
਍猀椀稀攀㴀 㐀　䴀䈀Ⰰ㰀戀爀㸀ഀഀ filegrowth= 10MB,
਍洀愀砀猀椀稀攀㴀 ㄀　　䴀䈀㰀戀爀㸀ഀഀ )
਍㰀戀爀㸀ഀഀ ALTER DATABASE SALES
਍䴀伀䐀䤀䘀夀 䘀䤀䰀䔀䜀刀伀唀倀 匀䄀䰀䔀匀䐀䄀吀䄀　㄀ 䐀䔀䘀䄀唀䰀吀㰀戀爀㸀ഀഀ GO
਍㰀戀爀㸀ഀഀ USE SALES
਍䜀伀㰀戀爀㸀ഀഀ
਍䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀 搀戀漀⸀䔀䴀倀䰀伀夀䔀䔀㰀戀爀㸀ഀഀ (
਍䔀䴀倀开䤀䐀     䤀一吀          一伀吀 一唀䰀䰀Ⰰ㰀戀爀㸀ഀഀ EMP_NAME VARCHAR(20) NOT NULL,
਍匀䄀䰀䄀刀夀     䐀䔀䌀䤀䴀䄀䰀⠀㜀Ⰰ㈀⤀㰀戀爀㸀ഀഀ )
਍伀一 嬀匀䄀䰀䔀匀䐀䄀吀䄀　㄀崀㰀戀爀㸀ഀഀ
਍䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀 搀戀漀⸀䔀䴀倀䰀伀夀䔀䔀㈀㰀戀爀㸀ഀഀ (
਍䔀䴀倀开䤀䐀     䤀一吀          一伀吀 一唀䰀䰀Ⰰ㰀戀爀㸀ഀഀ EMP_NAME VARCHAR(20) NOT NULL,
਍匀䄀䰀䄀刀夀     䐀䔀䌀䤀䴀䄀䰀⠀㜀Ⰰ㈀⤀㰀戀爀㸀ഀഀ )
਍伀一 嬀倀刀䤀䴀䄀刀夀崀㰀戀爀㸀ഀഀ
਍唀匀䔀 匀䄀䰀䔀匀㰀戀爀㸀ഀഀ GO
਍㰀戀爀㸀ഀഀ -- insert data into the EMPLOYEE table in Filegroup SALESDATA01 (the .ndf file)
਍椀渀猀攀爀琀 椀渀琀漀 䔀䴀倀䰀伀夀䔀䔀㰀戀爀㸀ഀഀ values
਍⠀㄀Ⰰ✀䠀愀爀爀礀✀Ⰰ㈀　　　⸀㔀　⤀㰀戀爀㸀ഀഀ
਍椀渀猀攀爀琀 椀渀琀漀 䔀䴀倀䰀伀夀䔀䔀㰀戀爀㸀ഀഀ values
਍⠀㈀Ⰰ✀一愀搀椀愀✀Ⰰ㌀　　　⸀　　⤀㰀戀爀㸀ഀഀ
਍椀渀猀攀爀琀 椀渀琀漀 䔀䴀倀䰀伀夀䔀䔀㰀戀爀㸀ഀഀ values
਍⠀㌀Ⰰ✀䄀氀戀攀爀琀✀Ⰰ㔀　　　⸀　　⤀㰀戀爀㸀ഀഀ
਍ഀഀ -- insert data into the EMPLOYEE2 table in Filegroup PRIMARY (the .mdf file)
਍椀渀猀攀爀琀 椀渀琀漀 䔀䴀倀䰀伀夀䔀䔀㈀㰀戀爀㸀ഀഀ values
਍⠀㄀Ⰰ✀䠀愀爀爀礀✀Ⰰ㈀　　　⸀㔀　⤀㰀戀爀㸀ഀഀ
਍椀渀猀攀爀琀 椀渀琀漀 䔀䴀倀䰀伀夀䔀䔀㈀㰀戀爀㸀ഀഀ values
਍⠀㈀Ⰰ✀一愀搀椀愀✀Ⰰ㌀　　　⸀　　⤀㰀戀爀㸀ഀഀ
਍椀渀猀攀爀琀 椀渀琀漀 䔀䴀倀䰀伀夀䔀䔀㈀㰀戀爀㸀ഀഀ values
਍⠀㌀Ⰰ✀䄀氀戀攀爀琀✀Ⰰ㔀　　　⸀　　⤀㰀戀爀㸀ഀഀ
਍䌀刀䔀䄀吀䔀 一伀一䌀䰀唀匀吀䔀刀䔀䐀 䤀一䐀䔀堀 椀渀搀砀开攀洀瀀氀漀礀攀攀开攀洀瀀渀愀洀攀 伀一 䔀䴀倀䰀伀夀䔀䔀⠀䔀䴀倀开一䄀䴀䔀⤀ 伀一 匀䄀䰀䔀匀䤀一䐀䔀堀　㄀㰀戀爀㸀ഀഀ GO
਍㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ Ok, we have a database right now.
਍㰀戀爀㸀ഀഀ Get a Hex editor:
਍㰀戀爀㸀ഀഀ Next, to emulate several crashes, we need a hex editor, by which we can open a database file,
਍愀渀搀 ∀挀漀爀爀甀瀀琀∀ 琀栀攀 昀椀氀攀 洀愀渀甀愀氀氀礀⸀㰀戀爀㸀ഀഀ There are several choices here. You might consider the free utility XVI32, which you can download from
਍琀栀攀 猀椀琀攀 戀攀氀漀眀⸀㰀戀爀㸀 ഀഀ
਍栀琀琀瀀㨀⼀⼀眀眀眀⸀挀栀洀愀愀猀⸀栀愀渀搀猀栀愀欀攀⸀搀攀⼀搀攀氀瀀栀椀⼀昀爀攀攀眀愀爀攀⼀砀瘀椀㌀㈀⼀砀瘀椀㌀㈀⸀栀琀洀㰀戀爀㸀ഀഀ
਍夀漀甀 漀渀氀礀 栀愀瘀攀 琀漀 搀漀眀渀氀漀愀搀 琀栀攀 稀椀瀀 昀椀氀攀Ⰰ 愀渀搀 攀砀琀爀愀挀琀 椀琀⸀ 吀栀攀渀 礀漀甀 愀爀攀 爀攀愀搀礀 琀漀 爀甀渀 椀琀⸀㰀戀爀㸀 ഀഀ There exists many alternatives too. Just "google" on "hex editor".
਍䈀甀琀 䤀 爀攀愀氀氀礀 爀攀挀漀洀洀攀渀搀 砀瘀椀㌀㈀Ⰰ 搀甀攀 琀漀 椀琀猀 猀椀洀瀀氀椀挀椀琀礀 愀渀搀 挀愀瀀愀戀椀氀椀琀椀攀猀⸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ ਍ഀഀ ਍㰀戀爀㸀ഀഀ

Chapter 2: A "soft" corruption on an ordinary data page (1).

਍ഀഀ This section primarily deals with a "soft corruption" based error, in an ordinary data page,
਍愀渀搀 栀漀眀 琀漀 甀渀搀攀爀猀琀愀渀搀 椀琀⸀ 䤀渀 琀栀椀猀 猀攀挀琀椀漀渀 眀攀 甀猀攀搀 匀儀䰀 匀攀爀瘀攀爀 ㈀　　㠀 ㌀㈀戀椀琀⸀㰀戀爀㸀ഀഀ This type of corruption is not very severe. We will do the corruption ourselves, by using
਍愀 栀攀砀攀搀椀琀漀爀Ⰰ 愀渀搀 挀栀愀渀最攀 猀漀洀攀 瘀愀氀甀攀 椀渀 愀 搀愀琀愀瀀愀最攀 眀栀椀氀攀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 漀昀昀氀椀渀攀⸀㰀戀爀㸀ഀഀ In this case, we do not "destroy" a page by killing a pageheader or other critical parts.
਍吀栀椀猀 猀攀挀琀椀漀渀 漀渀氀礀 搀攀愀氀猀 眀椀琀栀 愀 㰀䤀㸀搀愀琀愀㰀⼀䤀㸀 愀氀琀攀爀愀琀椀漀渀Ⰰ 戀甀琀 眀栀椀挀栀 眀椀氀氀 戀攀 搀攀琀攀挀琀攀搀 戀礀 匀儀䰀 匀攀爀瘀攀爀㰀戀爀㸀ഀഀ and it will throw an errormessage (if the "page verify" option is higher than "none').
਍㰀戀爀㸀ഀഀ The involved error message is:
਍㰀戀爀㸀ഀഀ ਍䴀攀猀猀愀最攀 㠀㈀㐀㨀 氀漀最椀挀愀氀 挀漀渀猀椀猀琀攀渀挀礀ⴀ戀愀猀攀搀 䤀⼀伀 攀爀爀漀爀㨀 椀渀挀漀爀爀攀挀琀 挀栀攀挀欀猀甀洀 㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䄀猀 眀攀 栀愀瘀攀 猀攀攀渀 椀渀 匀攀挀琀椀漀渀 ㄀Ⰰ 眀攀 栀愀瘀攀 瀀甀琀 琀栀爀攀攀 爀攀挀漀爀搀猀 椀渀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀⸀ 吀栀椀猀 琀愀戀氀攀 椀猀 猀琀漀爀攀搀㰀戀爀㸀ഀഀ in the SALESDAT01 filegroup, which consist of the "c:\mssql\data\SALES_DATA_01.ndf" file.
਍㰀戀爀㸀ഀഀ In section 2.1, we will explore where those records are located, that is, in which page the records are stored.
਍嘀愀爀椀漀甀猀 洀攀琀栀漀搀猀 攀砀椀猀琀猀⸀ 䠀攀爀攀 眀攀 眀椀氀氀 甀猀攀 琀栀攀 䐀䈀䌀䌀 倀䄀䜀䔀⠀⤀ 昀甀渀挀琀椀漀渀⸀㰀戀爀㸀ഀഀ
਍䤀渀 猀攀挀琀椀漀渀 ㈀⸀㈀Ⰰ 眀攀 眀椀氀氀 甀猀攀 愀 栀攀砀攀搀椀琀漀爀 ⠀氀椀欀攀 砀瘀椀㌀㈀⤀Ⰰ 琀漀 攀洀甀氀愀琀攀 愀 猀漀昀琀 挀漀爀爀甀瀀琀椀漀渀Ⰰ 戀礀 愀氀琀攀爀椀渀最㰀戀爀㸀ഀഀ an Employee name, directly in the file itself. Thus we bypass SQL Server, (using the hexeditor) while the sales database is offline.
਍䠀攀爀攀 眀攀 愀氀琀攀爀 愀 ✀渀漀渀 挀爀椀琀椀挀愀氀 瀀愀最攀✀Ⰰ 洀攀愀渀椀渀最 琀栀愀琀 眀攀 樀甀猀琀 愀氀琀攀爀 愀 搀愀琀愀瀀愀最攀⸀㰀戀爀㸀ഀഀ What is the effect?
਍㰀戀爀㸀ഀഀ

Section 2.1: DBCC PAGE()

਍ഀഀ Now, the three records of the EMPLOYEE table, are likedly to be located at the "first" usable datapage
਍椀渀 琀栀攀 ∀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀⸀渀搀昀∀ 昀椀氀攀Ⰰ 眀栀椀挀栀 ⠀椀渀 洀礀 挀愀猀攀⤀ 椀猀 瀀愀最攀 㠀⸀㰀戀爀㸀ഀഀ
਍夀漀甀 欀渀漀眀 琀栀愀琀 㰀䈀㸀琀栀攀 瘀攀爀礀 昀椀爀猀琀 瀀愀最攀猀㰀⼀䈀㸀 椀渀 愀渀礀 搀愀琀愀戀愀猀攀 昀椀氀攀Ⰰ 愀爀攀 甀猀攀搀 昀漀爀 愀搀洀椀渀椀猀琀爀愀琀椀瘀攀 瀀甀爀瀀漀猀攀猀Ⰰ 氀椀欀攀 琀栀攀 ∀昀椀氀攀栀攀愀搀攀爀 瀀愀最攀∀ ⠀瀀愀最攀 　⤀Ⰰ㰀戀爀㸀ഀഀ the "Page Free Space (PSF) page" (page 1) etc.. It's easy to check that our records are in the 8th page,
਍甀猀椀渀最 琀栀攀 䐀䈀䌀䌀 倀䄀䜀䔀⠀⤀ 挀漀洀洀愀渀搀⸀ 䰀攀琀✀猀 琀爀礀 琀栀愀琀⸀㰀戀爀㸀ഀഀ
਍䘀椀爀猀琀 甀猀攀 ∀䐀䈀䌀䌀 吀刀䄀䌀䔀伀一 ⠀㌀㘀　㐀⤀∀ 椀渀 礀漀甀爀 搀愀琀愀戀愀猀攀Ⰰ 椀渀 漀爀搀攀爀 琀漀 瘀椀攀眀 漀甀琀瀀甀琀 漀昀 琀栀攀 䐀䈀䌀䌀 倀䄀䜀䔀⠀⤀ 挀漀洀洀愀渀搀Ⰰ㰀戀爀㸀ഀഀ otherwise we won't see any output at all.
਍㰀戀爀㸀ഀഀ ਍ഀഀ DBCC TRACEON (3604)
਍䜀伀㰀戀爀㸀ഀഀ
਍ഀഀ ਍ഀഀ Since a database usually consists of several datafiles, a page in a database, is uniquely identified using the "file_id"
਍㰀䤀㸀愀渀搀㰀⼀䤀㸀 琀栀攀 ∀瀀愀最攀渀甀洀戀攀爀∀ ⠀椀渀 琀栀愀琀 昀椀氀攀⤀Ⰰ 氀椀欀攀 昀漀爀 攀砀愀洀瀀氀攀 ⠀㄀Ⰰ㌀㔀㔀⤀Ⰰ 洀攀愀渀椀渀最 瀀愀最攀 ㌀㔀㔀 椀渀 昀椀氀攀 渀甀洀戀攀爀 ㄀⸀㰀戀爀㸀ഀഀ let's first see which file_id's exists in our sales database. This is shown in the figure below.
਍䨀甀猀琀 甀猀攀 琀栀攀 焀甀攀爀礀 㰀䈀㸀∀猀攀氀攀挀琀 ⨀ 昀爀漀洀 猀礀猀昀椀氀攀猀∀㰀⼀䈀㸀⸀㰀戀爀㸀ഀഀ
਍㰀椀洀最 猀爀挀㴀∀猀焀氀挀漀爀爀㄀⸀樀瀀最∀ 愀氀椀最渀㴀∀挀攀渀琀攀爀∀⼀㸀ഀഀ
਍匀漀Ⰰ 氀攀琀✀猀 琀愀欀攀 愀 氀漀漀欀 愀琀 琀栀攀 搀愀琀愀戀愀猀攀 瀀愀最攀 椀渀 昀椀氀攀 渀甀洀戀攀爀 ㌀ ⠀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀⸀渀搀昀⤀Ⰰ 眀栀椀挀栀 挀漀渀琀愀椀渀猀 琀栀攀 琀栀爀攀攀 爀攀挀漀爀搀猀⸀㰀戀爀㸀ഀഀ We are going to use DBCC PAGE() for that. The command takes a number of parameters, which are:
਍㰀䤀㸀搀愀琀愀戀愀猀攀Ⰰ 昀椀氀攀开椀搀Ⰰ 瀀愀最攀 渀甀洀戀攀爀Ⰰ 漀甀琀瀀甀琀开洀漀搀攀⸀㰀⼀䤀㸀㰀戀爀㸀ഀഀ So, the following would be our statement, to view the contents of page 8:
਍㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䐀䈀䌀䌀 倀䄀䜀䔀⠀✀猀愀氀攀猀✀Ⰰ㌀Ⰰ㠀Ⰰ㌀⤀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍吀栀椀猀 挀漀洀洀愀渀搀 瀀爀漀搀甀挀攀猀 愀 氀漀琀 漀昀 漀甀琀瀀甀琀Ⰰ 漀昀 眀栀椀挀栀 戀攀氀漀眀 愀 猀洀愀氀氀 猀愀洀瀀氀攀 椀猀 猀栀漀眀渀㨀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ..
਍匀氀漀琀 　 䌀漀氀甀洀渀 ㈀ 伀昀昀猀攀琀 　砀㄀㐀 䰀攀渀最琀栀 㔀 䰀攀渀最琀栀 ⠀瀀栀礀猀椀挀愀氀⤀ 㔀㰀戀爀㸀ഀഀ EMP_NAME = Harry
਍匀氀漀琀 　 䌀漀氀甀洀渀 ㌀ 伀昀昀猀攀琀 　砀㠀 䰀攀渀最琀栀 㔀 䰀攀渀最琀栀 ⠀瀀栀礀猀椀挀愀氀⤀ 㔀㰀戀爀㸀ഀഀ SALARY = 2000.50
਍⸀⸀㰀戀爀㸀ഀഀ etc..
਍㰀戀爀㸀ഀഀ ਍䄀渀礀眀愀礀Ⰰ 眀攀 栀愀瘀攀 猀栀漀眀渀 琀栀愀琀 琀栀攀 爀攀挀漀爀搀猀 愀爀攀 椀渀搀攀攀搀 猀琀漀爀攀搀 椀渀 瀀愀最攀 㠀⸀㰀戀爀㸀ഀഀ Also, we have learned how to use the DBCC PAGE(0) command.
਍㰀戀爀㸀ഀഀ

Section 2.2: Creating a soft corruption in the page:

਍ഀഀ Now, with our hexeditor, we are going to open the "c:\mssql\data\SALES_DATA_01.ndf" datafile.
਍䘀椀爀猀琀Ⰰ 漀昀挀漀甀爀猀攀Ⰰ 眀攀 瀀甀琀 琀栀攀 猀愀氀攀猀 搀愀琀愀戀愀猀攀 漀昀昀氀椀渀攀Ⰰ 甀猀椀渀最 琀栀攀 䴀愀渀愀最攀洀攀渀琀 䌀漀渀猀漀氀攀 ⠀匀匀䴀匀⤀Ⰰ 漀爀 眀椀琀栀 琀栀攀 昀漀氀氀漀眀椀渀最㰀戀爀㸀ഀഀ SQL command:
਍㰀戀爀㸀ഀഀ alter database sales set offline
਍㰀戀爀㸀ഀഀ So, now start your hexeditor (like "xvi32") and open "c:\mssql\data\SALES_DATA_01.ndf"
਍匀椀渀挀攀 瀀愀最攀 㠀 猀琀愀爀琀猀 愀琀 㠀 砀 㠀㄀㤀㈀㴀 㘀㔀㔀㌀㘀Ⰰ 眀栀椀挀栀 椀猀 ∀㄀　　　　∀ 椀渀 栀攀砀椀搀攀挀椀洀愀氀Ⰰ 氀攀琀✀猀 猀挀爀漀氀氀 搀漀眀渀 琀漀 漀昀昀猀攀琀 ㄀　　　　⸀㰀戀爀㸀ഀഀ Below you can see the contents of the file, at that position.
਍㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ As you can clearly see in the right pane, you see the names of our records, like "Albert".
਍㰀戀爀㸀ഀഀ Now, let's "emulate" a soft corruption in that page. Navigate to the string Albert and replace that string
਍眀椀琀栀 ∀一攀氀猀漀渀∀⸀ 圀栀愀琀 眀攀 栀愀瘀攀 搀漀渀攀 栀攀爀攀Ⰰ 椀猀 樀甀猀琀 爀攀瀀氀愀挀攀 愀 瀀椀攀挀攀 漀昀 琀攀砀琀Ⰰ 眀椀琀栀 猀漀洀攀 漀琀栀攀爀 挀栀愀爀愀挀琀攀爀猀 漀昀 琀栀攀 猀愀洀攀 氀攀渀最琀栀⸀㰀戀爀㸀ഀഀ Now, save the file, and close your hexeditor.
਍匀攀挀漀渀搀氀礀Ⰰ 琀爀礀 琀漀 瀀甀琀 琀栀攀 搀愀琀愀戀愀猀攀 漀渀氀椀渀攀Ⰰ 甀猀椀渀最 ∀愀氀琀攀爀 搀愀琀愀戀愀猀攀 猀愀氀攀猀 猀攀琀 漀渀氀椀渀攀∀⸀㰀戀爀㸀ഀഀ
਍䄀猀 礀漀甀 猀攀攀Ⰰ 琀栀攀 搀愀琀愀戀愀猀攀 眀椀氀氀 漀瀀攀渀 眀椀琀栀漀甀琀 愀渀礀 挀漀洀瀀氀愀椀渀琀猀⸀ 䈀甀琀Ⰰ 愀挀琀甀愀氀氀礀 眀攀 㰀䤀㸀栀愀瘀攀㰀⼀䤀㸀 愀氀琀攀爀攀搀 愀 瀀愀最攀℀㰀戀爀㸀ഀഀ Why does SQL Server does NOT throw an error message at us?
਍㰀戀爀㸀ഀഀ It would have done that right away, at the moment it was trying to set the database online, if we would have altered
਍愀 㰀䈀㸀挀爀椀琀椀挀愀氀 瀀愀最攀㰀⼀䈀㸀Ⰰ 氀椀欀攀 瀀愀最攀 　 ⠀琀栀攀 昀椀氀攀栀攀愀搀攀爀⤀Ⰰ 漀爀 琀栀攀 戀漀漀琀瀀愀最攀 ⠀瀀愀最攀 㤀 椀渀 琀栀攀 ⸀洀搀昀 昀椀氀攀⤀⸀㰀戀爀㸀ഀഀ Now, we cannot expect SQL Server to check all pages of the database. This is just an ordinary datapage.
਍䈀甀琀 椀琀 眀椀氀氀 搀攀琀攀挀琀 琀栀攀 ∀挀漀爀爀甀瀀琀椀漀渀∀ 愀琀 琀栀攀 洀漀洀攀渀琀 椀琀 愀挀挀攀猀猀攀猀 琀栀愀琀 瀀愀最攀⸀㰀戀爀㸀ഀഀ
਍㰀䈀㸀匀甀猀瀀攀挀琀 䴀漀搀攀㨀㰀⼀䈀㸀 䤀昀 礀漀甀 栀愀瘀攀 猀漀洀攀 猀漀爀琀 漀昀 挀漀爀爀甀瀀琀椀漀渀Ⰰ 愀渀搀 昀漀爀 攀砀愀洀瀀氀攀Ⰰ 愀昀琀攀爀 愀 爀攀猀琀愀爀琀 匀儀䰀 匀攀爀瘀攀爀 渀攀攀搀猀㰀戀爀㸀ഀഀ to rollforward or rollback transactions, but it can't for some reason (for example, due to one or more corrupt tables),
਍椀琀 眀椀氀氀 氀椀欀攀氀礀 瀀氀愀挀攀 琀栀攀 搀愀琀愀戀愀猀攀 椀渀 㰀䈀㸀∀匀甀猀瀀攀挀琀∀㰀⼀䈀㸀 洀漀搀攀⸀㰀戀爀㸀ഀഀ There are other reasons too, as to why SQL Server might place a database in "Suspect" mode. In later chapters, we will see
਍栀漀眀 琀漀 搀攀愀氀 眀椀琀栀 琀栀椀猀 猀琀愀琀攀 漀昀 愀 搀愀琀愀戀愀猀攀⸀㰀戀爀㸀ഀഀ
਍ഀഀ Let's try to query the EMPLOYEE table:
਍㰀戀爀㸀ഀഀ ਍ഀഀ SELECT * FROM EMPLOYEE
਍㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍吀栀椀猀 眀椀氀氀 匀儀䰀 匀攀爀瘀攀爀 愀挀挀攀猀猀攀猀 琀栀攀 瀀愀最攀Ⰰ 愀渀搀 椀琀 眀椀氀氀 搀椀猀挀漀瘀攀爀 琀栀攀 愀氀琀攀爀愀琀椀漀渀 眀攀 栀愀瘀攀 搀漀渀攀⸀㰀戀爀㸀ഀഀ The following output appears:
਍㰀戀爀㸀ഀഀ ਍ഀഀ Msg 824, Level 24, State 2, Line 1
਍匀儀䰀 匀攀爀瘀攀爀 搀攀琀攀挀琀攀搀 愀 氀漀最椀挀愀氀 挀漀渀猀椀猀琀攀渀挀礀ⴀ戀愀猀攀搀 䤀⼀伀 攀爀爀漀爀㨀 椀渀挀漀爀爀攀挀琀 挀栀攀挀欀猀甀洀 ⠀攀砀瀀攀挀琀攀搀㨀 　砀㜀㤀㐀㤀㘀㔀攀昀㬀 愀挀琀甀愀氀㨀 　砀㜀㈀㐀攀㘀挀㘀㘀⤀⸀ 㰀戀爀㸀ഀഀ It occurred during a read of page (3:8) in database ID 5 at offset 0x00000000010000 in file 'c:\mssql\data\SALES_DATA_01.ndf'.
਍䄀搀搀椀琀椀漀渀愀氀 洀攀猀猀愀最攀猀 椀渀 琀栀攀 匀儀䰀 匀攀爀瘀攀爀 攀爀爀漀爀 氀漀最 漀爀 猀礀猀琀攀洀 攀瘀攀渀琀 氀漀最 洀愀礀 瀀爀漀瘀椀搀攀 洀漀爀攀 搀攀琀愀椀氀⸀ 㰀戀爀㸀ഀഀ This is a severe error condition that threatens database integrity and must be corrected immediately.
਍㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍一漀琀攀 琀栀愀琀 椀渀搀攀攀搀 琀栀攀 瀀愀最攀 椀渀瘀漀氀瘀攀搀 眀愀猀 瀀愀最攀 㠀 ⠀椀渀 昀椀氀攀 渀漀 ㌀⤀⸀ 䄀琀 琀栀攀 洀漀洀攀渀琀 琀栀愀琀 匀儀䰀 匀攀爀瘀攀爀 愀挀挀攀猀猀攀搀 琀栀攀 瀀愀最攀Ⰰ 椀琀 眀愀猀 搀椀猀挀漀瘀攀爀攀搀㰀戀爀㸀ഀഀ that the current checksum is different from the checksum stored in the pageheader.
਍䤀渀搀攀攀搀Ⰰ 戀礀 愀氀琀攀爀椀渀最 琀栀攀 䔀洀瀀氀漀礀攀攀 渀愀洀攀Ⰰ 眀攀 愀氀琀攀爀攀搀 ⠀愀 渀攀眀氀礀 挀漀洀瀀甀琀攀搀⤀ 挀栀攀挀欀猀甀洀 愀猀 眀攀氀氀 ℀㰀戀爀⸀ഀഀ
਍伀欀Ⰰ 戀甀琀 栀漀眀 琀漀 爀攀挀漀瘀攀爀 昀爀漀洀 琀栀椀猀 猀椀琀甀愀琀椀漀渀⸀㰀戀爀㸀ഀഀ
਍䐀椀搀 礀漀甀 渀漀琀椀挀攀搀 栀漀眀 匀儀䰀 猀攀爀瘀攀爀 漀渀氀礀 挀漀洀瀀氀愀椀渀攀搀 愀戀漀甀琀 琀栀攀 眀爀漀渀最 挀栀攀挀欀猀甀洀㼀㰀戀爀㸀ഀഀ Physically, there is nothing wrong with the page and it's data. We only changed an Employee name.
਍㰀戀爀㸀ഀഀ Take a look at this. In SSMS, rightclick the sales database, and chooce "properties".
਍䤀渀 琀栀攀 ∀伀瀀琀椀漀渀猀∀ 瀀愀渀攀Ⰰ 礀漀甀 眀椀氀氀 猀攀攀 愀渀 漀瀀琀椀漀渀 挀愀氀氀攀搀 ∀瀀愀最攀 瘀攀爀椀昀礀∀⸀ 吀栀攀 瀀漀猀猀椀戀氀攀 漀瀀琀椀漀渀猀 愀爀攀㨀㰀戀爀㸀ഀഀ Torn Page Detection, Checksum, None.
਍㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ
਍一漀眀Ⰰ 樀甀猀琀 挀栀愀渀最攀 琀栀攀 ∀瀀愀最攀 瘀攀爀椀昀礀∀ 琀漀 一漀渀攀Ⰰ 愀渀搀 爀甀渀 琀栀攀 焀甀攀爀礀 愀最愀椀渀⸀㰀戀爀㸀ഀഀ
਍吀栀椀猀 琀椀洀攀Ⰰ 匀儀䰀 匀攀爀瘀攀爀 搀漀攀猀 渀漀琀 挀愀爀攀 愀渀礀洀漀爀攀 漀渀 挀栀愀渀最攀搀 瀀愀最攀 挀栀攀挀欀猀甀洀猀Ⰰ 猀漀 椀琀 樀甀猀琀 爀攀琀甀爀渀猀 琀栀攀 爀攀挀漀爀搀猀 昀爀漀洀 琀栀攀 琀愀戀氀攀Ⰰ㰀戀爀㸀ഀഀ although "formally" a corruption exists.
਍㰀戀爀㸀ഀഀ It's also instructive to run the DBCC CHECKTABLE() command on the EMPLOYEE table.
਍㰀戀爀㸀ഀഀ While the page verify option is still none, DBCC CHECKTABLE(EMPLOYEE) comes with the following output.
਍㰀戀爀㸀ഀഀ ਍ഀഀ ਍䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀⠀䔀䴀倀䰀伀夀䔀䔀⤀㰀戀爀㸀ഀഀ
਍䐀䈀䌀䌀 爀攀猀甀氀琀猀 昀漀爀 ✀䔀䴀倀䰀伀夀䔀䔀✀⸀㰀戀爀㸀ഀഀ Msg 8951, Level 16, State 1, Line 1
਍吀愀戀氀攀 攀爀爀漀爀㨀 吀愀戀氀攀 ✀䔀䴀倀䰀伀夀䔀䔀✀ ⠀䤀䐀 ㈀　㜀㌀　㔀㠀㐀㈀㄀⤀⸀ 䐀愀琀愀 爀漀眀 搀漀攀猀 渀漀琀 栀愀瘀攀 愀 洀愀琀挀栀椀渀最 椀渀搀攀砀 爀漀眀 椀渀 椀渀搀攀砀 ✀椀渀搀砀开攀洀瀀氀漀礀攀攀开攀洀瀀渀愀洀攀✀ ⠀䤀䐀 ㈀⤀⸀ ഀഀ Possible missing or invalid keys for the index row matching:
਍䴀猀最 㠀㤀㔀㔀Ⰰ 䰀攀瘀攀氀 ㄀㘀Ⰰ 匀琀愀琀攀 ㄀Ⰰ 䰀椀渀攀 ㄀㰀戀爀㸀ഀഀ Data row (3:8:2) identified by (HEAP RID = (3:8:2)) with index values (EMP_NAME = 'Nelson' and HEAP RID = (3:8:2)).
਍䴀猀最 㠀㤀㔀㈀Ⰰ 䰀攀瘀攀氀 ㄀㘀Ⰰ 匀琀愀琀攀 ㄀Ⰰ 䰀椀渀攀 ㄀㰀戀爀㸀ഀഀ Table error: Table 'EMPLOYEE' (ID 2073058421). Index row in index 'indx_employee_empname' (ID 2) does not match any data row.
਍倀漀猀猀椀戀氀攀 攀砀琀爀愀 漀爀 椀渀瘀愀氀椀搀 欀攀礀猀 昀漀爀㨀㰀戀爀㸀ഀഀ Msg 8956, Level 16, State 1, Line 1
਍䤀渀搀攀砀 爀漀眀 ⠀㐀㨀㠀㨀　⤀ 眀椀琀栀 瘀愀氀甀攀猀 ⠀䔀䴀倀开一䄀䴀䔀 㴀 ✀䄀氀戀攀爀琀✀ 愀渀搀 䠀䔀䄀倀 刀䤀䐀 㴀 ⠀㌀㨀㠀㨀㈀⤀⤀ 瀀漀椀渀琀椀渀最 琀漀 琀栀攀 搀愀琀愀 爀漀眀 椀搀攀渀琀椀昀椀攀搀 戀礀 ⠀䠀䔀䄀倀 刀䤀䐀 㴀 ⠀㌀㨀㠀㨀㈀⤀⤀⸀㰀戀爀㸀ഀഀ There are 3 rows in 1 pages for object "EMPLOYEE".
਍䌀䠀䔀䌀䬀吀䄀䈀䰀䔀 昀漀甀渀搀 　 愀氀氀漀挀愀琀椀漀渀 攀爀爀漀爀猀 愀渀搀 ㈀ 挀漀渀猀椀猀琀攀渀挀礀 攀爀爀漀爀猀 椀渀 琀愀戀氀攀 ✀䔀䴀倀䰀伀夀䔀䔀✀ ⠀漀戀樀攀挀琀 䤀䐀 ㈀　㜀㌀　㔀㠀㐀㈀㄀⤀⸀㰀戀爀㸀ഀഀ repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (SALES.dbo.EMPLOYEE).
਍䐀䈀䌀䌀 攀砀攀挀甀琀椀漀渀 挀漀洀瀀氀攀琀攀搀⸀ 䤀昀 䐀䈀䌀䌀 瀀爀椀渀琀攀搀 攀爀爀漀爀 洀攀猀猀愀最攀猀Ⰰ 挀漀渀琀愀挀琀 礀漀甀爀 猀礀猀琀攀洀 愀搀洀椀渀椀猀琀爀愀琀漀爀⸀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍一漀琀攀 愀氀猀漀Ⰰ 栀漀眀 琀栀攀 挀漀洀洀愀渀搀 昀漀甀渀搀 琀栀愀琀 琀栀攀 椀渀搀攀砀 攀渀琀爀礀 ⠀漀昀 䄀氀戀攀爀琀⤀ 搀漀攀猀 渀漀琀 洀愀琀挀栀 琀栀攀 琀愀戀氀攀 搀愀琀愀 ⠀眀栀椀挀栀 椀猀 一攀氀猀漀渀⤀⸀㰀戀爀㸀ഀഀ
਍䤀渀 昀愀挀琀Ⰰ 眀椀琀栀 琀栀椀猀 琀礀瀀攀 漀昀 猀漀昀琀 挀漀爀爀甀瀀琀椀漀渀Ⰰ 礀漀甀 挀愀渀 ∀爀攀瀀愀椀爀∀ 琀栀攀 琀愀戀氀攀 ⠀愀挀琀甀愀氀氀礀 瀀愀最攀 㠀⤀Ⰰ 愀渀搀 琀栀攀 椀渀搀攀砀 攀渀琀爀礀Ⰰ 戀礀 甀猀椀渀最㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀⠀䔀䴀倀䰀伀夀䔀䔀Ⰰ爀攀瀀愀椀爀开爀攀戀甀椀氀搀⤀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍吀栀椀猀 挀漀洀洀愀渀搀 眀椀氀氀 爀攀瀀愀椀爀 琀栀攀 挀栀攀挀欀猀甀洀Ⰰ 愀渀搀 洀愀欀攀猀 猀甀爀攀 琀栀愀琀 琀栀攀 椀渀搀攀砀 椀猀 椀渀 猀礀渀挀 眀椀琀栀 琀栀攀 琀愀戀氀攀搀愀琀愀⸀㰀戀爀㸀ഀഀ If you change the "page verify" option back to CHECKSUM, you can query the table and no inconsistencies are reported.
਍㰀戀爀㸀ഀഀ I admit that this section was more for demonstration purposes only, than for a real recovery operation.
਍䄀渀礀眀愀礀Ⰰ 椀琀 氀攀愀爀渀攀搀 甀猀 愀 氀漀琀 愀戀漀甀琀 挀攀爀琀愀椀渀 搀攀琀愀椀氀猀 漀昀 挀漀爀爀甀瀀琀椀漀渀⸀㰀戀爀㸀ഀഀ
਍匀甀瀀瀀漀猀攀 礀漀甀 攀渀挀漀甀渀琀攀爀 愀 猀椀琀甀愀琀椀漀渀 猀椀洀椀氀愀爀 愀猀 愀戀漀瘀攀Ⰰ 眀椀琀栀 洀愀渀礀 挀栀攀挀欀猀甀洀 攀爀爀漀爀猀 漀渀 洀愀渀礀 瀀愀最攀猀⸀㰀戀爀㸀ഀഀ Then, by just changing the "page verify" database option, you might be able to select all records of the effected tables.
਍䈀甀琀 琀栀愀琀 挀漀渀搀椀琀椀漀渀 椀猀 愀 戀椀琀 ∀搀椀猀琀甀爀戀椀渀最∀Ⰰ 漀昀挀漀甀爀猀攀⸀ 夀漀甀 洀椀最栀琀 挀漀渀猀椀搀攀爀㨀㰀戀爀㸀ഀഀ
਍㰀䈀㸀㄀㨀 唀猀攀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀⠀琀愀戀氀攀开渀愀洀攀Ⰰ爀攀瀀愀椀爀开爀攀戀甀椀氀搀⤀㰀⼀䈀㸀㰀戀爀㸀ഀഀ Just try the upper command on all effected tables.
਍㰀戀爀㸀ഀഀ 2: "rebuilding" the effected tables into new tables.
਍䔀猀猀攀渀琀椀愀氀氀礀 琀栀椀猀 洀攀愀渀猀 瀀甀琀琀椀渀最 琀栀攀 搀愀琀愀 椀渀琀漀 㰀椀㸀渀攀眀 瀀愀最攀猀㰀⼀䤀㸀Ⰰ 眀椀琀栀 挀漀爀爀攀挀琀 挀栀攀挀欀猀甀洀猀⸀㰀戀爀㸀ഀഀ You can simply insert the data from the old tables into new tables (if "select *" works, with page verify=NONE)
਍夀漀甀 挀愀渀 甀猀攀 猀琀愀琀攀洀攀渀琀猀 氀椀欀攀㨀㰀戀爀㸀ഀഀ
਍匀䔀䰀䔀䌀吀 ⨀ 䤀一吀伀 琀愀戀氀攀开戀  㰀䤀㸀ⴀⴀ 挀爀攀愀琀攀 琀愀戀氀攀开戀 ∀漀渀 琀栀攀 昀氀礀∀㰀⼀䤀㸀㰀戀爀㸀ഀഀ FROM table_a
਍㰀戀爀㸀ഀഀ INSERT INTO table_b -- table_b need to exist already
਍匀䔀䰀䔀䌀吀 ⨀ 䘀刀伀䴀 琀愀戀氀攀开愀㰀戀爀㸀ഀഀ
਍⠀一漀琀攀㨀 椀琀 猀漀甀渀搀猀 攀愀猀礀Ⰰ 戀甀琀 挀漀甀氀搀 戀攀 挀漀洀瀀氀椀挀愀琀攀搀 搀甀攀 琀漀 倀䬀ⴀ䘀䬀 爀攀氀愀琀椀漀渀猀Ⰰ 爀攀渀愀洀椀渀最 琀愀戀氀攀猀Ⰰ 琀爀椀最最攀爀猀 攀琀挀⸀⸀⤀㰀戀爀㸀ഀഀ
਍㰀䈀㸀㌀㨀 刀攀猀琀漀爀攀 椀渀搀椀瘀椀搀甀愀氀 瀀愀最攀猀 昀爀漀洀 戀愀挀欀甀瀀⸀㰀⼀䈀㸀㰀戀爀㸀ഀഀ In principle, if your database uses the "Full Recovery model" (or Bulk-logged model), then you
਍愀爀攀 愀戀氀攀 琀漀 爀攀猀琀漀爀攀 愀渀 椀渀搀椀瘀椀搀甀愀氀 瀀愀最攀Ⰰ 椀渀 挀愀猀攀 琀栀愀琀 瀀愀最攀 椀猀 挀漀爀爀甀瀀琀⸀㰀戀爀㸀ഀഀ The syntax is like in the following example:
਍㰀戀爀㸀ഀഀ RESTORE DATABASE SALES PAGE = '2:155' FROM DISK='d:\backups\sales.dmp' WITH RECOVERY
਍㰀戀爀㸀ഀഀ Here, we restore the pagenumber 155 in fileno 2, from the diskbased backup.
਍㰀戀爀㸀ഀഀ
਍㰀栀㈀㸀䌀栀愀瀀琀攀爀 ㌀㨀 匀攀瘀攀爀攀 挀漀爀爀甀瀀琀椀漀渀 漀渀 漀爀搀椀渀愀爀礀 搀愀琀愀 瀀愀最攀猀⸀㰀⼀栀㈀㸀ഀഀ ਍䤀渀 猀攀挀琀椀漀渀 ㈀Ⰰ 眀攀 漀渀氀礀 挀栀愀渀最攀搀 愀渀 攀洀瀀氀漀礀攀攀 渀愀洀攀 椀渀 愀 搀愀琀愀 瀀愀最攀Ⰰ 甀猀椀渀最 愀 栀攀砀 攀搀椀琀漀爀⸀㰀戀爀㸀ഀഀ In fact, it wasn't a severe form of corruption, but it was corruption allright.
਍㰀戀爀㸀ഀഀ In this section, I will put a lot of records into the EMPLOYEE table. Then, when the database is offline,
਍䤀 眀椀氀氀 甀猀攀 砀瘀椀㌀㈀ 琀漀 眀椀瀀攀 漀甀琀 瀀愀最攀猀 一 愀渀搀 一⬀㄀⸀ 䤀 眀椀氀氀 猀琀愀爀琀 猀漀洀攀眀栀攀爀攀 ∀椀渀 琀栀攀 洀椀搀搀氀攀∀ 漀昀 瀀愀最攀 一Ⰰ 愀氀氀 琀栀攀 眀愀礀㰀戀爀㸀ഀഀ through to the middle of page N+1, and fill that region with "all zero's".
਍䄀挀琀甀愀氀氀礀Ⰰ 㰀䤀㸀琀栀椀猀 椀猀㰀⼀䤀㸀 愀 猀攀瘀攀爀攀 挀漀爀爀甀瀀琀椀漀渀 戀攀挀愀甀猀攀 䤀 挀爀漀猀猀 瀀愀最攀戀漀甀渀搀愀爀椀攀猀 愀渀搀 愀氀猀漀 搀攀猀琀爀漀礀 愀 瀀愀最攀 栀攀愀搀攀爀⸀㰀戀爀㸀ഀഀ
਍圀栀愀琀 眀椀氀氀 戀攀 琀栀攀 攀昀昀攀挀琀Ⰰ 愀渀搀 栀漀眀 挀愀渀 眀攀 爀攀猀漀氀瘀攀 琀栀攀 猀椀琀甀愀琀椀漀渀㼀㰀戀爀㸀ഀഀ
਍䤀昀 礀漀甀 眀愀渀琀 昀漀氀氀漀眀 愀氀漀渀最 琀栀攀 攀砀攀爀挀椀猀攀Ⰰ 琀栀攀渀 搀攀氀攀琀攀 琀栀攀 猀愀氀攀猀 搀愀琀愀戀愀猀攀Ⰰ 愀渀搀 挀爀攀愀琀攀 椀琀 愀最愀椀渀 甀猀椀渀最 琀栀攀 猀挀爀椀瀀琀 漀昀 猀攀挀琀椀漀渀 ㄀⸀㰀戀爀㸀ഀഀ Then, only create the EMPLOYEE and EMPLOYEE2 tables, but don't insert the 3 records per table.
਍䄀氀猀漀Ⰰ 搀漀 渀漀琀 挀爀攀愀琀攀 琀栀攀 椀渀搀攀砀 ⠀琀栀攀 氀愀猀琀 猀琀愀琀攀洀攀渀琀⤀⸀㰀戀爀㸀ഀഀ
਍伀欀Ⰰ 氀攀琀✀猀 椀渀猀攀爀琀 愀 戀甀渀挀栀 漀昀 爀攀挀漀爀搀猀 椀渀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ Use the following script to insert 9999 records in the EMPLOYEE table
਍㰀戀爀㸀ഀഀ ਍ഀഀ DECLARE @i INT
਍㰀戀爀㸀ഀഀ SET @i=1
਍㰀戀爀㸀ഀഀ WHILE (@i<10000)
਍  䈀䔀䜀䤀一㰀戀爀㸀ഀഀ INSERT INTO EMPLOYEE
਍     嘀䄀䰀唀䔀匀 ⠀䀀椀Ⰰ✀䠀愀爀爀礀✀Ⰰ㄀　　　⤀㰀戀爀㸀ഀഀ SELECT @i=@i+1
਍   䔀一䐀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍吀栀愀渀欀猀 琀漀 琀栀攀 䀀椀Ⰰ 眀攀 栀愀瘀攀 甀渀椀焀甀攀 爀攀挀漀爀搀猀 椀渀 琀栀攀 琀愀戀氀攀Ⰰ 愀氀琀栀漀甀最栀 琀栀攀 攀洀瀀氀漀礀攀攀 渀愀洀攀 愀渀搀 猀愀氀愀爀礀 愀爀攀 猀愀洀攀 椀渀 愀氀氀 爀攀挀漀爀搀猀⸀㰀戀爀㸀ഀഀ Actually, for our purpose, it does not matter at all, as long as we are able to destroy a few datapages,
਍眀椀琀栀 最漀漀搀 瀀愀最攀猀 漀渀 琀栀攀 ∀氀攀昀琀∀ 愀渀搀 ∀爀椀最栀琀∀ 漀昀 瀀愀最攀猀 一Ⰰ 愀渀搀 一⬀㄀⸀㰀戀爀㸀ഀഀ Ofcourse, if you like you can insert many more rows, by just varying the while clause, like for example using
਍⠀䀀椀㰀㄀　　　　　　⤀Ⰰ 眀栀椀挀栀 眀椀氀氀 椀渀猀攀爀琀 愀 洀椀氀氀椀漀渀 爀漀眀猀⸀㰀戀爀㸀ഀഀ
਍䄀挀琀甀愀氀氀礀Ⰰ 栀漀眀 洀愀渀礀 瀀愀最攀猀 愀爀攀 愀氀氀漀挀愀琀攀搀 琀漀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀 爀椀最栀琀 渀漀眀㼀 嘀愀爀椀漀甀猀 漀瀀琀椀漀渀猀 攀砀椀猀琀猀Ⰰ 戀甀琀 眀攀 挀愀渀 甀猀攀㰀戀爀㸀ഀഀ the DBCC PAGE() command again, and this time request information from "page 1" in "filenumber 3" (sales_data_01.ndf).
਍吀栀椀猀 椀猀 琀栀攀 倀愀最攀 䘀爀攀攀 匀瀀愀挀攀 ⠀倀匀䘀⤀ 瀀愀最攀Ⰰ 眀栀椀挀栀 猀琀漀爀攀猀 椀渀昀漀爀洀愀琀椀漀渀 愀戀漀甀琀 琀栀攀 愀氀氀漀挀愀琀椀漀渀 愀渀搀 昀爀攀攀 猀瀀愀挀攀 漀昀 瀀愀最攀猀⸀㰀戀爀㸀ഀഀ So, let's try that:
਍㰀戀爀㸀ഀഀ ਍ഀഀ DBCC TRACEON (3604)
਍䜀伀㰀戀爀㸀ഀഀ
਍䐀䈀䌀䌀 倀䄀䜀䔀⠀✀猀愀氀攀猀✀Ⰰ㌀Ⰰ㄀Ⰰ㌀⤀㰀戀爀㸀ഀഀ GO
਍㰀戀爀㸀ഀഀ Output:
਍㰀䤀㸀⠀猀欀椀瀀瀀椀渀最 愀 氀漀琀 漀昀 氀椀渀攀猀Ⰰ 漀渀氀礀 猀栀漀眀椀渀最 琀栀攀 氀愀猀琀 氀椀渀攀猀⸀⸀⸀⤀㰀⼀䤀㸀㰀戀爀㸀ഀഀ
਍倀䘀匀㨀 倀愀最攀 䄀氀氀漀挀 匀琀愀琀甀猀  䀀　砀㐀㐀㄀㘀䌀　　　㰀戀爀㸀ഀഀ
਍⠀㌀㨀　⤀        ⴀ ⠀㌀㨀㌀⤀        㴀     䄀䰀䰀伀䌀䄀吀䔀䐀   　开倀䌀吀开䘀唀䰀䰀㰀戀爀㸀                              ഀഀ (3:4) - (3:5) = NOT ALLOCATED 0_PCT_FULL
਍⠀㌀㨀㘀⤀        ⴀ ⠀㌀㨀㜀⤀        㴀     䄀䰀䰀伀䌀䄀吀䔀䐀   　开倀䌀吀开䘀唀䰀䰀㰀戀爀㸀                             ഀഀ (3:8) - = ALLOCATED 100_PCT_FULL Mixed Ext
਍⠀㌀㨀㤀⤀        ⴀ              㴀     䄀䰀䰀伀䌀䄀吀䔀䐀   　开倀䌀吀开䘀唀䰀䰀 䤀䄀䴀 倀愀最攀  䴀椀砀攀搀 䔀砀琀㰀戀爀㸀ഀഀ (3:10) - (3:16) = ALLOCATED 100_PCT_FULL Mixed Ext
਍⠀㌀㨀㄀㜀⤀       ⴀ ⠀㌀㨀㈀㌀⤀       㴀 一伀吀 䄀䰀䰀伀䌀䄀吀䔀䐀   　开倀䌀吀开䘀唀䰀䰀㰀戀爀㸀                              ഀഀ (3:24) - (3:50) = ALLOCATED 100_PCT_FULL
਍⠀㌀㨀㔀㄀⤀       ⴀ              㴀     䄀䰀䰀伀䌀䄀吀䔀䐀  㔀　开倀䌀吀开䘀唀䰀䰀㰀戀爀㸀                              ഀഀ (3:52) - (3:5119) = NOT ALLOCATED 0_PCT_FULL
਍㰀戀爀㸀ഀഀ ਍ഀഀ What we see here, is that for example pages 3:24 to 3:50 are allocated to the EMPLOYEE table, and are filled with records.
਍匀漀Ⰰ 椀昀 䤀 欀椀氀氀 瀀愀最攀猀 ㌀　 愀渀搀 ㌀㄀Ⰰ 眀攀 栀愀瘀攀 琀栀攀 猀椀琀甀愀琀椀漀渀 琀栀愀琀 眀攀 眀愀渀琀 琀漀 椀渀瘀攀猀琀椀最愀琀攀⸀㰀戀爀㸀ഀഀ
਍一攀砀琀Ⰰ 瀀甀琀 琀栀攀 猀愀氀攀猀 搀愀琀愀戀愀猀攀 漀昀昀氀椀渀攀⸀㰀戀爀㸀ഀഀ Start xvi32, and open the "c:\mssql\data\SALES_DATA_01.ndf" datafile. ਍㰀戀爀㸀ഀഀ In xvi32, we want to go to database page 30. And 30x8192=245760, or in hexidecimal that would be 3C000.
਍吀栀攀 ㌀㄀猀琀 瀀愀最攀Ⰰ 眀漀甀氀搀 猀琀愀爀琀 愀琀 ㌀䔀　　　⸀ 匀漀Ⰰ 䤀 眀椀氀氀 最漀 琀漀 猀漀洀攀眀栀攀爀攀 椀渀 戀攀琀眀攀攀渀 ㌀䌀　　　 愀渀搀 ㌀䔀　　　Ⰰ 愀渀搀 昀椀氀氀㰀戀爀㸀ഀഀ a region with all 0's, crossing the boundary to the next page.
਍㰀戀爀㸀ഀഀ Save, the file, and close xvi32.
਍一攀砀琀Ⰰ 瀀甀琀 琀栀攀 猀愀氀攀猀 搀愀琀愀戀愀猀攀 漀渀氀椀渀攀⸀ 䄀猀 攀砀瀀攀挀琀攀搀Ⰰ 琀栀攀 匀儀䰀 匀攀爀瘀攀爀 搀漀攀猀 渀漀琀 挀漀洀瀀氀愀椀渀 愀琀 愀氀氀⸀㰀戀爀㸀ഀഀ
਍一漀眀 氀攀琀✀猀 焀甀攀爀礀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍匀䔀䰀䔀䌀吀 ⨀ 䘀刀伀䴀 䔀䴀倀䰀伀夀䔀䔀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍圀攀 眀椀氀氀 猀攀攀 愀 渀甀洀戀攀爀 漀昀 爀攀挀漀爀搀猀 瀀愀猀猀 琀栀攀 猀挀爀攀攀渀Ⰰ 愀猀 匀儀䰀 匀攀爀瘀攀爀 眀椀氀氀 樀甀猀琀 爀攀愀搀 琀栀攀 琀愀戀氀攀 昀爀漀洀 琀栀攀 戀攀最椀渀渀椀渀最⸀㰀戀爀㸀ഀഀ Then, it will try to read the corrupt page, and an error message appears:
਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
਍䴀猀最 㠀㈀㐀Ⰰ 䰀攀瘀攀氀 ㈀㐀Ⰰ 匀琀愀琀攀 ㈀Ⰰ 䰀椀渀攀 ㄀㰀戀爀㸀ഀഀ SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x90ee5853; actual: 0x949c5001).
਍䤀琀 漀挀挀甀爀爀攀搀 搀甀爀椀渀最 愀 爀攀愀搀 漀昀 瀀愀最攀 ⠀㌀㨀㌀　⤀ 椀渀 搀愀琀愀戀愀猀攀 䤀䐀 㔀 愀琀 漀昀昀猀攀琀 　砀　　　　　　　　　㌀挀　　　 椀渀 昀椀氀攀 ✀挀㨀尀洀猀猀焀氀尀搀愀琀愀尀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀⸀渀搀昀✀⸀㰀戀爀㸀 ഀഀ This is a severe error condition that threatens database integrity and must be corrected immediately.
਍䌀漀洀瀀氀攀琀攀 愀 昀甀氀氀 搀愀琀愀戀愀猀攀 挀漀渀猀椀猀琀攀渀挀礀 挀栀攀挀欀 ⠀䐀䈀䌀䌀 䌀䠀䔀䌀䬀䐀䈀⤀⸀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍吀栀椀猀 琀椀洀攀Ⰰ 椀琀 眀椀氀氀 㰀䈀㸀渀漀琀 栀攀氀瀀㰀⼀䈀㸀 琀漀 瀀甀琀 琀栀攀 搀愀琀愀戀愀猀攀 漀瀀琀椀漀渀 ∀瀀愀最攀 瘀攀爀椀昀礀∀ 昀爀漀洀 ∀䌀䠀䔀䌀䬀匀唀䴀∀ 琀漀 ∀一伀一䔀∀Ⰰ 猀椀渀挀攀 琀栀椀猀 琀椀洀攀㰀戀爀㸀ഀഀ we not dealing with just a "soft" corruption. No, this time, two database pages are truly destroyed.
਍㰀戀爀㸀ഀഀ How can we recover from this situation?
਍䰀攀琀✀猀 琀爀礀 琀漀 爀攀瀀愀椀爀 琀栀攀 琀愀戀氀攀 眀椀琀栀㨀㰀戀爀㸀ഀഀ
਍䴀攀琀栀漀搀 ㄀⸀ 甀猀椀渀最 匀儀䰀 猀攀爀瘀攀爀 爀攀瀀愀椀爀 洀攀琀栀漀搀猀 氀椀欀攀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀⠀琀愀戀氀攀开渀愀洀攀Ⰰ 爀攀瀀愀椀爀开挀氀愀甀猀攀⤀⸀㰀戀爀㸀ഀഀ Method 2. Our own "monkey" method where we try to salvage as many rows as possible.
਍㰀戀爀㸀ഀഀ

Method 1: DBCC CHECKTABLE (table_name,repair_allow_data_loss)

਍ഀഀ Let's first SQL Server check the integrety of the table, using the DBCC CHECKTABLE(EMPLOYEE) command.
਍㰀戀爀㸀ഀഀ ਍ഀഀ DBCC CHECKTABLE(EMPLOYEE)
਍㰀戀爀㸀ഀഀ A lot of output appears, but the most important lines are the following:
਍㰀戀爀㸀ഀഀ ..
਍爀攀瀀愀椀爀开愀氀氀漀眀开搀愀琀愀开氀漀猀猀 椀猀 琀栀攀 洀椀渀椀洀甀洀 爀攀瀀愀椀爀 氀攀瘀攀氀 昀漀爀 琀栀攀 攀爀爀漀爀猀 昀漀甀渀搀 戀礀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀 ⠀匀䄀䰀䔀匀⸀搀戀漀⸀䔀䴀倀䰀伀夀䔀䔀⤀⸀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ Ok, so if we want SQL Server to repair the table, our only chance is by using
਍琀栀攀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀 ⠀䔀䴀倀䰀伀夀䔀䔀Ⰰ爀攀瀀愀椀爀开愀氀氀漀眀开搀愀琀愀开氀漀猀猀⤀ 挀漀洀洀愀渀搀⸀㰀戀爀㸀ഀഀ
਍䠀攀爀攀Ⰰ 匀儀䰀 匀攀爀瘀攀爀 眀愀爀渀猀 甀猀 琀栀愀琀 眀攀 洀愀礀 攀砀瀀攀爀椀攀渀挀攀 瀀漀猀猀椀戀氀攀 搀愀琀愀氀漀猀猀⸀ 䤀渀 漀甀爀 挀愀猀攀Ⰰ 琀栀攀 搀愀琀愀 椀渀 愀 昀攀眀 瀀愀最攀猀㰀戀爀㸀ഀഀ is really lost. But if we can save the majority of rows in the EMPLOYEE table, the damage is somewhat limited,
਍猀漀 氀攀琀✀猀 琀爀礀 琀栀愀琀 挀漀洀洀愀渀搀⸀㰀戀爀㸀ഀഀ
਍䘀椀爀猀琀Ⰰ 椀渀 漀爀搀攀爀 琀漀 甀猀攀 琀栀攀 挀漀洀洀愀渀搀Ⰰ 礀漀甀 渀攀攀搀 琀漀 瀀甀琀 琀栀攀 搀愀琀愀戀愀猀攀 椀渀 ∀匀椀渀最氀攀 唀猀攀爀 䴀漀搀攀∀⸀ഀഀ After you did that, we try the SQL Server repair method:
਍㰀戀爀㸀ഀഀ ਍ഀഀ DBCC CHECKTABLE (EMPLOYEE,repair_allow_data_loss)
਍㰀戀爀㸀ഀഀ A lot of output appears, but the most important lines are the following:
਍㰀戀爀㸀ഀഀ ..
਍刀攀瀀愀椀爀㨀 吀栀攀 瀀愀最攀 ⠀㌀㨀㌀　⤀ 栀愀猀 戀攀攀渀 搀攀愀氀氀漀挀愀琀攀搀 昀爀漀洀 漀戀樀攀挀琀 䤀䐀 ㈀　㜀㌀　㔀㠀㐀㈀㄀Ⰰ 椀渀搀攀砀 䤀䐀 　Ⰰ㰀戀爀㸀ഀഀ partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data).
਍刀攀瀀愀椀爀㨀 吀栀攀 瀀愀最攀 ⠀㌀㨀㌀㄀⤀ 栀愀猀 戀攀攀渀 搀攀愀氀氀漀挀愀琀攀搀 昀爀漀洀 漀戀樀攀挀琀 䤀䐀 ㈀　㜀㌀　㔀㠀㐀㈀㄀Ⰰ㰀戀爀㸀ഀഀ index ID 0, partition ID 72057594038321152, alloc unit ID 72057594042318848 (type In-row data).
਍⸀⸀㰀戀爀㸀ഀഀ Msg 8928, Level 16, State 1, Line 1
਍吀栀攀 攀爀爀漀爀 栀愀猀 戀攀攀渀 爀攀瀀愀椀爀攀搀⸀㰀戀爀㸀ഀഀ ..
਍㰀戀爀㸀ഀഀ ਍ഀഀ Yes, we have succes! SQL Server essentially deallocated pages 30 and 31 (as expected), and moved some pointers
਍猀漀 琀栀愀琀 愀氀氀 瀀愀最攀猀 漀昀 琀栀攀 琀愀戀氀攀 栀愀瘀攀 瀀漀椀渀琀攀爀猀 琀漀 琀栀攀 昀漀爀洀攀爀 愀渀搀 渀攀砀琀 瀀愀最攀⸀㰀戀爀㸀ഀഀ The table is repaired, only we miss the data from pages 30 and 31, which was unavoidable ofcourse.
਍䄀最愀椀渀Ⰰ 眀攀 挀愀渀 瀀攀爀昀漀爀洀 焀甀攀爀椀攀猀 漀渀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀 椀渀 琀栀攀 甀猀甀愀氀 眀愀礀⸀㰀戀爀㸀ഀഀ
਍吀栀攀 昀漀氀氀漀眀椀渀最 昀椀最甀爀攀 猀栀漀眀猀 挀氀攀愀爀氀礀 眀栀愀琀 椀猀 洀椀猀猀椀渀最㨀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍匀䔀䰀䔀䌀吀 ⨀ 䘀刀伀䴀 䔀䴀倀䰀伀夀䔀䔀㰀戀爀㸀ഀഀ
਍㰀䤀㸀猀欀椀瀀瀀椀渀最 愀 氀漀琀 漀昀 爀攀挀漀爀搀猀㰀⼀䤀㸀㰀戀爀㸀ഀഀ ..
਍㌀㤀㠀㜀        䠀愀爀爀礀                ㄀　　　⸀　　㰀戀爀㸀ഀഀ 3988 Harry 1000.00
਍㌀㤀㠀㤀        䠀愀爀爀礀                ㄀　　　⸀　　㰀戀爀㸀ഀഀ 3990 Harry 1000.00 <- here the "gap" starts
਍㐀㔀㘀㄀        䠀愀爀爀礀                ㄀　　　⸀　　 㰀ⴀ 栀攀爀攀 琀栀攀 ∀最愀瀀∀ 攀渀搀猀㰀戀爀㸀ഀഀ 4562 Harry 1000.00
਍㐀㔀㘀㌀        䠀愀爀爀礀                ㄀　　　⸀　　㰀戀爀㸀ഀഀ 4564 Harry 1000.00
਍㐀㔀㘀㔀        䠀愀爀爀礀                ㄀　　　⸀　　㰀戀爀㸀ഀഀ 4566 Harry 1000.00
਍㐀㔀㘀㜀        䠀愀爀爀礀                ㄀　　　⸀　　㰀戀爀㸀ഀഀ ..
਍㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䘀爀漀洀 琀栀攀 㤀㤀㤀㤀 爀攀挀漀爀搀猀Ⰰ 眀攀 洀椀猀猀 㔀㜀㄀ 爀攀挀漀爀搀猀⸀ 吀栀愀琀✀猀 琀栀攀 瀀攀渀愀氀琀礀 漀昀 欀椀氀氀椀渀最 ㈀ 搀愀琀愀戀愀猀攀 瀀愀最攀猀⸀㰀戀爀㸀ഀഀ But since our records are so small, many records will fit in a database page. So, with many other
਍琀爀甀攀 瀀爀漀搀甀挀琀椀漀渀 琀愀戀氀攀猀Ⰰ 礀漀甀 洀椀最栀琀 ∀漀渀氀礀∀ 洀椀猀猀 樀甀猀琀 愀 昀攀眀 爀攀挀漀爀搀猀⸀㰀戀爀㸀ഀഀ I am not saying that it's perfect, but at least you have a sound table back, with the majority of records.
਍㰀戀爀㸀ഀഀ

Method 2: Selecting "around" corrupt pages, just using queries.

਍ഀഀ In the unlikely event, that "method 1" does not work for some reason, and you still have the corrupt table,
਍礀漀甀 洀椀最栀琀 挀漀渀猀椀搀攀爀 琀栀椀猀 洀攀琀栀漀搀㨀㰀戀爀㸀ഀഀ It's also possible to "select around" the corrupt pages. In effect, you just use SQL to select as much rows
਍愀猀 礀漀甀 挀愀渀Ⰰ 愀渀搀 猀欀椀瀀瀀椀渀最 琀栀攀 爀漀眀猀 漀渀 琀栀攀 搀愀洀愀最攀搀 瀀愀最攀猀⸀㰀戀爀㸀ഀഀ This "trick" usually is not easy. It certainly helps if the table uses a unique key, and that an index is defined
਍漀渀 琀栀愀琀 欀攀礀⸀㰀戀爀㸀ഀഀ
਍匀琀椀氀氀Ⰰ 甀猀椀渀最 渀漀 洀漀爀攀 琀栀愀渀 挀漀洀洀漀渀 猀攀渀猀攀Ⰰ 眀攀 挀愀渀 椀洀愀最椀渀最 琀栀愀琀 眀攀 挀愀渀 樀甀猀琀 猀攀氀攀挀琀 爀漀眀猀Ⰰ 愀渀搀 瀀甀琀 琀栀攀洀 椀渀 愀 挀漀瀀礀 琀愀戀氀攀㰀戀爀㸀ഀഀ as long as SQL Server does not "touches" the damaged pages.
਍㰀戀爀㸀ഀഀ Here is a trivial (wrong) example that intuitively might look allright (but most often don't work well)
਍椀渀 漀爀搀攀爀 琀漀 琀爀礀 琀漀 猀愀氀瘀愀最攀 ✀猀漀甀渀搀∀ 爀漀眀猀 昀爀漀洀 琀栀攀 挀漀爀爀甀瀀琀 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ
਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍㰀戀爀㸀ഀഀ begin try
਍搀攀挀氀愀爀攀 䀀椀 椀渀琀㰀戀爀㸀ഀഀ set @i=0
਍㰀戀爀㸀 ഀഀ while (@i<100000)
਍戀攀最椀渀㰀戀爀㸀ഀഀ insert into employee_copy
਍ 猀攀氀攀挀琀 ⨀ 昀爀漀洀 攀洀瀀氀漀礀攀攀 眀栀攀爀攀 攀洀瀀开椀搀㸀䀀椀 愀渀搀 攀洀瀀开椀搀㰀䀀椀⬀㄀　㰀戀爀㸀ഀഀ select @i=@i+10
਍攀渀搀㰀戀爀㸀ഀഀ end try
਍㰀戀爀㸀 ഀഀ begin catch
਍瀀爀椀渀琀 䀀椀㰀戀爀㸀ഀഀ end catch
਍㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ As you can see, we try to start at "the top" of the table, and work our way right trough it, copying rows
਍琀漀 愀 挀漀瀀礀 琀愀戀氀攀⸀ 唀渀琀椀氀 匀儀䰀 匀攀爀瘀攀爀 洀攀攀琀猀 琀栀攀 挀漀爀爀甀瀀琀 瀀愀最攀 漀昀挀漀甀爀猀攀⸀㰀戀爀㸀ഀഀ Then we could restart this game (setting @i at an appropriate value) to process the records "below" the
਍搀愀洀愀最攀搀 瀀愀最攀猀⸀㰀戀爀㸀ഀഀ Well, it could work. But the above code is way too simple and often does not work.
਍吀栀愀琀✀猀 眀栀礀 䌀栀愀瀀琀攀爀 㜀 眀椀氀氀 最漀 椀渀琀漀 猀漀洀攀 搀攀琀愀椀氀猀 漀昀 琀栀椀猀 洀攀琀栀漀搀⸀㰀戀爀㸀ഀഀ
਍㰀栀㌀㸀刀攀挀愀瀀㨀㰀⼀栀㌀㸀ഀഀ ਍䤀渀 䌀栀愀瀀琀攀爀猀 ㈀ 愀渀搀 ㌀Ⰰ 眀攀 栀愀瘀攀 猀攀攀渀 栀漀眀 眀攀 挀愀渀 栀愀渀搀氀攀 挀漀爀爀甀瀀琀椀漀渀 漀渀 漀爀搀椀渀愀爀礀 搀愀琀愀瀀愀最攀猀⸀㰀戀爀㸀ഀഀ
਍㰀漀氀㸀ഀഀ
  • We have seen what happens if a "soft" corruption in a tablepage have occurred, which we solved without dataloss.

    • ਍㰀氀椀㸀圀攀 栀愀瘀攀 猀攀攀渀 眀栀愀琀 栀愀瀀瀀攀渀猀 椀昀 愀 ∀栀愀爀搀∀ 挀漀爀爀甀瀀琀椀漀渀 椀渀 愀 昀攀眀 琀愀戀氀攀瀀愀最攀猀 栀愀瘀攀 漀挀挀甀爀爀攀搀Ⰰ 眀栀椀挀栀 眀攀 猀漀氀瘀攀搀 眀椀琀栀 猀漀洀攀 搀愀琀愀氀漀猀猀⸀㰀戀爀㸀ഀഀ In the latter case, a few table pages were truly destroyed, so, some dataloss was actually unavoidable.
    ਍㰀⼀漀氀㸀ഀഀ
    ਍㴀㸀 㰀唀㸀㰀䈀㸀䘀漀爀 昀椀渀搀椀渀最 瀀爀漀戀氀攀洀猀Ⰰ 眀攀 甀猀攀搀㨀 㰀⼀䈀㸀㰀⼀唀㸀㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀⠀琀愀戀氀攀开渀愀洀攀⤀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍吀栀椀猀 眀椀氀氀 渀漀琀 爀攀瀀愀椀爀 漀爀 愀氀琀攀爀 愀渀礀琀栀椀渀最Ⰰ 戀甀琀 琀栀攀 漀甀琀瀀甀琀 眀椀氀氀 猀栀漀眀 礀漀甀 ⠀椀渀 洀漀猀琀 挀愀猀攀猀⤀ 眀栀愀琀✀猀 眀爀漀渀最Ⰰ 愀渀搀 洀椀最栀琀 最椀瘀攀㰀戀爀㸀ഀഀ a hint on which minimum level of DBCC command should be used next.
    ਍㰀戀爀㸀ഀഀ In order to check the whole database in one run, you can use:
    ਍㰀戀爀㸀ഀഀ DBCC CHECKDB(database_name)
    ਍㰀戀爀㸀ഀഀ Here too, only problems will be reported, but not repaired. Since it checks the whole database, possibly multiple
    ਍漀戀樀攀挀琀猀 愀爀攀 爀攀瀀漀爀琀攀搀⸀㰀戀爀㸀ഀഀ Ofcourse, it's recommended to check your databases on a regular basis.
    ਍㰀戀爀㸀ഀഀ => For solving the softcorruption of (a) tablepage(s) we used:
    ਍㰀戀爀㸀ഀഀ (1): Put the "PAGE_VERIFY" database option to NONE, and see if you now can select the whole table.
    ਍䤀昀 礀漀甀 挀愀渀 爀攀琀爀攀椀瘀攀 愀氀氀 爀攀挀漀爀搀猀Ⰰ 洀甀氀琀椀瀀氀攀 漀瀀琀椀漀渀猀 攀砀椀猀琀猀 琀漀 爀攀戀甀椀氀搀 琀栀攀 搀愀琀愀 ⠀氀椀欀攀 䤀一匀䔀刀吀䤀一䜀 爀漀眀猀 椀渀琀漀 愀 挀漀瀀礀 琀愀戀氀攀⤀⸀㰀戀爀㸀ഀഀ But you likely have no unsurmountable problems, so you just might as well try to repair the table.
    ਍㰀戀爀㸀ഀഀ (2): In order to repair the table, we used:
    ਍㰀戀爀㸀ഀഀ DBCC CHECKTABLE(table_name,repair_rebuild)
    ਍㰀戀爀㸀ഀഀ This will not lead to dataloss, since it will only rebuild checksums, pointers, and likely indexentries too.
    ਍㰀戀爀㸀ഀഀ Here too, a similar "database wide" DBCC CHECKDB command exists as well. it's:
    ਍㰀戀爀㸀ഀഀ DBCC CHECKDB(database_name, repair_rebuild)
    ਍㰀戀爀㸀ഀഀ However, if you have multiple corrupt objects, I still would recommend to handle them one a the time.
    ਍㰀戀爀㸀ഀഀ => For solving the hard corruption of (a) tablepage(s) we used:
    ਍㰀戀爀㸀ഀഀ DBCC CHECKTABLE (table_name,repair_allow_data_loss)
    ਍㰀戀爀㸀ഀഀ It does not neccessarily lead to dataloss. In the example we have used, it was a brutal attack on datapages, fully
    ਍搀攀猀琀爀漀礀椀渀最 琀栀攀洀⸀ 伀戀瘀椀漀甀猀氀礀Ⰰ 猀漀洀攀 搀愀琀愀氀漀猀猀 愀昀琀攀爀 爀攀瀀愀椀爀 眀愀猀 甀渀愀瘀漀椀搀愀戀氀攀⸀㰀戀爀㸀ഀഀ In general, it's likely (but not always so) that the "bad" pages gets deallocated from the object.
    ਍㰀戀爀㸀ഀഀ Here too, a similar "database wide" DBCC CHECKDB command exists as well. it's obviously:
    ਍㰀戀爀㸀ഀഀ DBCC CHECKDB(database_name, repair_allow_data_loss)
    ਍㰀戀爀㸀ഀഀ However, if you have multiple corrupt objects, I still would recommend to handle them one a the time.
    ਍㰀戀爀㸀ഀഀ As another option for repair, is the use of "smart" queries (the monkey method) where we try to select "around"
    ਍琀栀攀 戀愀搀 瀀愀最攀猀⸀ 䠀漀眀攀瘀攀爀Ⰰ 挀攀爀琀愀椀渀 挀漀渀搀椀琀椀漀渀猀 猀栀漀甀氀搀 戀攀 椀渀 瀀氀愀挀攀Ⰰ 氀椀欀攀 愀 甀渀椀焀甀攀 欀攀礀⸀㰀戀爀㸀ഀഀ This will be the subject of Chapter 8.
    ਍㰀戀爀㸀ഀഀ Please note that all of the above handle ordinary datapages. If a "special" page in a database file
    ਍最攀琀 挀漀爀爀甀瀀琀攀搀 ⠀氀椀欀攀 瀀愀最攀 　㨀 琀栀攀 栀攀愀搀攀爀 瀀愀最攀Ⰰ 漀爀 瀀愀最攀 ㄀㨀 琀栀攀 倀䘀匀 瀀愀最攀Ⰰ 漀爀 瀀愀最攀 ㈀㨀 琀栀攀 䜀䄀䴀 瀀愀最攀Ⰰ 攀琀挀⸀⸀⤀㰀戀爀㸀ഀഀ we have a larger problem, and in many cases, DBCC commands will not be of help.
    ਍㰀戀爀㸀ഀഀ ਍一漀琀攀㨀 椀昀 礀漀甀 眀漀甀氀搀 栀愀瘀攀 搀攀琀攀爀洀椀渀攀搀 琀栀愀琀 琀栀攀 漀戀樀攀挀琀 椀猀 ∀樀甀猀琀∀ 愀渀 椀渀搀攀砀Ⰰ 礀漀甀 洀椀最栀琀 樀甀猀琀 䐀刀伀倀 愀渀搀 䌀刀䔀䄀吀䔀 琀栀攀 椀渀搀攀砀 愀最愀椀渀⸀㰀戀爀㸀ഀഀ An index is just "derived" data, and can be rebuild without loss of tabledata. You only have to keep in mind
    ਍琀栀愀琀 琀栀攀 椀渀搀攀砀 洀椀最栀琀 猀甀瀀瀀漀爀琀 愀 ∀挀漀渀猀琀爀愀椀渀琀∀⸀ 䤀昀 琀栀愀琀✀猀 琀爀甀攀Ⰰ 琀栀攀爀攀 愀爀攀 猀漀洀攀 昀漀氀氀漀眀ⴀ甀瀀 愀挀琀椀漀渀猀⸀㰀戀爀㸀ഀഀ     ਍㰀戀爀㸀ഀഀ
    ਍㰀栀㈀㸀䌀栀愀瀀琀攀爀 㐀㨀 䤀渀愀挀挀攀猀椀戀氀攀 漀爀 匀甀猀瀀攀挀琀 搀愀琀愀戀愀猀攀Ⰰ 搀甀攀 琀漀 愀 䴀椀猀猀椀渀最 漀爀 挀漀爀爀甀瀀琀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最⸀ 䴀猀最 㤀㐀㔀⸀㰀⼀栀㈀㸀㰀戀爀㸀ഀഀ ਍䤀渀 琀栀椀猀 挀栀愀瀀琀攀爀Ⰰ 眀攀 眀椀氀氀 攀砀瀀氀漀爀攀 眀栀愀琀 眀攀 挀愀渀 搀漀 眀栀攀渀 琀栀攀 吀爀愀渀猀愀挀琀椀漀渀 氀漀最 椀猀 洀椀猀猀椀渀最Ⰰ 漀爀 椀猀 挀漀爀爀甀瀀琀⸀㰀戀爀㸀ഀഀ
    ਍ⴀ䤀昀 琀栀攀 吀爀愀渀猀愀挀琀椀漀渀 䰀漀最 椀猀 洀椀猀猀椀渀最Ⰰ 礀漀甀爀 搀愀琀愀戀愀猀攀 眀椀氀氀 戀攀 ∀椀渀愀挀挀攀猀椀戀氀攀∀⸀ 夀漀甀 眀椀氀氀 最攀琀 愀渀 攀爀爀漀爀洀攀猀猀愀最攀 琀栀愀琀 眀椀氀氀㰀戀爀㸀ഀഀ inform you to that fact.
    ਍ⴀ䤀昀 琀栀攀 吀爀愀渀猀愀挀琀椀漀渀 䰀漀最 椀猀 爀攀愀氀氀礀 挀漀爀爀甀瀀琀Ⰰ 琀栀攀 搀愀琀愀戀愀猀攀 洀椀最栀琀 戀攀 椀渀愀挀挀攀猀椀戀氀攀Ⰰ 漀爀 椀琀 眀椀氀氀 戀攀 瀀甀琀 椀渀 ∀匀甀猀瀀攀挀琀∀ 洀漀搀攀⸀㰀戀爀㸀ഀഀ -If you have corrupt objects like tables and indexes, and SQL Server needs to rollback or rollforward data
    ਍爀攀氀愀琀攀搀 琀漀 琀栀漀猀攀 琀愀戀氀攀猀Ⰰ 琀栀攀 搀愀琀愀戀愀猀攀 眀椀氀氀 戀攀 瀀氀愀挀攀搀 椀渀 匀甀猀瀀攀挀琀 洀漀搀攀⸀ 吀栀愀琀猀 眀栀礀 愀昀琀攀爀 愀 挀爀愀猀栀Ⰰ 愀 搀愀琀愀戀愀猀攀 洀椀最栀琀㰀戀爀㸀ഀഀ show up as Suspect. In chapter 3 we have seen, that even if some ordinary datapages (table or index) are hard corrupted, but they
    ਍搀漀 渀漀琀 渀攀攀搀 琀漀 戀攀 愀挀挀攀猀猀攀搀 搀甀爀椀渀最 琀栀攀 猀琀愀爀琀 ⠀愀琀 琀栀攀 爀攀挀漀瘀攀爀礀 瀀栀愀猀攀⤀Ⰰ 匀儀䰀 匀攀爀瘀攀爀 眀椀氀氀 樀甀猀琀 猀琀愀爀琀 渀漀爀洀愀氀氀礀⸀㰀戀爀㸀ഀഀ
    ਍䄀猀 猀愀椀搀 戀攀昀漀爀攀Ⰰ 椀渀 琀栀椀猀 挀栀愀瀀琀攀爀 眀攀 眀椀氀氀 猀攀攀 眀栀愀琀 眀攀 挀愀渀 搀漀 椀昀 琀栀攀 吀爀愀渀猀愀挀琀椀漀渀 氀漀最 椀猀 挀漀爀爀甀瀀琀攀搀 漀爀 洀椀猀猀椀渀最⸀㰀戀爀㸀ഀഀ Ofcourse, such a small note as this one, cannot list all possible solutions. But three realistic solutions
    ਍愀爀攀 瀀爀攀猀攀渀琀攀搀 栀攀爀攀Ⰰ 眀栀椀挀栀 愀爀攀㨀㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀⠀㄀⤀ 䄀䰀吀䔀刀 䐀䄀吀䄀䈀䄀匀䔀 䐀䄀吀䄀䈀䄀匀䔀开一䄀䴀䔀 匀䔀吀 䔀䴀䔀刀䜀䔀一䌀夀㰀⼀䈀㸀 㰀䤀㸀渀漀琀 爀攀挀漀洀洀攀渀搀攀搀⸀㰀⼀䤀㸀㰀戀爀㸀ഀഀ This allows you to SELECT all tables, and thus you can salvage all data. But this method is not recommended,
    ਍猀椀渀挀攀 戀攀氀漀眀 琀眀漀 洀攀琀栀漀搀猀 愀爀攀 氀椀猀琀攀搀 琀栀愀琀 洀椀最栀琀 爀攀瀀愀椀爀 礀漀甀爀 搀愀琀愀戀愀猀攀⸀㰀戀爀㸀ഀഀ However, the method is often used to get out of the Suspect state, and to go to Emergency state.
    ਍㰀戀爀㸀ഀഀ (2) ALTER DATABASE DATABASE_NAME REBUILD LOG ON (NAME=logical_name,FILENAME=physical_name)
    ਍吀栀椀猀 洀攀琀栀漀搀 椀猀 爀攀挀漀洀洀攀渀搀攀搀⸀ 䤀渀 洀愀渀礀 挀愀猀攀猀Ⰰ 椀昀 琀栀攀 琀爀愀渀猀愀挀椀漀渀氀漀最 昀椀氀攀猀 眀攀爀攀 最漀渀攀Ⰰ 椀琀 眀椀氀氀 爀攀戀甀椀氀搀㰀戀爀㸀ഀഀ a new transaction logfile. If it succeeds, you have an operational database.
    ਍㰀戀爀㸀ഀഀ (3) CREATE DATABASE DATABASE_NAME ON (FILENAME = 'path to mdf file') FOR ATTACH_FORCE_REBUILD_LOG
    ਍䤀昀 洀攀琀栀漀搀 ⠀㈀⤀ 栀愀猀 昀愀椀氀攀搀Ⰰ 瀀漀猀猀椀戀氀礀 搀甀攀 琀漀 琀栀攀 昀愀挀琀 琀栀愀琀 匀儀䰀 匀攀爀瘀攀爀 琀栀椀渀欀猀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 ∀爀攀挀漀瘀攀爀礀开瀀攀渀搀椀渀最∀㰀戀爀㸀ഀഀ or due to other reasons, we can try to "detach" and "attach" the database again, with a ATTACH_FORCE_REBUILD_LOG clause.
    ਍吀栀攀爀攀 椀猀 愀 眀愀爀渀椀渀最 琀栀漀甀最栀℀ 倀爀攀昀攀爀爀愀戀氀礀Ⰰ 挀爀攀愀琀攀 挀漀瀀椀攀猀 漀昀 礀漀甀爀 搀愀琀愀戀愀猀攀昀椀氀攀猀 琀漀 愀渀漀琀栀攀爀 搀椀猀欀 漀爀 昀漀氀搀攀爀⠀猀⤀⸀㰀戀爀㸀ഀഀ
    ਍ഀഀ

    4.1 Simulating a Missing (or corrupt) Transaction log file.

    ਍ഀഀ Please create the SALES database again, using the full script as listed in chapter 1.
    ਍㰀戀爀㸀ഀഀ Again, we have a sound database, with a couple of tables which we can access in the usual way.
    ਍一漀眀Ⰰ 琀漀 猀椀洀甀氀愀琀攀 愀 猀攀爀椀漀甀猀 攀爀爀漀爀 挀漀渀搀椀琀椀漀渀Ⰰ 椀渀 眀栀椀挀栀 琀栀攀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最 昀椀氀攀 椀猀 洀椀猀猀椀渀最Ⰰ 搀漀 琀栀攀 昀漀氀氀漀眀椀渀最㨀㰀戀爀㸀ഀഀ
    ਍ⴀ 匀栀甀琀 搀漀眀渀 琀栀攀 匀儀䰀 匀攀爀瘀攀爀 猀攀爀瘀椀挀攀⸀㰀戀爀㸀ഀഀ - Navigate the filesystem to the "c:\mssql\data" folder.
    ਍ⴀ 䐀攀氀攀琀攀 琀栀攀 ∀匀䄀䰀䔀匀开䰀伀䜀开　　㄀⸀氀搀昀∀ 琀爀愀渀猀愀挀琀椀漀渀 氀漀最 昀椀氀攀⸀㰀戀爀㸀ഀഀ - Start the SQL Server service again.
    ਍㰀戀爀㸀ഀഀ next, open SQL Server Management Studio (SSMS), and try to access the SALES database.
    ਍䰀椀欀攀氀礀Ⰰ 匀匀䴀匀 眀椀氀氀 琀栀爀漀眀 琀栀攀 攀爀爀漀爀 洀攀猀猀愀最攀 ∀匀䄀䰀䔀匀 椀猀 渀漀琀 愀挀挀攀猀猀椀戀氀攀⸀⸀∀ 琀漀 礀漀甀⸀㰀戀爀㸀ഀഀ In SSMS, you also see that next to the SALES database icon, there is no plus sign (+) to examine objects in that database.
    ਍䤀昀 眀攀 琀愀欀攀 愀 氀漀漀欀 愀琀 琀栀攀 匀儀䰀 匀攀爀瘀攀爀 氀漀最Ⰰ 眀攀 昀椀渀搀 攀渀琀爀椀攀猀 氀椀欀攀 琀栀攀 昀漀氀氀漀眀椀渀最㨀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䘀椀氀攀 愀挀琀椀瘀愀琀椀漀渀 昀愀椀氀甀爀攀⸀ 吀栀攀 瀀栀礀猀椀挀愀氀 昀椀氀攀 渀愀洀攀 ∀挀㨀尀洀猀猀焀氀尀搀愀琀愀尀匀䄀䰀䔀匀开䰀伀䜀开　　㄀⸀氀搀昀∀ 洀愀礀 戀攀 椀渀挀漀爀爀攀挀琀⸀㰀戀爀㸀ഀഀ The log cannot be rebuilt because the database was not cleanly shut down.
    ਍䴀猀最 㤀㐀㔀Ⰰ 䰀攀瘀攀氀 ㄀㐀Ⰰ 匀琀愀琀攀 ㈀Ⰰ 䰀椀渀攀 ㄀㰀戀爀㸀ഀഀ Database 'SALES' cannot be opened due to inaccessible files or insufficient memory or disk space.
    ਍䴀猀最 㔀　㘀㤀Ⰰ 䰀攀瘀攀氀 ㄀㘀Ⰰ 匀琀愀琀攀 ㄀Ⰰ 䰀椀渀攀 ㄀㰀戀爀㸀ഀഀ ALTER DATABASE statement failed.
    ਍㰀戀爀㸀ഀഀ and maybe also: ਍㰀戀爀㸀ഀഀ FileMgr::StartLogFiles: Operating system error 2(error not found) occurred while creating or opening file
    ਍✀挀㨀尀洀猀猀焀氀尀搀愀琀愀尀匀䄀䰀䔀匀开䰀伀䜀开　　㄀⸀氀搀昀✀⸀ 㸀戀爀㸀ഀഀ Diagnose and correct the operating system error, and retry the operation.
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ We are not able to open and access the SALES database. There are several strategies that we can follow.
    ਍吀栀攀 渀攀砀琀 猀攀挀琀椀漀渀猀 眀椀氀氀 搀椀猀挀甀猀猀 瘀愀爀椀漀甀猀 洀攀琀栀漀搀猀⸀㰀戀爀㸀ഀഀ
    ਍ഀഀ

    4.2 Solution 1: Perform the neccessary Trivial checks

    ਍ഀഀ Here, ofcourse we know that the logfile is missing, but in general you should quicly check the following trivial items:
    ਍㰀戀爀㸀ഀഀ - did somebody accidently moved database files (on your Server) to another location?
    ਍ⴀ 椀猀 琀栀攀 昀椀氀攀 瀀攀爀栀愀瀀猀 椀渀 琀栀攀 刀攀挀礀挀氀攀 戀椀渀㼀㰀戀爀㸀ഀഀ - do you have an NTFS Undelete utility (there are even a couple of free tools available)
    ਍ⴀ 愀爀攀 瀀攀爀栀愀瀀猀 琀栀攀 昀椀氀攀猀礀猀琀攀洀 瀀攀爀洀椀猀猀椀漀渀猀 挀栀愀渀最攀搀Ⰰ 猀漀 琀栀愀琀 琀栀攀 猀攀爀瘀椀挀攀 挀愀渀渀漀琀 愀挀挀攀猀猀 琀栀攀 昀椀氀攀㼀㰀戀爀㸀ഀഀ
    ਍圀攀 愀爀攀 渀漀琀 最漀椀渀最 琀漀 甀渀搀攀氀攀琀攀 琀栀攀 昀椀氀攀Ⰰ 愀渀搀 眀攀 昀甀爀琀栀攀爀 琀爀攀愀琀 琀栀椀猀 挀愀猀攀 愀猀 椀昀 琀栀攀 氀漀最昀椀氀攀 椀猀 瀀攀爀洀愀渀攀渀琀氀礀 洀椀猀猀椀渀最⸀㰀戀爀㸀ഀഀ
    ਍㰀栀㌀㸀㐀⸀㌀ 匀漀氀甀琀椀漀渀 ㈀㨀 瀀氀愀挀攀 琀栀攀 䐀愀琀愀戀愀猀攀 椀渀 䔀洀攀爀最攀渀挀礀 䴀漀搀攀 ⠀渀漀琀 爀攀挀漀洀洀攀渀搀攀搀⤀㰀⼀栀㌀㸀ഀഀ
    ਍䤀昀 椀琀✀猀 挀氀攀愀爀 昀爀漀洀 琀栀攀 攀爀漀爀 洀攀猀猀愀最攀猀Ⰰ 琀栀愀琀 㰀䤀㸀椀琀 椀猀 琀栀攀 吀爀愀渀猀愀挀琀椀漀渀 氀漀最Ⰰ 琀栀愀琀 椀猀 洀椀猀猀椀渀最 漀爀 挀漀爀爀甀瀀琀㰀⼀䤀㸀Ⰰ㰀戀爀㸀 ഀഀ then this method is not recommended.
    ਍䈀甀琀 椀渀 漀琀栀攀爀 挀椀爀挀甀洀猀琀愀渀挀攀猀Ⰰ 昀漀爀 攀砀愀洀瀀氀攀Ⰰ 眀栀攀渀 礀漀甀 昀椀爀猀琀 猀攀攀 愀 ∀匀甀猀瀀攀挀琀∀ 搀愀琀愀戀愀猀攀Ⰰ 琀栀攀渀 椀琀 挀漀甀氀搀 戀攀㰀戀爀㸀ഀഀ a good action.
    ਍㰀戀爀㸀ഀഀ But, suppose we get spooked by now, because we know we have a serious problem. Although better methods exists to get "life"
    ਍愀最愀椀渀Ⰰ 眀攀 洀椀最栀琀 搀攀挀椀搀攀 琀漀 爀攀琀爀椀攀瘀攀 琀栀攀 椀渀昀漀爀洀愀琀椀漀渀 昀爀漀洀 琀栀攀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀Ⰰ 愀猀 猀漀漀渀 愀猀 瀀漀猀猀椀戀氀攀⸀㰀戀爀㸀ഀഀ
    ਍䤀渀 洀愀渀礀 挀愀猀攀猀Ⰰ 椀昀 礀漀甀 挀愀渀✀琀 愀挀挀攀猀猀 愀 搀愀琀愀戀愀猀攀 ⠀昀漀爀 攀砀愀洀瀀氀攀Ⰰ 搀甀攀 琀漀 洀椀猀猀椀渀最 琀爀愀渀猀愀挀琀椀漀渀 氀漀最 昀椀氀攀猀Ⰰ 漀爀 搀甀攀 琀漀 挀漀爀爀甀瀀琀 漀戀樀攀挀琀猀⤀Ⰰ ഀഀ you can place a database in "Emergency Mode".
    ਍䤀昀 琀栀攀 搀愀琀愀戀愀猀攀 眀愀猀 ∀挀氀攀愀渀氀礀∀ 猀栀甀琀搀漀眀渀 戀攀昀漀爀攀 琀栀攀 搀攀氀攀琀椀漀渀 漀昀 琀栀攀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最昀椀氀攀Ⰰ 椀琀 瀀爀漀戀愀戀氀礀 眀椀氀氀 眀漀爀欀⸀㰀戀爀㸀ഀഀ
    ਍䤀昀 椀琀 眀漀爀欀猀Ⰰ 愀 搀愀琀愀戀愀猀攀 椀猀 琀栀攀渀 攀昀昀攀挀琀椀瘀攀氀礀 琀漀 戀攀 挀漀渀猀椀搀攀爀攀搀 愀猀 ∀爀攀愀搀ⴀ漀渀氀礀∀Ⰰ 戀甀琀 眀攀 愀爀攀 愀戀氀攀 琀漀 匀䔀䰀䔀䌀吀 愀氀氀 琀愀戀氀攀猀Ⰰ㰀戀爀㸀ഀഀ and copy the data to another database. Also, export tools as "bcp" will work.
    ਍吀栀攀爀攀 椀猀 渀漀 最愀爀愀渀琀攀攀 琀栀愀琀 椀琀 眀椀氀氀 㰀䤀㸀愀氀眀愀礀猀㰀⼀䤀㸀 眀漀爀欀Ⰰ 戀甀琀 椀渀 洀愀渀礀 挀愀猀攀猀Ⰰ 椀琀 爀攀愀氀氀礀 搀漀攀猀⸀㰀戀爀㸀ഀഀ
    ਍䰀攀琀✀猀 琀爀礀 椀琀⸀ 匀琀愀爀琀 愀 儀甀攀爀礀 圀椀渀搀漀眀Ⰰ 愀渀搀 攀砀攀挀甀琀攀 琀栀攀 昀漀氀氀漀眀椀渀最 吀匀儀䰀 猀琀愀琀攀洀攀渀琀㨀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䄀䰀吀䔀刀 䐀䄀吀䄀䈀䄀匀䔀 匀䄀䰀䔀匀 匀䔀吀 䔀䴀䔀刀䜀䔀一䌀夀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ If it worked, then if you refresh the databases icon in SSMS, you will now see the "+" appear before the SALES database,
    ਍愀渀搀 礀漀甀 挀愀渀 瘀椀攀眀 琀栀攀 漀戀樀攀挀琀猀 愀猀 甀猀甀愀氀⸀ 䔀昀昀攀挀琀椀瘀攀氀礀Ⰰ 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 爀攀愀搀ⴀ漀渀氀礀Ⰰ 戀甀琀 礀漀甀 愀爀攀 愀戀氀攀 琀漀 爀攀琀爀椀攀瘀攀 琀栀攀 搀愀琀愀⸀㰀戀爀㸀ഀഀ Just try to select the rows from the EMPLOYEE table, to check if it works.
    ਍㰀戀爀㸀ഀഀ At least this solution is a method to salvage all your data, but it is not recommended.
    ਍㰀戀爀㸀ഀഀ It can also be interresting to take a look at a couple of systemviews, like for example:
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ select substring(name,1,30), database_id, state, state_desc from sys.databases
    ਍猀攀氀攀挀琀 ⨀ 昀爀漀洀 猀礀猀⸀搀洀开琀爀愀渀开搀愀琀愀戀愀猀攀开琀爀愀渀猀愀挀琀椀漀渀猀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
    ਍㰀栀㌀㸀㐀⸀㐀 匀漀氀甀琀椀漀渀 ㌀㨀 刀攀戀甀椀氀搀 琀栀攀 吀爀愀渀猀愀挀琀椀漀渀 䰀漀最 ⠀爀攀挀漀洀洀攀渀搀攀搀⤀㰀⼀栀㌀㸀ഀഀ ਍匀攀瘀攀爀愀氀 愀瀀瀀爀漀愀挀栀攀猀 攀砀椀猀琀猀 琀漀 氀攀琀 匀儀䰀 匀攀爀瘀攀爀 挀爀攀愀琀攀 愀 渀攀眀 吀爀愀渀猀愀挀琀椀漀渀 氀漀最昀椀氀攀⸀㰀戀爀㸀ഀഀ
    ਍伀渀挀攀Ⰰ 眀攀 栀愀搀 琀栀攀 䐀䈀䌀䌀 刀䔀䈀唀䤀䰀䐀开䰀伀䜀⠀⤀ 猀琀愀琀攀洀攀渀琀 琀漀 漀甀爀 搀椀猀瀀漀猀愀氀Ⰰ 戀甀琀 椀琀✀猀 渀漀琀 愀瘀愀椀氀愀戀氀攀 漀瘀攀爀 愀氀氀 瘀攀爀猀椀漀渀猀Ⰰ㰀戀爀㸀 ഀഀ so we don't consider that one.
    ਍㰀戀爀㸀ഀഀ What we will try instead is the following command:
    ਍㰀戀爀㸀ഀഀ ALTER DATABASE SALES REBUILD LOG ON (NAME=logical_name,FILENAME=physical_name)
    ਍㰀戀爀㸀ഀഀ So, let's try it:
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ ALTER DATABASE SALES REBUILD LOG ON (NAME=Sales_Log,FILENAME='c:\mssql\data\sales_log.ldf')
    ਍㰀戀爀㸀ഀഀ The following output hopefully appears:
    ਍㰀戀爀㸀ഀഀ Warning: The log for database 'SALES' has been rebuilt. Transactional consistency has been lost.
    ਍吀栀攀 刀䔀匀吀伀刀䔀 挀栀愀椀渀 眀愀猀 戀爀漀欀攀渀Ⰰ 愀渀搀 琀栀攀 猀攀爀瘀攀爀 渀漀 氀漀渀最攀爀 栀愀猀 挀漀渀琀攀砀琀 漀渀 琀栀攀 瀀爀攀瘀椀漀甀猀 氀漀最 昀椀氀攀猀Ⰰ 猀漀 礀漀甀 眀椀氀氀 渀攀攀搀 琀漀 欀渀漀眀 眀栀愀琀 琀栀攀礀 眀攀爀攀⸀㰀戀爀㸀ഀഀ You should run DBCC CHECKDB to validate physical consistency. The database has been put in dbo-only mode.
    ਍圀栀攀渀 礀漀甀 愀爀攀 爀攀愀搀礀 琀漀 洀愀欀攀 琀栀攀 搀愀琀愀戀愀猀攀 愀瘀愀椀氀愀戀氀攀 昀漀爀 甀猀攀Ⰰ 礀漀甀 眀椀氀氀 渀攀攀搀 琀漀 爀攀猀攀琀 搀愀琀愀戀愀猀攀 漀瀀琀椀漀渀猀 愀渀搀 搀攀氀攀琀攀 愀渀礀 攀砀琀爀愀 氀漀最 昀椀氀攀猀⸀ ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ
    ਍䤀昀 琀栀攀 甀瀀瀀攀爀 漀甀琀瀀甀琀 愀瀀瀀攀愀爀攀搀Ⰰ 眀攀 栀愀瘀攀 猀甀挀挀攀猀℀ 䤀昀 渀漀眀 礀漀甀 爀攀昀爀攀猀栀 匀匀䴀匀Ⰰ 琀栀攀 䔀洀攀爀最攀渀挀礀 洀漀搀攀 椀猀 爀攀瀀氀愀挀攀搀 戀礀 ∀刀攀猀琀爀椀挀琀攀搀 唀猀攀爀∀ 洀漀搀攀⸀㰀戀爀㸀ഀഀ Now, change the database properties so that the Database is multi-user again.
    ਍䄀氀猀漀Ⰰ 眀攀 栀愀瘀攀 愀 爀攀戀甀椀氀搀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最Ⰰ 愀渀搀 礀漀甀 挀愀渀 愀搀搀 昀椀氀攀猀Ⰰ 琀漀 眀栀愀琀 礀漀甀 猀攀攀 椀猀 愀瀀瀀爀漀瀀爀椀愀琀攀 昀漀爀 琀栀攀 䰀漀最⸀㰀戀爀㸀ഀഀ
    ਍㰀栀㌀㸀㐀⸀㔀 匀漀氀甀琀椀漀渀 㐀㨀 刀攀戀甀椀氀搀 氀漀最 眀椀琀栀 䌀刀䔀䄀吀䔀 䐀䄀吀䄀䈀䄀匀䔀⸀⸀ 䄀吀吀䄀䌀䠀开䘀伀刀䌀䔀开刀䔀䈀唀䤀䰀䐀开䰀伀䜀⤀⸀㰀⼀栀㌀㸀ഀഀ ਍䤀昀 琀栀攀 猀漀氀甀琀椀漀渀 漀昀 猀攀挀琀椀漀渀 㐀⸀㐀 栀愀猀 昀愀椀氀攀搀Ⰰ 眀攀 愀爀攀 猀琀椀氀氀 渀漀琀 漀甀琀 漀昀 戀甀猀椀渀攀猀猀 礀攀琀⸀㰀戀爀㸀ഀഀ Indeed, in some cases, SQL Server might "think" that a database is in a "RECOVERY_PENDING" state.
    ਍吀栀攀渀Ⰰ 椀昀 愀 䰀漀最 椀猀 氀漀猀琀Ⰰ 椀琀 洀椀最栀琀 樀甀猀琀 瀀爀漀琀攀猀琀 椀渀 爀攀戀甀椀氀搀椀渀最 琀栀攀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最昀椀氀攀Ⰰ 戀甀琀 琀栀攀爀攀 愀爀攀 漀琀栀攀爀 挀愀甀猀攀猀 愀猀 眀攀氀氀⸀㰀戀爀㸀ഀഀ In this simple note, we emulate problems with the transaction log, by just deleting it (while the database is offline).
    ਍䈀甀琀 琀栀攀爀攀 挀漀甀氀搀 戀攀 爀攀愀氀氀礀 挀漀洀瀀氀攀砀 瀀爀漀戀氀攀洀猀 搀甀攀 琀漀 ⠀眀栀愀琀 匀儀䰀 匀攀爀瘀攀爀 琀栀椀渀欀猀⤀ 愀爀攀 甀渀昀椀渀椀猀栀攀搀 琀爀愀渀猀愀挀琀椀漀渀猀 琀栀愀琀 洀甀猀琀 爀漀氀氀戀愀挀欀⸀㰀戀爀㸀ഀഀ
    ਍䈀攀氀漀眀Ⰰ 椀猀 愀挀琀甀愀氀氀礀 愀 戀椀琀 漀昀 愀 琀爀椀挀欀Ⰰ 猀漀 椀昀 礀漀甀 愀爀攀 眀漀爀欀椀渀最 爀椀最栀琀 渀漀眀 漀渀 琀栀攀 匀䄀䰀䔀匀 搀攀洀漀 搀愀琀愀戀愀猀攀Ⰰ 礀漀甀 搀漀 渀漀琀 栀愀瘀攀 琀漀 琀愀欀攀㰀戀爀㸀ഀഀ any precautions.
    ਍㰀戀爀㸀ഀഀ But if you want to apply the following method to a database that is of any value, I would like you to create copies of the
    ਍搀愀琀愀戀愀猀攀 昀椀氀攀猀 ⠀攀⸀最⸀ 琀漀 愀渀漀琀栀攀爀 搀椀猀欀Ⰰ 漀爀 昀漀氀搀攀爀⤀⸀ 匀漀Ⰰ 礀漀甀 洀椀最栀琀 挀漀渀猀椀搀攀爀 猀栀甀琀琀椀渀最 搀漀眀渀 琀栀攀 匀儀䰀 匀攀爀瘀攀爀 猀攀爀瘀椀挀攀Ⰰ 愀渀搀 洀愀欀攀 挀漀瀀椀攀猀⸀㰀戀爀㸀ഀഀ
    ਍䄀渀礀眀愀礀Ⰰ 眀攀 最漀 愀栀攀愀搀 眀椀琀栀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀⸀㰀戀爀㸀ഀഀ Please drop it, and create it again using the full script in Chapter 1.
    ਍㰀戀爀㸀ഀഀ Again, we have a sound database, with a couple of tables which we can access in the usual way.
    ਍一漀眀Ⰰ 樀甀猀琀 愀猀 椀渀 猀攀挀琀椀漀渀 㐀⸀㄀Ⰰ 搀漀 琀栀攀 昀漀氀氀漀眀椀渀最㨀㰀戀爀㸀ഀഀ
    ਍ⴀ 匀栀甀琀 搀漀眀渀 琀栀攀 匀儀䰀 匀攀爀瘀攀爀 猀攀爀瘀椀挀攀⸀㰀戀爀㸀ഀഀ - Navigate the filesystem to the "c:\mssql\data" folder.
    ਍ⴀ 䐀攀氀攀琀攀 琀栀攀 ∀匀䄀䰀䔀匀开䰀伀䜀开　　㄀⸀氀搀昀∀ 琀爀愀渀猀愀挀琀椀漀渀 氀漀最 昀椀氀攀⸀㰀戀爀㸀ഀഀ - Start the SQL Server service again.
    ਍㰀戀爀㸀ഀഀ If you try to access the database, you get the familiar errormessages, just as described in section 4.1.
    ਍㰀戀爀㸀ഀഀ This time we are going to "detach" the database, so that SQL Server places it in a specific state, just as if
    ਍椀琀 搀漀攀猀 渀漀琀 欀渀漀眀 琀栀攀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀 愀渀礀洀漀爀攀⸀㰀戀爀㸀ഀഀ But again, it's advisable to create copies of the databasefiles first (if possible), while SQL Server is down
    ਍㰀戀爀㸀ഀഀ Let's try the following:
    ਍㰀戀爀㸀ഀഀ ਍猀瀀开搀攀琀愀挀栀开搀戀 猀愀氀攀猀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ Now, you may receive output that the command completed, but it's more likely you get output similar to this:
    ਍㰀戀爀㸀ഀഀ ਍䴀猀最 㤀㐀㜀Ⰰ 䰀攀瘀攀氀 ㄀㘀Ⰰ 匀琀愀琀攀 ㄀Ⰰ 䰀椀渀攀 ㄀㰀戀爀㸀ഀഀ Error while closing database 'SALES'. Check for previous additional errors and retry the operation.
    ਍ഀഀ
    ਍ഀഀ ਍一漀眀Ⰰ 搀漀渀✀琀 眀漀爀爀礀 愀戀漀甀琀 琀栀攀 洀攀猀猀愀最攀 愀戀漀瘀攀⸀ 吀栀攀 搀愀琀愀戀愀猀攀 椀猀 搀攀ⴀ爀攀最椀猀琀攀爀攀搀 昀爀漀洀 匀儀䰀 匀攀爀瘀攀爀㰀戀爀㸀ഀഀ Also, we still have the original database files (.mdf and .ndf files), and next we tell SQL Server to attach
    ਍琀栀攀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀Ⰰ 樀甀猀琀 愀猀 椀昀 礀漀甀 眀攀爀攀 洀漀瘀椀渀最 椀琀 昀爀漀洀 漀渀攀 匀攀爀瘀攀爀 琀漀 愀渀漀琀栀攀爀⸀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䌀刀䔀䄀吀䔀 䐀䄀吀䄀䈀䄀匀䔀 匀䄀䰀䔀匀㰀戀爀㸀ഀഀ ON (FILENAME = 'c:\mssql\data\sales.mdf')
    ਍䘀伀刀 䄀吀吀䄀䌀䠀开䘀伀刀䌀䔀开刀䔀䈀唀䤀䰀䐀开䰀伀䜀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍夀漀甀 瀀爀漀戀愀戀氀礀 最攀琀 琀栀攀 昀漀氀氀漀眀椀渀最 漀甀琀瀀甀琀Ⰰ 眀栀椀挀栀 愀挀琀甀愀氀氀礀 洀攀愀渀猀 猀甀挀挀攀猀Ⰰ 椀昀 礀漀甀 椀渀猀瀀攀挀琀 琀栀攀 猀攀挀漀渀搀 氀椀渀攀 挀氀漀猀攀氀礀㨀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䘀椀氀攀 愀挀琀椀瘀愀琀椀漀渀 昀愀椀氀甀爀攀⸀ 吀栀攀 瀀栀礀猀椀挀愀氀 昀椀氀攀 渀愀洀攀 ∀挀㨀尀洀猀猀焀氀尀搀愀琀愀尀匀䄀䰀䔀匀开䰀伀䜀开　　㄀⸀氀搀昀∀ 洀愀礀 戀攀 椀渀挀漀爀爀攀挀琀⸀㰀戀爀㸀ഀഀ New log file 'c:\mssql\data\SALES_log.LDF' was created.
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ Next, try if you can for example select the EMPLOYEE table again.
    ਍䠀漀瀀攀昀甀氀氀礀 椀琀 眀漀爀欀猀Ⰰ 愀渀搀 眀攀 挀漀渀挀氀甀搀攀 琀栀愀琀 眀攀 栀愀瘀攀 爀攀瀀愀椀爀攀搀 琀栀攀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀⸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ
    ਍ഀഀ

    Chapter 5: Inaccesible or Suspect database, due to Severe corruption.

    ਍ഀഀ

    5.1 A few remarks on databases pages: the ordinary datapages and the special pages

    ਍ഀഀ Chapters 2 and 3 dealt with corruption on (ordinary) data pages. These pages will generally hold table- and index data.
    ਍䤀昀 漀戀樀攀挀琀猀 漀渀 漀爀搀椀渀愀爀礀 搀愀琀愀 漀爀 椀渀搀攀砀 瀀愀最攀猀 眀攀渀琀 挀漀爀爀甀瀀琀Ⰰ 琀栀攀渀 匀儀䰀 匀攀爀瘀攀爀 搀椀搀 渀漀琀 愀氀琀攀爀 琀栀攀 ∀猀琀愀琀攀 漀昀 琀栀攀 搀愀琀愀戀愀猀攀∀Ⰰ㰀戀爀㸀ഀഀ unless there is a reason to put it into the 'Suspect" state (like that at startup, rollback is needed, but the object is corrupt).
    ਍㰀戀爀㸀ഀഀ You know that a database will have one .mdf (primary) file, and usually one or more .ndf (secondary) files,
    ਍眀栀椀挀栀 眀椀氀氀 栀漀氀搀 ⠀漀爀 愀爀攀 猀甀瀀瀀漀猀攀搀 琀漀 栀漀氀搀⤀Ⰰ 琀栀攀 愀挀琀甀愀氀 搀愀琀愀 ⠀琀愀戀氀攀猀Ⰰ 椀渀搀攀砀攀猀 攀琀挀⸀⸀⤀⸀ ഀഀ
    ਍吀栀攀 㰀䈀㸀昀椀爀猀琀 挀漀甀瀀氀攀 漀昀 瀀愀最攀猀㰀⼀䈀㸀 椀渀 琀栀攀 ⸀洀搀昀 漀爀 愀渀礀 ⸀渀搀昀 昀椀氀攀Ⰰ 愀爀攀 ∀猀瀀攀挀椀愀氀∀Ⰰ 愀渀搀 眀椀氀氀 挀漀渀琀愀椀渀 ⠀愀洀漀渀最 漀琀栀攀爀 琀栀椀渀最猀⤀ 㰀戀爀㸀ഀഀ identification data, and metadata, describing the database, and the (starting)location of all objects in the file.
    ਍吀栀攀猀攀 瀀愀最攀猀 昀漀爀洀 琀栀攀 ✀愀搀洀椀渀椀猀琀爀愀琀椀漀渀∀ 漀昀 琀栀攀 昀椀氀攀猀 愀渀搀 琀栀攀 搀愀琀愀戀愀猀攀⸀㰀戀爀㸀ഀഀ
    ਍ഀഀ A page is a unit of 8192 bytes with a specific structure. The larger part of a database file, is further organized
    ਍椀渀琀漀 攀砀琀攀渀琀猀⸀ 䔀砀琀攀渀琀猀 愀爀攀 愀 挀漀氀氀攀挀琀椀漀渀 漀昀 攀椀最栀琀 瀀栀礀猀椀挀愀氀氀礀 挀漀渀琀椀最甀漀甀猀 瀀愀最攀猀 愀渀搀 愀爀攀 甀猀攀搀 琀漀 攀昀昀椀挀椀攀渀琀氀礀 洀愀渀愀最攀 琀栀攀 瀀愀最攀猀⸀㰀戀爀㸀ഀഀ
    ਍吀栀攀 ∀愀搀洀椀渀椀猀琀爀愀琀椀漀渀∀ 漀昀 瀀愀最攀猀⼀攀砀琀攀渀琀猀 椀猀 挀漀瘀攀爀攀搀 戀礀 猀漀洀攀 瘀攀爀礀 猀瀀攀挀椀昀椀挀 瀀愀最攀猀㨀㰀戀爀㸀ഀഀ
    ਍ⴀ 吀栀攀 ∀䜀䄀䴀∀ ⠀䜀氀漀戀愀氀 䄀氀氀漀挀愀琀椀漀渀 䴀愀瀀⤀ 愀渀搀 ∀匀䜀䄀䴀∀ ⠀匀栀愀爀攀搀 䜀氀漀戀愀氀 䄀氀氀漀挀愀琀椀漀渀 䴀愀瀀⤀ 欀攀攀瀀猀 琀爀愀挀欀 漀昀 眀栀椀挀栀 攀砀琀攀渀琀猀 㰀戀爀㸀ഀഀ have been allocated, and which are free.
    ਍ⴀ 吀栀攀 ∀倀䘀匀∀ 瀀愀最攀 ⠀倀愀最攀 䘀爀攀攀 匀瀀愀挀攀 瀀愀最攀⤀ 欀攀攀瀀猀 椀渀昀漀爀洀愀琀椀漀渀 愀戀漀甀琀 瀀愀最攀 愀氀氀漀挀愀琀椀漀渀 愀渀搀 昀爀攀攀 猀瀀愀挀攀 愀瘀愀椀氀愀戀氀攀 漀渀 瀀愀最攀猀⸀㰀戀爀㸀ഀഀ
    ਍夀漀甀 眀椀氀氀 昀椀渀搀 愀 䜀䄀䴀 愀渀搀 匀䜀䄀䴀 瀀愀最攀 愀猀 瀀愀最攀 ㈀ 愀渀搀 瀀愀最攀 ㌀ 愀琀 琀栀攀 戀攀最椀渀 漀昀 愀 昀椀氀攀Ⰰ 挀漀瘀攀爀椀渀最 㐀䜀 ⠀㘀㐀　　　⤀ 漀昀 攀砀琀攀渀琀猀⸀㰀戀爀㸀ഀഀ PFS pages can be located at multiple places, but you will find one as page 1 at the beginning of a file.
    ਍䈀礀 琀栀攀 眀愀礀Ⰰ 倀䘀匀 瀀愀最攀猀 愀爀攀 漀渀氀礀 甀猀攀搀 昀漀爀 ∀栀攀愀瀀猀∀Ⰰ 眀栀椀挀栀 愀爀攀 琀愀戀氀攀猀 眀椀琀栀漀甀琀 愀 挀氀甀猀琀攀爀攀搀 椀渀搀攀砀⸀㰀戀爀㸀ഀഀ
    ਍匀漀Ⰰ 琀栀攀 昀椀爀猀琀 瀀愀最攀猀 愀琀 琀栀攀 戀攀最椀渀渀椀渀最 漀昀 愀 搀愀琀愀戀愀猀攀 昀椀氀攀Ⰰ 栀愀瘀攀 愀 猀瀀攀挀椀昀椀挀 瀀甀爀瀀漀猀攀⸀ 氀攀琀✀猀 爀攀挀愀瀀椀琀甀氀愀琀攀 琀栀攀洀 昀椀爀猀琀⸀㰀戀爀㸀 ഀഀ
    ਍㰀䈀㸀伀瘀攀爀瘀椀攀眀 匀瀀攀挀椀昀椀挀 倀愀最攀猀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍瀀愀最攀 　㨀 吀栀攀 瘀攀爀礀 昀椀爀猀琀 瀀愀最攀 椀渀 愀渀礀 昀椀氀攀⸀ 䤀琀✀猀 琀栀攀 ∀昀椀氀攀栀攀愀搀攀爀 瀀愀最攀∀⸀ 椀琀 挀漀渀琀愀椀渀猀 椀渀昀漀爀洀愀琀椀漀渀 愀戀漀甀琀 琀栀攀 昀椀氀攀 椀琀猀攀氀昀Ⰰ㰀戀爀㸀ഀഀ like to what "dbid" (database id) it belongs, on which pages the GAM and SGAM (sort of directory pages) are located etc..
    ਍㰀戀爀㸀ഀഀ page 1: The (first) PFS pages. It keeps track of allocated pages and free space in pages.
    ਍㰀戀爀㸀ഀഀ page 2: The first GAM page.
    ਍㰀戀爀㸀ഀഀ page 3: The first SGAM page.
    ਍㰀戀爀㸀ഀഀ pages 4-7: Specific pages which will track differential, changed pages and logging information.
    ਍㰀戀爀㸀ഀഀ Usually, the first usable page (in an .ndf file) for regular data will start from page 8.
    ਍䤀昀 愀渀 椀渀搀攀砀 眀愀猀 挀爀攀愀琀攀搀 椀渀 琀栀攀 搀愀琀愀戀愀猀攀Ⰰ 瀀愀最攀 㤀 挀漀甀氀搀 愀氀猀漀 戀攀 琀栀攀 昀椀爀猀琀 䤀䄀䴀 瀀愀最攀Ⰰ 甀渀氀攀猀猀 琀栀攀 昀椀爀猀琀㰀戀爀㸀ഀഀ table creation and inserts of data, spanned a number of pages (as of page 8). Then, IAM's occur at later positions.
    ਍㰀戀爀㸀ഀഀ Only for the primary .mdf datafile, page 10 is the "bootpage". It stores specific metadata, as well as Log Sequence Numbers
    ਍昀漀爀 愀氀氀 猀漀爀琀猀 漀昀 漀瀀攀爀愀琀椀漀渀猀 愀猀 昀甀氀氀 戀愀挀欀甀瀀猀Ⰰ 搀椀昀昀攀爀攀渀琀椀愀氀 戀愀挀欀甀瀀猀Ⰰ 挀爀攀愀琀攀 椀渀搀攀砀 䰀匀一✀猀 攀琀挀⸀⸀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ let's play around a bit with the DBCC PAGE() and DBCC DBINFO() commands.
    ਍㰀戀爀㸀ഀഀ Let's first create a new sales database again. If you still have the old one, then just drop it using SQL Server management Studio.
    ਍一漀眀Ⰰ 挀爀攀愀琀攀 愀 渀攀眀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀 甀猀椀渀最 琀栀攀 猀挀爀椀瀀琀 漀昀 挀栀愀瀀琀攀爀 ㄀⸀ 吀栀椀猀 猀挀爀椀瀀琀 挀爀攀愀琀攀猀 愀 搀愀琀愀戀愀猀攀 眀椀琀栀㰀戀爀㸀ഀഀ a specific layout with multiple filegroups, and also will create a few tables on different filegroups.
    ਍㰀戀爀㸀ഀഀ After you have executed the script, let's explore the special pages a bit:
    ਍㰀戀爀㸀ഀഀ Playing around to view some specific pages:
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ DBCC TRACEON (3604)
    ਍䜀伀㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀ⴀⴀ ㄀⸀ 氀攀琀✀猀 琀愀欀攀 愀 氀漀漀欀 愀琀 瀀愀最攀 　 ⠀昀椀氀攀栀攀愀搀攀爀⤀ 椀渀 昀椀氀攀 渀漀 ㌀ ⠀眀栀椀挀栀 椀猀 ∀猀愀氀攀猀开搀愀琀愀ⴀ　㄀⸀渀搀昀∀⤀㬀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍䐀䈀䌀䌀 倀䄀䜀䔀⠀✀猀愀氀攀猀✀Ⰰ㌀Ⰰ　Ⰰ㌀⤀㰀戀爀㸀ഀഀ
    ਍㰀椀洀最 猀爀挀㴀∀猀焀氀挀漀爀爀㐀⸀樀瀀最∀ 愀氀椀最渀㴀∀挀攀渀琀攀爀∀⼀㸀ഀഀ
    ਍伀渀氀礀 瀀愀爀琀椀愀氀 漀甀琀瀀甀琀 椀猀 猀栀漀眀渀 愀戀漀瘀攀⸀㰀戀爀㸀 ഀഀ Do you for example notice the location of the GAM and SGAM pages (at the bottom)
    ਍㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀ⴀⴀ ㈀⸀ 氀攀琀✀猀 琀愀欀攀 愀 氀漀漀欀 愀琀 瀀愀最攀 ㄀ ⠀倀䘀匀⤀ 椀渀 昀椀氀攀 渀漀 ㌀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍䐀䈀䌀䌀 倀䄀䜀䔀⠀✀猀愀氀攀猀✀Ⰰ㌀Ⰰ㄀Ⰰ㌀⤀㰀戀爀㸀ഀഀ
    ਍㰀椀洀最 猀爀挀㴀∀猀焀氀挀漀爀爀㔀⸀樀瀀最∀ 愀氀椀最渀㴀∀挀攀渀琀攀爀∀⼀㸀ഀഀ
    ਍伀渀氀礀 瀀愀爀琀椀愀氀 漀甀琀瀀甀琀 椀猀 猀栀漀眀渀 愀戀漀瘀攀⸀㰀戀爀㸀 ഀഀ Do you for example notice the listing of free and allocated pages (at the bottom)
    ਍㰀戀爀㸀ഀഀ
    ਍㰀䈀㸀ⴀⴀ ㌀⸀ 氀攀琀✀猀 琀愀欀攀 愀 氀漀漀欀 愀琀 瀀愀最攀 ㈀ ⠀䜀䄀䴀 瀀愀最攀⤀ 椀渀 昀椀氀攀 渀漀 ㌀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍䐀䈀䌀䌀 倀䄀䜀䔀⠀✀猀愀氀攀猀✀Ⰰ㌀Ⰰ㈀Ⰰ㌀⤀㰀戀爀㸀ഀഀ
    ਍㰀椀洀最 猀爀挀㴀∀猀焀氀挀漀爀爀㘀⸀樀瀀最∀ 愀氀椀最渀㴀∀挀攀渀琀攀爀∀⼀㸀ഀഀ
    ਍伀渀氀礀 瀀愀爀琀椀愀氀 漀甀琀瀀甀琀 椀猀 猀栀漀眀渀 愀戀漀瘀攀⸀㰀戀爀㸀 ഀഀ Do you for example notice the listing of free and allocated pages (at the bottom)
    ਍㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍㰀栀㌀㸀㔀⸀㈀ 䤀渀愀挀挀攀猀椀戀氀攀 漀爀 匀甀猀瀀攀挀琀 搀愀琀愀戀愀猀攀 搀甀攀 琀漀 挀漀爀爀甀瀀琀椀漀渀 漀昀 愀渀 ⸀渀搀昀 昀椀氀攀㰀⼀栀㌀㸀ഀഀ ਍一漀琀攀㨀 昀漀爀 椀渀昀漀爀洀愀琀椀漀渀 漀渀 眀栀愀琀 琀漀 搀漀 愀琀 愀 洀椀猀猀椀渀最 漀爀 挀漀爀爀甀瀀琀 吀爀愀渀猀愀挀琀椀漀渀 氀漀最㨀 瀀氀攀愀猀攀 猀攀攀 䌀栀愀瀀琀攀爀 㐀⸀㰀戀爀㸀ഀഀ
    ਍伀昀挀漀甀爀猀攀Ⰰ 洀漀猀琀 瀀愀最攀猀 椀渀 愀 搀愀琀愀戀愀猀攀 愀爀攀 漀爀搀椀渀愀爀礀 搀愀琀愀 瀀愀最攀猀 ⠀氀椀欀攀 琀愀戀氀攀ⴀ 愀渀搀 椀渀搀攀砀⤀ 瀀愀最攀猀⸀ 䤀昀 挀漀爀爀甀瀀琀椀漀渀 椀猀 昀漀甀渀搀㰀戀爀㸀ഀഀ here, we have a good chance to repair it.
    ਍㰀戀爀㸀ഀഀ Situation 1: Suspect database, and/or ordinary data pages in an .ndf file are corrupted, then: ਍㰀漀氀㸀ഀഀ
  • Either SQL Server will start normally if it does not need to access the related objects for recovery purpose.

    • ਍㰀氀椀㸀 伀爀Ⰰ 椀琀 洀椀最栀琀 瀀甀琀 琀栀攀 搀愀琀愀戀愀猀攀 椀渀 愀 猀甀猀瀀攀挀琀 猀琀愀琀攀Ⰰ 椀昀 愀琀 猀琀愀爀琀甀瀀 椀琀 渀攀攀搀猀 琀漀 愀挀挀攀猀猀 琀栀攀 挀漀爀爀甀瀀琀 漀戀樀攀挀琀猀 ⠀氀椀欀攀 愀琀 ∀爀漀氀氀戀愀挀欀∀⼀∀爀漀氀氀昀漀爀眀愀爀搀∀⤀⸀㰀⼀氀椀㸀㰀戀爀㸀ഀഀ ਍㰀戀爀㸀ഀഀ Situation 2: Severe corruption or missing .ndf file.
    ਍㰀戀爀㸀ഀഀ In section 5.1, we have seen a few examples of "special" system pages. They surely exist in the beginning of a database file,
    ਍戀甀琀 猀漀洀攀 ⠀洀椀最栀琀⤀ 戀攀 瀀爀攀猀攀渀琀 愀琀 挀攀爀琀愀椀渀 氀漀挀愀琀椀漀渀猀 琀栀爀漀甀最栀漀甀琀 琀栀攀 昀椀氀攀 ⠀氀椀欀攀 琀栀攀 倀䘀匀 瀀愀最攀Ⰰ 䤀䄀䴀✀猀 攀琀挀⸀⸀⤀Ⰰ 搀攀瀀攀渀搀椀渀最㰀戀爀㸀ഀഀ on the size of the file, and the number of objects.
    ਍㰀戀爀㸀ഀഀ ਍㰀䈀㸀匀椀琀甀愀琀椀漀渀 ㄀㨀 漀爀搀椀渀愀爀礀 搀愀琀愀 瀀愀最攀猀 椀渀 愀渀 ⸀渀搀昀 昀椀氀攀 愀爀攀 挀漀爀爀甀瀀琀攀搀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍䰀攀琀✀猀 昀椀爀猀琀 搀攀愀氀 眀椀琀栀 ∀匀椀琀甀愀琀椀漀渀 ㄀∀⸀㰀戀爀㸀ഀഀ Situation 1 is, in general, survivable using the techniques we have seen before.
    ਍䈀甀琀 椀琀 洀甀猀琀 戀攀 猀愀椀搀Ⰰ 琀栀愀琀 椀昀 琀栀攀 吀爀愀渀猀愀挀琀椀漀渀 䰀漀最 椀猀 ⠀瀀栀礀猀椀挀愀氀氀礀 漀爀 氀漀最椀挀愀氀氀礀⤀ 挀漀爀爀甀瀀琀 愀琀 琀栀攀 猀愀洀攀 琀椀洀攀Ⰰ 椀琀 最攀琀猀 愀 戀椀琀 栀愀爀搀攀爀⸀㰀戀爀㸀ഀഀ
    ਍䰀攀琀✀猀 昀椀爀猀琀 攀砀瀀氀漀爀攀 琀栀攀 洀漀爀攀 最攀渀攀爀愀氀 挀愀猀攀㨀㰀戀爀㸀ഀഀ
    ਍䘀椀爀猀琀Ⰰ 椀昀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 匀甀猀瀀攀挀琀Ⰰ 礀漀甀 挀愀渀 琀爀礀 琀漀 瀀甀琀 琀栀攀 搀愀琀愀戀愀猀攀 椀渀 䔀洀攀爀最攀渀挀礀 洀漀搀攀⸀㰀戀爀㸀ഀഀ After that, it is (likely) to be accesible. Then you can try the DBCC CHECKDB command to obtain a list of objects
    ਍眀栀椀挀栀 栀愀瘀攀 挀漀爀爀甀瀀琀 瀀愀最攀猀⸀㰀戀爀㸀ഀഀ Then, it might get clear that you must run DBCC CHECKTABLE with the "repair_allow_data_loss" option, to repair
    ਍琀栀攀 氀椀猀琀 漀昀 琀栀攀 漀戀樀攀挀琀猀⸀ 吀栀椀猀 洀椀最栀琀 爀攀猀甀氀琀 椀渀 ⠀甀猀甀愀氀氀礀 猀氀椀最栀琀⤀ 搀愀琀愀氀漀猀猀⸀ 䈀甀琀 愀琀 琀椀洀攀猀 琀栀愀琀 䤀 栀愀搀 琀漀 甀猀攀㰀戀爀㸀ഀഀ the procedure for "for real", dataloss really did not happen often. So, many corruptions are just logical corruptions,
    ਍椀渀猀琀攀愀搀 漀昀 爀攀愀氀氀礀 琀爀愀猀栀椀渀最 瀀愀最攀猀 ⠀氀椀欀攀 眀攀 栀愀瘀攀 猀攀攀渀 椀渀 䌀栀愀瀀琀攀爀 ㌀⤀⸀㰀戀爀㸀ഀഀ
    ਍吀漀 瀀甀琀 琀栀攀 愀戀漀瘀攀 椀渀 愀 ∀渀攀愀琀∀ 氀椀猀琀㨀㰀戀爀㸀ഀഀ
    ਍⠀㄀⤀㨀 䐀愀琀愀戀愀猀攀 椀猀 猀甀猀瀀攀挀琀⸀ 夀漀甀 琀栀攀渀 琀爀礀㨀㰀戀爀㸀ഀഀ (2): ALTER DATABASE database_name SET EMERGENCY
    ਍⠀㌀⤀㨀 䤀昀 椀琀 琀甀爀渀猀 漀甀琀 琀栀愀琀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 愀挀挀攀猀椀戀氀攀 ⠀昀漀爀 攀砀愀洀瀀氀攀⸀ 礀漀甀 挀愀渀 焀甀攀爀礀 愀 吀愀戀氀攀⤀Ⰰ 礀漀甀 琀栀攀渀 琀爀礀㨀㰀戀爀㸀ഀഀ (4): DBCC CHECKDB(database_name) to obtain a list of corrupt objects and other information.
    ਍⠀㔀⤀㨀 䤀昀 椀琀 琀甀爀渀猀 漀甀琀Ⰰ 琀栀愀琀 漀渀氀礀 琀愀戀氀攀猀 愀渀搀⼀漀爀 椀渀搀攀砀攀猀 愀爀攀 愀昀昀攀挀琀攀搀Ⰰ 琀栀攀渀㰀戀爀㸀ഀഀ (6): Run DBCC CHECKTABLE(table_name) for those tables. See what what minimum "repair clause" is needed.
    ਍⠀㜀⤀㨀 刀甀渀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀⠀琀愀戀氀攀开渀愀洀攀Ⰰ爀攀瀀愀椀爀开爀攀戀甀椀氀搀⤀ 漀爀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀 ⠀琀愀戀氀攀开渀愀洀攀Ⰰ爀攀瀀愀椀爀开愀氀氀漀眀开搀愀琀愀开氀漀猀猀⤀⸀㰀戀爀㸀ഀഀ (8): If all tables are done, run DBCC CHECKDB(database_name) again.
    ਍㰀戀爀㸀ഀഀ Hopefully it has worked.
    ਍㰀戀爀㸀ഀഀ If it turns out, that the Transaction log was "bad" also, try the solutions of sections 4.4 or 4.5.
    ਍㰀戀爀㸀ഀഀ Situation 2: Severe corruption in an .ndf file
    ਍㰀戀爀㸀ഀഀ It's possible, that in such a situation, "partial database availability", can be of use.
    ਍㰀戀爀㸀ഀഀ Since SQL Server 2005, there is a new "state" in which the database can exist, and it's called "partial database availability".
    ਍㰀戀爀㸀ഀഀ If you are familiar with Oracle, you know that you can put a "tablespace" offline. That feature is available since a very long time.
    ਍吀栀攀 ∀吀愀戀氀攀猀瀀愀挀攀∀ 挀漀渀挀攀瀀琀 漀昀 伀爀愀挀氀攀Ⰰ 椀猀 瘀攀爀礀 猀椀洀椀氀愀爀 琀漀 琀栀攀 ∀䘀椀氀攀最爀漀甀瀀∀ 挀漀渀挀攀瀀琀 椀渀 匀儀䰀 匀攀爀瘀攀爀⸀㰀戀爀㸀ഀഀ
    ਍䤀渀 瀀爀椀渀挀椀瀀氀攀Ⰰ 椀琀✀猀 渀漀眀 瀀漀猀猀椀戀氀攀 琀漀 瀀氀愀挀攀 愀 䘀椀氀攀最爀漀甀瀀 漀昀昀氀椀渀攀Ⰰ 眀栀椀氀攀 琀栀攀 爀攀猀琀 漀昀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 猀琀椀氀氀 愀挀挀攀猀猀椀戀氀攀⸀㰀戀爀㸀ഀഀ Ofcourse, if you only have a few Filegroups, then probably you are not helped much, if a large part of the database is not online.
    ਍䈀甀琀Ⰰ 攀瘀攀渀 椀昀 礀漀甀 栀愀瘀攀 漀渀氀礀 愀 猀洀愀氀氀 渀甀洀戀攀爀 漀昀 昀椀氀攀最爀漀甀瀀猀Ⰰ 琀栀攀渀 礀漀甀 猀琀椀氀氀 洀椀最栀琀 栀愀瘀攀 ∀搀椀猀琀爀椀戀甀琀攀搀∀ 琀栀攀 漀戀樀攀挀琀猀 椀渀 愀 猀漀爀琀 漀昀㰀戀爀㸀ഀഀ "functional" meaningfull way, so that the application can still function even if a part of the database is offline.
    ਍伀昀挀漀甀爀猀攀Ⰰ 礀漀甀 挀愀渀渀漀琀 攀砀瀀攀挀琀 琀栀椀猀 琀漀 眀漀爀欀 椀昀 礀漀甀 甀猀攀 愀 猀椀洀瀀氀攀 猀琀爀甀挀琀甀爀攀 氀椀欀攀 栀愀瘀椀渀最 漀渀攀 ⸀洀搀昀 昀椀氀攀 愀渀搀 愀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最⸀㰀戀爀㸀ഀഀ
    ਍匀漀Ⰰ 琀栀攀爀攀 愀爀攀 愀 昀攀眀 爀攀焀甀椀爀攀洀攀渀琀猀Ⰰ 戀攀昀漀爀攀 礀漀甀 挀愀渀 洀攀愀渀椀渀最昀甀氀氀礀 甀猀攀 琀栀攀 ∀瀀愀爀琀椀愀氀 搀愀琀愀戀愀猀攀 愀瘀愀椀氀愀戀椀氀椀琀礀∀⸀㰀戀爀㸀ഀഀ
    ਍ⴀ 吀栀攀 搀愀琀愀戀愀猀攀 猀栀漀甀氀搀 甀猀攀 琀栀攀 ∀䘀甀氀氀 刀攀挀漀瘀攀爀礀 洀漀搀攀氀∀ 昀漀爀 琀漀琀愀氀 爀攀挀漀瘀攀爀礀Ⰰ 戀甀琀 琀栀攀 昀攀愀琀甀爀攀 眀椀氀氀 猀琀椀氀氀 眀漀爀欀 椀昀 琀栀攀 洀漀搀攀氀 椀猀 ∀匀椀洀瀀氀攀∀⸀㰀戀爀㸀ഀഀ - There exists sufficient "structure" in the number and purpose of filegroups.
    ਍㰀戀爀㸀ഀഀ let's try demonstrate the "partial database availablity" feature. Create the SALES database again, using the script from Chapter 1.
    ਍䄀昀琀攀爀 琀栀攀 匀䄀䰀䔀匀 搀愀琀愀戀愀猀攀 椀猀 挀爀攀愀琀攀搀 愀最愀椀渀Ⰰ 眀攀 欀渀漀眀 琀栀愀琀 眀攀 栀愀瘀攀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀 椀渀 琀栀攀 ∀匀䄀䰀䔀匀开䐀䄀吀䄀开　㄀∀ 昀椀氀攀 最爀漀甀瀀Ⰰ㰀戀爀㸀ഀഀ while the EMPLOYEE2 table resides in the Primary Filegroup.
    ਍一漀眀Ⰰ 眀攀 攀砀攀挀甀琀攀 琀栀攀 昀漀氀氀漀眀椀渀最㨀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍䄀䰀吀䔀刀 䐀䄀吀䄀䈀䄀匀䔀 匀䄀䰀䔀匀㰀戀爀㸀ഀഀ MODIFY FILE (NAME = 'SALES_DATA_01' , OFFLINE)
    ਍䜀伀㰀戀爀㸀ഀഀ
    ਍䄀䰀吀䔀刀 䐀䄀吀䄀䈀䄀匀䔀 匀䄀䰀䔀匀 匀䔀吀 伀一䰀䤀一䔀㰀戀爀㸀ഀഀ GO
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ So, the tablespace "SALES_DATA_01" (and thus the file "c:\mssql\data\SALES_DATA_01.ndf") is offline,
    ਍眀栀椀氀攀 琀栀攀 爀攀猀琀 漀昀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 猀琀椀氀氀 漀瀀攀渀⸀ 㰀戀爀㸀ഀഀ Now, what happens if we query the EMPLOYEE and EMPLOYEE2 tables?
    ਍圀攀 攀砀瀀攀挀琀 琀栀愀琀 眀攀 愀爀攀 渀漀琀 愀戀氀攀 琀漀 愀挀挀攀猀猀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀Ⰰ 戀甀琀 焀甀攀爀礀椀渀最 琀栀攀 䔀䴀倀䰀伀夀䔀䔀㈀ 琀愀戀氀攀 猀栀漀甀氀搀 眀漀爀欀⸀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍匀䔀䰀䔀䌀吀 ⨀ 䘀刀伀䴀 䔀䴀倀䰀伀夀䔀䔀㰀戀爀㸀ഀഀ
    ਍䴀猀最 㠀㘀㔀㌀Ⰰ 䰀攀瘀攀氀 ㄀㘀Ⰰ 匀琀愀琀攀 ㄀Ⰰ 䰀椀渀攀 ㄀㰀戀爀㸀ഀഀ The query processor is unable to produce a plan for the table or view 'EMPLOYEE' because the table resides in a filegroup which is not online.
    ਍㰀戀爀㸀ഀഀ
    ਍匀䔀䰀䔀䌀吀 ⨀ 䘀刀伀䴀 䔀䴀倀䰀伀夀䔀䔀㈀㰀戀爀㸀ഀഀ
    ਍䴀倀开䤀䐀      䔀䴀倀开一䄀䴀䔀             匀䄀䰀䄀刀夀㰀戀爀㸀ഀഀ
    ਍㄀           䠀愀爀爀礀                ㈀　　　⸀㔀　㰀戀爀㸀ഀഀ 2 Nadia 3000.00
    ਍㌀           䄀氀戀攀爀琀               㔀　　　⸀　　㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍吀栀椀猀 昀攀愀琀甀爀攀 椀渀搀攀攀搀 眀漀爀欀猀⸀ 㰀戀爀㸀ഀഀ
    ਍匀漀Ⰰ 愀猀 漀昀 匀儀䰀 匀攀爀瘀攀爀 ㈀　　㔀Ⰰ 椀琀 椀猀 瀀漀猀猀椀戀氀攀 琀漀 洀愀爀欀 愀 氀漀猀琀 漀爀 挀漀爀爀甀瀀琀 昀椀氀攀最爀漀甀瀀 愀猀 漀昀昀氀椀渀攀Ⰰ 愀渀搀 琀栀攀渀 戀爀椀渀最 琀栀攀 爀攀猀琀㰀戀爀㸀ഀഀ of the unaffected database online, so that an application might continue to function.
    ਍伀昀挀漀甀爀猀攀Ⰰ 漀戀樀攀挀琀猀 琀栀愀琀 愀爀攀 氀漀挀愀琀攀搀 漀渀 琀栀攀 漀昀昀氀椀渀攀 昀椀氀攀最爀漀甀瀀Ⰰ 愀爀攀 渀漀琀 愀瘀愀椀氀愀戀氀攀⸀㰀戀爀㸀ഀഀ
    ਍吀栀攀 氀愀爀最攀爀 礀漀甀爀 搀愀琀愀戀愀猀攀 椀猀Ⰰ 愀渀搀 琀栀攀 洀漀爀攀 搀椀猀琀爀椀戀甀琀攀搀 漀瘀攀爀 搀椀昀昀攀爀攀渀琀 昀椀氀攀最爀漀甀瀀猀Ⰰ 琀栀攀 戀攀琀琀攀爀 琀栀椀猀 昀攀愀琀甀爀攀 眀椀氀氀 眀漀爀欀⸀㰀戀爀㸀ഀഀ
    ਍伀昀挀漀甀爀猀攀Ⰰ 礀漀甀 猀栀漀甀氀搀 爀攀猀琀漀爀攀 琀栀攀 昀椀氀攀最爀漀甀瀀 愀渀搀 甀猀攀 琀爀愀渀猀愀挀琀椀漀渀 氀漀最 戀愀挀欀甀瀀猀 琀漀 昀甀氀氀礀 爀攀挀漀瘀攀爀⸀㰀戀爀㸀ഀഀ But at least it is a feature where not your entire database is lost.
    ਍㰀戀爀㸀ഀഀ
    ਍㰀戀爀㸀ഀഀ ਍㰀栀㈀㸀䌀栀愀瀀琀攀爀 㘀㨀 伀瘀攀爀瘀椀攀眀 漀昀 匀儀䰀 匀攀爀瘀攀爀 刀攀瀀愀椀爀 挀漀洀洀愀渀搀猀⸀㰀⼀栀㈀㸀㰀戀爀㸀ഀഀ ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ ਍㰀栀㌀㸀㄀⸀ 倀刀伀䈀䰀䔀䴀匀 圀䤀吀䠀 吀刀䄀一匀䄀䌀吀䤀伀一 䰀伀䜀㨀㰀⼀栀㌀㸀ഀഀ ਍匀攀攀 挀栀愀瀀琀攀爀 㐀 昀漀爀 攀砀瀀氀愀渀愀琀椀漀渀猀⸀ 夀漀甀 洀椀最栀琀 琀爀礀 漀渀攀 漀昀 琀栀攀 昀漀氀氀漀眀椀渀最㨀㰀戀爀㸀ഀഀ
    ਍ഀഀ ਍㄀⸀ 䄀䰀吀䔀刀 䐀䄀吀䄀䈀䄀匀䔀 䐀䄀吀䄀䈀䄀匀䔀开一䄀䴀䔀 刀䔀䈀唀䤀䰀䐀 䰀伀䜀 伀一 ⠀一䄀䴀䔀㴀氀漀最椀挀愀氀开渀愀洀攀Ⰰ䘀䤀䰀䔀一䄀䴀䔀㴀瀀栀礀猀椀挀愀氀开渀愀洀攀⤀㰀戀爀㸀ഀഀ
    ਍㈀⸀ 䌀刀䔀䄀吀䔀 䐀䄀吀䄀䈀䄀匀䔀 䐀䄀吀䄀䈀䄀匀䔀开一䄀䴀䔀 伀一 ⠀䘀䤀䰀䔀一䄀䴀䔀 㴀 ✀瀀愀琀栀 琀漀 洀搀昀 昀椀氀攀✀⤀ 䘀伀刀 䄀吀吀䄀䌀䠀开䘀伀刀䌀䔀开刀䔀䈀唀䤀䰀䐀开䰀伀䜀㰀戀爀㸀ഀഀ
    ਍㌀⸀ 䄀䰀吀䔀刀 䐀䄀吀䄀䈀䄀匀䔀 䐀䄀吀䄀䈀䄀匀䔀开一䄀䴀䔀 匀䔀吀 䔀䴀䔀刀䜀䔀一䌀夀 ⠀渀漀琀 爀攀挀漀洀洀攀渀搀攀搀⤀㰀戀爀㸀ഀഀ
    ਍㐀⸀ 䤀昀 攀瘀攀爀礀琀栀椀渀最 昀愀椀氀猀 ⠀搀漀渀✀琀 甀猀攀 椀琀Ⰰ 甀渀氀攀猀猀 漀甀琀 漀昀 漀瀀琀椀漀渀猀⤀㨀㰀戀爀㸀ഀഀ
    ਍ⴀ 䐀攀琀愀挀栀 琀栀攀 搀愀琀愀戀愀猀攀⸀㰀戀爀㸀ഀഀ - Shutdown SQL Server.
    ਍ⴀ 刀攀渀愀洀攀 ⸀洀搀昀 愀渀搀 愀氀氀 ⸀渀搀昀 昀椀氀攀猀 琀漀 ⸀漀氀搀 ⠀愀氀氀 搀愀琀愀戀愀猀攀 昀椀氀攀猀⤀㰀戀爀㸀ഀഀ - Create a small blank database with the same name, and exactly same filegroups and physical files.
    ਍ⴀ 䐀攀琀愀挀栀 琀栀攀 渀攀眀 搀愀琀愀戀愀猀攀㰀戀爀㸀ഀഀ - Shutdown SQL Server.
    ਍ⴀ 䐀攀氀攀琀攀 琀栀攀 ⸀洀搀昀 愀渀搀 ⸀渀搀昀 昀椀氀攀猀 漀昀 戀氀愀渀欀 搀愀琀愀戀愀猀攀Ⰰ 攀砀挀攀瀀琀 琀栀攀 氀漀最 ⸀氀搀昀 昀椀氀攀⸀㰀戀爀㸀ഀഀ - Rename the .old files to original .mdf and .ndf files.
    ਍ⴀ 匀琀愀爀琀 匀儀䰀 匀攀爀瘀攀爀㰀戀爀㸀ഀഀ - Use CREATE DATABASE DATABASE_NAME ON (FILENAME = 'path to mdf file') FOR ATTACH_FORCE_REBUILD_LOG
    ਍ⴀ 䤀昀 搀愀琀愀戀愀猀攀 漀瀀攀渀攀渀搀㨀 甀猀攀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀䐀䈀㰀戀爀㸀ഀഀ - If database Suspect: ALTER DATABASE DATABASE_NAME SET EMERGENCY
    ਍ⴀ 甀猀攀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀䐀䈀Ⰰ 愀渀搀 琀栀攀渀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀⠀⤀ 昀漀爀 愀氀氀 愀昀昀攀挀琀攀搀 漀戀樀攀挀琀猀⸀㰀戀爀㸀ഀഀ
    ਍㰀戀爀㸀ഀഀ

    2. PROBLEMS WITH OBJECTS LIKE TABLES, CORRUPT DATA PAGES:

    ਍ഀഀ See chapters 2 and 3 for explanations. You might try one of the following:
    ਍㰀戀爀㸀ഀഀ 1. Retreiving information on possible corrupt objects:
    ਍㰀戀爀㸀ഀഀ DBCC CHECKDB(DATABASE_NAME) -- only listing, not repairing
    ਍㰀戀爀㸀ഀഀ 2. Establishing level of corruption of a table and associated objects:
    ਍㰀戀爀㸀ഀഀ DBCC CHECKTABLE(TABLE_NAME) -- only listing, not repairing
    ਍㰀戀爀㸀ഀഀ 3. Repair logical corruption like checksum, index entries, pointers:
    ਍㰀戀爀㸀ഀഀ DBCC CHECKTABLE(TABLE_NAME,repair_rebuild) -- no dataloss
    ਍㰀戀爀㸀ഀഀ 4. Repair hard corruption (possible de-allocation of some pages):
    ਍㰀戀爀㸀ഀഀ DBCC CHECKTABLE (TABLE_NAME,repair_allow_data_loss) -- possibly some dataloss
    ਍㰀戀爀㸀ഀഀ 5. REPAIR A COMPLETE DATABASE (not recommended, see also chapter 3):
    ਍㰀戀爀㸀ഀഀ DBCC CHECKDB(database_name, repair_rebuild)
    ਍漀爀㰀戀爀㸀ഀഀ DBCC CHECKDB(database_name, repair_allow_data_loss)
    ਍㰀戀爀㸀ഀഀ Note:
    ਍䤀昀 愀 琀愀戀氀攀 栀愀猀 漀渀氀礀 ∀猀漀昀琀∀ 挀漀爀爀甀瀀琀椀漀渀 ⠀昀漀爀 攀砀愀洀瀀氀攀 眀爀漀渀最 挀栀攀挀欀猀甀洀 漀渀 漀渀攀 漀爀 洀漀爀攀 搀愀琀愀 瀀愀最攀猀⤀Ⰰ㰀戀爀㸀 ഀഀ setting the "page verify" database option to NONE, allows you to select the table again.
    ਍㰀戀爀㸀ഀഀ

    3. PROBLEMS WITH A FILE OR FILEGROUP:

    ਍ഀഀ See chapter 5 for explanations.
    ਍㰀戀爀㸀ഀഀ => Try to place the "faulty" Filegroup offline.
    ਍㰀戀爀㸀ഀഀ -- Execute below statements as "one" batch:
    ਍㰀戀爀㸀ഀഀ ALTER DATABASE DATABASE_NAME
    ਍䴀伀䐀䤀䘀夀 䘀䤀䰀䔀 ⠀一䄀䴀䔀 㴀 ✀䘀椀氀攀最爀漀甀瀀开一愀洀攀✀ Ⰰ 伀䘀䘀䰀䤀一䔀⤀㰀戀爀㸀ഀഀ GO
    ਍㰀戀爀㸀ഀഀ ALTER DATABASE DATABASE_NAME SET ONLINE
    ਍䜀伀㰀戀爀㸀ഀഀ
    ਍䤀昀 椀琀 眀漀爀欀猀Ⰰ 琀栀攀 爀攀猀琀 漀昀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 漀渀氀椀渀攀⸀㰀戀爀㸀ഀഀ
    ਍㴀㸀 䄀猀 愀渀漀琀栀攀爀 瀀漀猀猀椀戀氀攀 洀攀琀栀漀搀㨀㰀戀爀㸀ഀഀ
    ਍⠀㄀⤀㨀 䐀愀琀愀戀愀猀攀 椀猀 猀甀猀瀀攀挀琀⸀ 夀漀甀 琀栀攀渀 琀爀礀㨀㰀戀爀㸀ഀഀ (2): ALTER DATABASE database_name SET EMERGENCY
    ਍⠀㌀⤀㨀 䤀昀 椀琀 琀甀爀渀猀 漀甀琀 琀栀愀琀 琀栀攀 搀愀琀愀戀愀猀攀 椀猀 愀挀挀攀猀椀戀氀攀 ⠀昀漀爀 攀砀愀洀瀀氀攀⸀ 礀漀甀 挀愀渀 焀甀攀爀礀 愀 吀愀戀氀攀⤀Ⰰ 礀漀甀 琀栀攀渀 琀爀礀㨀㰀戀爀㸀ഀഀ (4): DBCC CHECKDB(database_name) to obtain a list of corrupt objects and other information.
    ਍⠀㔀⤀㨀 䤀昀 椀琀 琀甀爀渀猀 漀甀琀Ⰰ 琀栀愀琀 漀渀氀礀 琀愀戀氀攀猀 愀渀搀⼀漀爀 椀渀搀攀砀攀猀 愀爀攀 愀昀昀攀挀琀攀搀Ⰰ 琀栀攀渀㰀戀爀㸀ഀഀ (6): Run DBCC CHECKTABLE(table_name) for those tables. See what what minimum "repair clause" is needed.
    ਍⠀㜀⤀㨀 刀甀渀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀⠀琀愀戀氀攀开渀愀洀攀Ⰰ爀攀瀀愀椀爀开爀攀戀甀椀氀搀⤀ 漀爀 䐀䈀䌀䌀 䌀䠀䔀䌀䬀吀䄀䈀䰀䔀 ⠀琀愀戀氀攀开渀愀洀攀Ⰰ爀攀瀀愀椀爀开愀氀氀漀眀开搀愀琀愀开氀漀猀猀⤀⸀㰀戀爀㸀ഀഀ (8): If all tables are done, run DBCC CHECKDB(database_name) again.
    ਍㰀戀爀㸀ഀഀ
    ਍㰀栀㌀㸀㐀⸀ 匀伀䴀䔀 匀唀倀倀伀刀吀䤀一䜀 匀儀䰀㰀⼀栀㌀㸀ഀഀ ਍匀攀攀 挀栀愀瀀琀攀爀 ㈀ 愀渀搀 ㌀ 昀漀爀 攀砀愀洀瀀氀攀猀⸀㰀戀爀㸀ഀഀ
    ਍㄀⸀ 嘀椀攀眀 愀 搀愀琀愀戀愀猀攀 瀀愀最攀㨀㰀戀爀㸀ഀഀ
    ਍䐀䈀䌀䌀 吀刀䄀䌀䔀伀一 ⠀㌀㘀　㐀⤀㰀戀爀㸀ഀഀ GO
    ਍㰀戀爀㸀ഀഀ DBCC PAGE('database_name', file_id, pagenumber, output_mode) -- e.g.: DBCC PAGE('sales',3,30,3)
    ਍㰀戀爀㸀ഀഀ 2. Get object_id from object_name, or the other way around:
    ਍㰀戀爀㸀ഀഀ SELECT OBJECT_ID(object_name)
    ਍匀䔀䰀䔀䌀吀 伀䈀䨀䔀䌀吀开一䄀䴀䔀⠀漀戀樀攀挀琀开椀搀⤀㰀戀爀㸀ഀഀ
    ਍㌀⸀ 䜀攀琀 愀 氀椀猀琀 漀昀 琀愀戀氀攀猀 愀渀搀 琀栀攀椀爀 愀猀猀漀挀椀愀琀攀搀 椀渀搀攀砀攀猀㨀㰀戀爀㸀ഀഀ
    ਍匀䔀䰀䔀䌀吀㰀戀爀㸀ഀഀ substring(sysobjects.name,1,50) AS TABLENAME,
    ਍猀甀戀猀琀爀椀渀最⠀猀礀猀椀渀搀攀砀攀猀⸀渀愀洀攀Ⰰ㄀Ⰰ㔀　⤀ 䄀匀 䤀一䐀䔀堀一䄀䴀䔀Ⰰ㰀戀爀㸀 ഀഀ sysobjects.id, sysindexes.indid, sysindexes.groupid,sysindexes.rows
    ਍䘀刀伀䴀 猀礀猀漀戀樀攀挀琀猀Ⰰ 猀礀猀椀渀搀攀砀攀猀㰀戀爀㸀ഀഀ WHERE sysobjects.id=sysindexes.id
    ਍伀刀䐀䔀刀 䈀夀 猀礀猀椀渀搀攀砀攀猀⸀爀漀眀猀 搀攀猀挀㰀戀爀㸀ഀഀ
    ਍㐀⸀ 䬀椀氀氀 愀 猀攀猀猀椀漀渀 ⠀猀瀀椀搀⤀㨀㰀戀爀㸀ഀഀ
    ਍欀椀氀氀 猀瀀椀搀  ⴀⴀ 攀⸀最⸀㨀 欀椀氀氀 ㄀㌀㈀㰀戀爀㸀ഀഀ
    ਍㔀⸀ 匀栀漀眀 匀儀䰀 匀攀爀瘀攀爀 瘀攀爀猀椀漀渀㨀㰀戀爀㸀ഀഀ
    ਍匀䔀䰀䔀䌀吀 䀀䀀嘀䔀刀匀䤀伀一㰀戀爀㸀ഀഀ
    ਍㘀⸀ 䜀攀琀 圀椀渀搀漀眀猀 瘀攀爀猀椀漀渀㨀㰀戀爀㸀ഀഀ
    ਍ⴀ㸀 猀琀愀爀琀 挀洀搀 ⴀ㸀 眀椀渀瘀攀爀㰀戀爀㸀ഀഀ -> start cmd -> systeminfo
    ਍ⴀ㸀 猀琀愀爀琀 挀洀搀 ⴀ㸀 猀礀猀琀攀洀椀渀昀漀 㸀 氀椀猀琀⸀琀砀琀  ⠀琀栀攀渀 漀瀀攀渀 氀椀猀琀⸀琀砀琀 眀椀琀栀 渀漀琀攀瀀愀搀⤀㰀戀爀㸀ഀഀ
    ਍㜀⸀ 伀琀栀攀爀 ㈀　　㔀⼀㈀　　㠀㨀㰀戀爀㸀ഀഀ
    ਍㰀愀 栀爀攀昀㴀∀栀琀琀瀀㨀⼀⼀愀渀琀愀瀀攀砀⸀漀爀最⼀猀焀氀猀攀爀瘀攀爀猀攀猀猀椀漀渀猀⸀琀砀琀∀㸀匀栀漀眀椀渀最 猀攀猀猀椀漀渀猀Ⰰ 猀瀀椀搀猀Ⰰ 愀渀搀 戀氀漀挀欀攀搀 猀攀猀猀椀漀渀猀㰀⼀愀㸀㰀戀爀㸀ഀഀ Showing backup history from the MSDB database
    ਍㰀愀 栀爀攀昀㴀∀栀琀琀瀀㨀⼀⼀愀渀琀愀瀀攀砀⸀漀爀最⼀猀焀氀猀攀爀瘀攀爀椀渀搀攀砀攀猀⸀琀砀琀∀㸀匀栀漀眀椀渀最 椀渀搀攀砀 椀渀昀漀爀洀愀琀椀漀渀 ⠀渀甀洀戀攀爀 漀昀 爀漀眀猀Ⰰ 昀爀愀最洀攀渀琀愀琀椀漀渀 攀琀挀⸀⸀⤀㰀⼀愀㸀㰀戀爀㸀ഀഀ Showing database file info (sizes,paths,%free/%full etc..)
    ਍㰀愀 栀爀攀昀㴀∀栀琀琀瀀㨀⼀⼀愀渀琀愀瀀攀砀⸀漀爀最⼀猀焀氀猀攀爀瘀攀爀眀愀椀琀猀⸀琀砀琀∀㸀匀栀漀眀椀渀最 匀儀䰀 匀攀爀瘀攀爀 眀愀椀琀猀㰀⼀愀㸀㰀戀爀㸀ഀഀ Note describing SQL Server backup and restore using TSQL
    ਍㰀戀爀㸀ഀഀ ਍ഀഀ
    ਍ഀഀ ਍㰀栀㈀㸀䌀栀愀瀀琀攀爀 㜀㨀 唀猀椀渀最 焀甀攀爀椀攀猀 琀漀 猀愀氀瘀愀最攀 搀愀琀愀 昀爀漀洀 挀漀爀爀甀瀀琀 琀愀戀氀攀猀⸀㰀⼀栀㈀㸀㰀戀爀㸀ഀഀ ਍䤀渀猀琀攀愀搀 漀昀 甀猀椀渀最 爀攀瀀愀椀爀 挀漀洀洀愀渀搀猀Ⰰ 眀攀 洀椀最栀琀 戀攀 愀戀氀攀 琀漀 甀猀攀 愀渀 愀氀琀攀爀渀愀琀椀瘀攀 洀攀琀栀漀搀⸀ 䤀渀 琀栀椀猀 挀愀猀攀Ⰰ 眀攀 猀椀洀瀀氀礀 甀猀攀 焀甀攀爀椀攀猀⸀㰀戀爀㸀ഀഀ The trick then is, to try to select "around" corrupt pages.
    ਍㰀戀爀㸀ഀഀ Actually, the best way to repair your table, is by using the methods as shown in chapters 2 and 3.
    ਍䈀甀琀 椀渀 琀栀攀 甀渀氀椀欀攀氀礀 攀瘀攀渀琀 琀栀愀琀 琀栀漀猀攀 洀攀琀栀漀搀猀 搀漀攀猀 渀漀琀 眀漀爀欀Ⰰ 礀漀甀 洀椀最栀琀 猀愀瘀攀 搀愀琀愀 甀猀椀渀最 焀甀攀爀椀攀猀⸀㰀戀爀⸀ഀഀ
    ਍圀攀 洀椀最栀琀 挀漀渀猀椀搀攀爀 琀眀漀 挀愀琀攀最漀爀椀攀猀㨀㰀戀爀㸀ഀഀ
    ਍㄀⸀ 吀栀攀 琀愀戀氀攀 栀愀猀 漀渀攀 漀爀 洀漀爀攀 渀漀渀ⴀ挀氀甀猀琀攀爀攀搀 椀渀搀攀砀攀猀⸀㰀戀爀㸀ഀഀ 2. The table does not have indexes, or those indexes are unusable for our purposes.
    ਍ഀഀ
    ਍㰀䈀㸀䌀愀猀攀 ㄀㨀 吀栀攀 琀愀戀氀攀 栀愀猀 漀渀攀 漀爀 洀漀爀攀 渀漀渀ⴀ挀氀甀猀琀攀爀攀搀 椀渀搀攀砀攀猀㨀㰀⼀䈀㸀㰀戀爀㸀ഀഀ
    ਍圀栀礀 眀漀甀氀搀 椀渀搀攀砀攀猀 栀攀氀瀀㼀㰀戀爀㸀ഀഀ Suppose a table is so severely damaged, that you are hardly able to query data from the table itself.
    ਍一漀眀Ⰰ 礀漀甀 欀渀漀眀 琀栀愀琀 愀 渀漀渀挀氀甀猀琀攀爀攀搀 椀渀搀攀砀 椀猀 愀渀 攀砀琀攀爀渀愀氀 漀戀樀攀挀琀Ⰰ 愀渀搀 椀琀 ∀挀漀渀琀愀椀渀猀∀ 漀渀攀 漀爀 洀漀爀攀 挀漀氀甀洀渀猀㰀戀爀㸀ഀഀ of the table, from all rows of the table.
    ਍匀漀Ⰰ 椀昀 礀漀甀 眀漀甀氀搀 甀猀攀 愀 㰀䤀㸀挀漀瘀攀爀椀渀最 焀甀攀爀礀㰀⼀䤀㸀Ⰰ 琀栀愀琀 漀渀氀礀 猀攀氀攀挀琀猀 琀栀攀 挀漀氀甀洀渀猀 挀漀渀琀愀椀渀攀搀 椀渀 琀栀攀 椀渀搀攀砀Ⰰ㰀戀爀㸀ഀഀ and the Query Optimizer decides to use that index, then the table is not accessed at all!
    ਍䄀渀搀 猀甀瀀瀀漀猀攀 昀甀爀琀栀攀爀Ⰰ 琀栀愀琀 洀甀氀琀椀瀀氀攀 椀渀搀攀砀攀猀 攀砀椀猀琀Ⰰ 琀栀攀渀 礀漀甀 洀椀最栀琀 攀瘀攀渀 戀攀 愀戀氀攀 琀漀 爀攀琀爀攀椀瘀攀 愀 爀攀氀攀瘀愀渀琀 愀洀漀甀渀琀 漀昀 搀愀琀愀⸀㰀戀爀㸀ഀഀ
    ਍䰀攀琀✀猀 琀爀礀 椀琀 甀猀椀渀最 愀 瘀攀爀礀 猀椀洀瀀氀攀 攀砀愀洀瀀氀攀⸀ 圀攀 眀椀氀氀 甀猀攀 琀栀攀 攀砀愀洀瀀氀攀 漀昀 琀栀攀 ∀栀愀爀搀∀ 挀漀爀爀甀瀀琀攀搀 瀀愀最攀猀㰀戀爀㸀ഀഀ as shown in Chapter 3.
    ਍伀渀氀礀 琀栀椀猀 琀椀洀攀Ⰰ 昀漀爀 琀栀攀 猀愀欀攀 漀昀 搀攀洀漀渀猀琀爀愀琀椀漀渀Ⰰ 琀栀攀 椀渀搀攀砀 漀渀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀Ⰰ 眀椀氀氀 甀猀攀 琀栀攀 䔀䴀倀开一䄀䴀䔀 愀渀搀 匀䄀䰀䄀刀夀 挀漀氀甀洀渀猀⸀㰀戀爀㸀ഀഀ So, create the SALES database, the EMPLOYEE and EMPLOYEE2 tables, and create a non-clustered index as follows:
    ਍㰀戀爀㸀ഀഀ ਍䌀刀䔀䄀吀䔀 一伀一䌀䰀唀匀吀䔀刀䔀䐀 䤀一䐀䔀堀 椀渀搀砀开攀洀瀀氀漀礀攀攀开攀洀瀀渀愀洀攀 伀一 䔀䴀倀䰀伀夀䔀䔀⠀䔀䴀倀开一䄀䴀䔀Ⰰ匀䄀䰀䄀刀夀⤀ 伀一 匀䄀䰀䔀匀䤀一䐀䔀堀　㄀㰀戀爀㸀ഀഀ GO
    ਍㰀戀爀㸀ഀഀ ਍一漀眀Ⰰ 攀砀攀挀甀琀攀 琀栀攀 猀挀爀椀瀀琀 琀栀愀琀 椀渀猀攀爀琀猀 㤀㤀㤀㤀 爀漀眀猀 椀渀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀⸀㰀戀爀㸀ഀഀ Next, do the procedure as listed in chapter 3, to hard corrupt a couple of data pages of the EMPLOYEE table.
    ਍㰀戀爀㸀ഀഀ As you have seen in Chapter3 (and what you maybe are doing again if you practise this example), you cannot
    ਍猀攀氀攀挀琀 琀栀攀 䔀䴀倀䰀伀夀䔀䔀 琀愀戀氀攀㨀 匀儀䰀 匀攀爀瘀攀爀 眀椀氀氀 挀漀洀攀 甀瀀 眀椀琀栀 愀渀 攀爀爀漀爀洀攀猀猀愀最攀 瀀爀攀瘀攀渀琀椀渀最 礀漀甀 琀漀 爀攀琀爀攀椀瘀攀 琀栀攀 爀漀眀猀⸀㰀戀爀㸀ഀഀ
    ਍一漀眀Ⰰ 眀椀琀栀 漀甀爀 渀漀渀ⴀ挀氀甀猀琀攀爀攀搀 椀渀搀攀砀Ⰰ 眀攀 洀椀最栀琀 戀攀 愀戀氀攀 琀漀 甀猀攀 琀栀椀猀 琀爀椀挀欀⸀ 䄀挀琀甀愀氀氀礀Ⰰ 椀琀✀猀 渀漀琀 愀 ∀琀爀椀挀欀∀ 漀昀挀漀甀爀猀攀Ⰰ㰀戀爀㸀ഀഀ since we will use a "covering" query which forces the optimizer to use the index, and the table is not touched at all.
    ਍匀漀Ⰰ 渀漀眀 琀爀礀 琀栀椀猀㨀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀挀漀甀爀椀攀爀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ SELECT emp_name, salary FROM EMPLOYEE WITH (INDEX(indx_employee_empname))
    ਍䜀伀㰀戀爀㸀ഀഀ
    ਍㰀昀漀渀琀 昀愀挀攀㴀∀愀爀椀愀氀∀ 猀椀稀攀㴀㈀ 挀漀氀漀爀㴀∀戀氀愀挀欀∀㸀ഀഀ SQL Server will return all rows, that is all EMP_NAMES and all SALARIES. Only the EMP_ID is missing,
    ਍猀椀渀挀攀 琀栀愀琀 椀猀 渀漀琀 ∀瀀愀爀琀∀ 漀昀 琀栀攀 椀渀搀攀砀⸀㰀戀爀㸀ഀഀ
    ਍吀爀甀攀Ⰰ 椀琀✀猀 挀攀爀琀愀椀渀氀礀 渀漀琀 瀀攀爀昀攀挀琀⸀ 䈀甀琀 猀甀瀀瀀漀猀攀 洀甀氀琀椀瀀氀攀 渀漀渀ⴀ挀氀甀猀琀攀爀攀搀 椀渀搀攀砀攀猀 漀渀 礀漀甀爀 搀愀洀愀最攀搀 琀愀戀氀攀 攀砀椀猀琀猀Ⰰ㰀戀爀㸀ഀഀ all using different columns, then you would be able to construct several covering queries, and you might be able
    ਍琀漀 爀攀琀爀攀椀瘀攀 愀 爀攀氀攀瘀愀渀琀 愀洀漀甀渀琀 漀昀 搀愀琀愀⸀㰀戀爀㸀ഀഀ
    ਍㰀戀爀㸀ഀഀ So, for now I will quit working on this document. Next, I will start a similar note on Oracle.
    ਍㰀戀爀㸀ഀഀ I certainly hope it was of use ! ਍㰀戀爀㸀ഀഀ
    ਍㰀⼀戀漀搀礀㸀ഀഀ