SQLserver数据文件(MDF)的页面文件头结构剖析

SQLserver数据文件(MDF)的页面文件头结构剖析

先执行一下以下SQL语句，我的测试环境为SQL2005

dbcctraceon(3604)

go
dbccpage(master,1,0,2)

可以看到MDF文件的一些物理结构信息，其中包括重要的头96个字节。也就是第一个页面的文件头。

........

PAGE HEADER:

Page @0x03FA0000

m_pageId = (1:0) m_headerVersion = 1 m_type = 15
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8
m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99
m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 0
m_slotCnt = 1 m_freeCnt = 7937 m_freeData = 3059
m_reservedCnt = 0 m_lsn = (149:448:1) m_xactReserved = 0
m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = -1073741694

........

DATA:

Memory Dump @0x62FEC000

62FEC000: 010f0000 08000000 00000000 00000000 †................
62FEC010: 00000000 00000100 63000000 011ff30b †........c.......
62FEC020: 00000000 01000000 95000000 c0010000 †................
62FEC030: 01000000 00000000 00000000 820000c0 †................
62FEC040: 00000000 00000000 00000000 00000000 †................
62FEC050: 00000000 00000000 00000000 00000000 †................

以上蓝色的文字就是文件头的一些信息。如果这些信息损坏将会造成严重的后果。

经过简单的逐个字节分析，中间借助了windows计算器和c#的BitConverter.GetBytes函数。得出了如下文件结构图，其中每行4个字节，一共分析了文件头的前64个字节。

 

00:0Fm_headerVersionm_typem_typeFlagBitsm_levelm_flagBitsm_indexIdm_prevPage(2)m_prevPage(1)pminlen10:1Fm_nextPage(2)m_nextPage(1)m_slotCntAllocUnitId.idObjm_freeCntm_freeData20:2Fm_pageId（2）m_pageId（1）m_reservedCntm_lsn(1)m_lsn(2)30:3Fm_lsn(3)m_xactReservedm_xdesId(2)m_xdesId(1)m_ghostRecCntm_tornBits

在数据库的头96个字节中第0x40开始直道0x5F应该都是0。

我发现只有测试页的m_pageId 的冒号前面的数字不为1时才在0x40到0x5f写入数据。但是具体代表什么还没有看出来。

姑且认为数据库第一个页面的0x00-0x3f就如上图所示，0x40-0x5f都为0（不正确的话请纠正一下）

这张图有什么用呢，如果你理解了上述参数的意义，用二进制编辑器打开一个头文件损坏的mdf文件就有可能恢复这个已经损坏的数据库。

偶不是dba也不是专业恢复数据的，只是个普通的开发人员，怎么恢复还请有经验人士补充一下。

有情提醒，这些东西非常危险，请不要随意测试，最好找一个没用的数据库来研究。

参数的意义

m_pageId
Thisidentifiesthefilenumberthepageispartofandthepositionwithinthefile.(1:143)meanspage143infile1.
m_headerVersion
Thisisthepageheaderversion.Sinceversion7.0thisvaluehasalwaysbeen1.
m_typea
Thisisthepagetype.Thevaluesyou'relikelytoseeare:
1-datapage.Thisholdsdatarecordsinaheaporclusteredindexleaf-level.
2-indexpage.Thisholdsindexrecordsintheupperlevelsofaclusteredindexandalllevelsofnon-clusteredindexes.
3-textmixpage.AtextpagethatholdssmallchunksofLOBvaluesplusinternalpartsoftexttree.ThesecanbesharedbetweenLOBvaluesinthesamepartitionofanindexorheap.
4-texttreepage.AtextpagethatholdslargechunksofLOBvaluesfromasinglecolumnvalue.
7-sortpage.Apagethatstoresintermediateresultsduringasortoperation.
8-GAMpage.HoldsglobalallocationinformationaboutextentsinaGAMinterval(everydatafileissplitinto4GBchunks-thenumberofextentsthatcanberepresentedinabitmaponasingledatabasepage).Basicallywhetheranextentisallocatedornot.GAM=GlobalAllocationMap.Thefirstoneispage2ineachfile.Moreontheseinalaterpost.
9-SGAMpage.HoldsglobalallocationinformationaboutextentsinaGAMinterval.Basicallywhetheranextentisavailableforallocatingmixed-pages.SGAM=SharedGAM.thefirstoneispage3ineachfile.Moreontheseinalaterpost.
10-IAMpage.HoldsallocationinformationaboutwhichextentswithinaGAMintervalareallocatedtoanindexorallocationunit,inSQLServer2000and2005respectively.IAM=IndexAllocationMap.Moreontheseinalaterpost.
11-PFSpage.HoldsallocationandfreespaceinformationaboutpageswithinaPFSinterval(everydatafileisalsosplitintoapprox64MBchunks-thenumberofpagesthatcanberepresentedinabyte-maponasingledatabasepage.PFS=PageFreeSpace.Thefirstoneispage1ineachfile.Moreontheseinalaterpost.
13-bootpage.Holdsinformationaboutthedatabase.There'sonlyoneoftheseinthedatabase.It'spage9infile1.
15-fileheaderpage.Holdsinformationaboutthefile.There'soneperfileandit'spage0inthefile.
16-diffmappage.HoldsinformationaboutwhichextentsinaGAMintervalhavechangedsincethelastfullordifferentialbackup.Thefirstoneispage6ineachfile.
17-MLmappage.HoldsinformationaboutwhichextentsinaGAMintervalhavechangedwhileinbulk-loggedmodesincethelastbackup.Thisiswhatallowsyoutoswitchtobulk-loggedmodeforbulk-loadsandindexrebuildswithoutworryingaboutbreakingabackupchain.Thefirstoneispage7ineachfile.
m_typeFlagBits
Thisismostlyunused.Fordataandindexpagesitwillalwaysbe4.Forallotherpagesitwillalwaysbe0-exceptPFSpages.IfaPFSpagehasm_typeFlagBitsof1,thatmeansthatatleastoneofthepagesinthePFSintervalmappedbythePFSpagehasatleastoneghostrecord.
m_level
Thisisthelevelthatthepageispartofintheb-tree.
Levelsarenumberedfrom0attheleaf-levelandincreasetothesingle-pagerootlevel(i.e.thetopoftheb-tree).
InSQLServer2000,theleaflevelofaclusteredindex(withdatapages)waslevel0,andthenextlevelup(withindexpages)wasalsolevel0.Thelevelthenincreasedtotheroot.SotodeterminewhetherapagewastrulyattheleaflevelinSQLServer2000,youneedtolookatthem_typeaswellasthem_level.
Forallpagetypesapartfromindexpages,thelevelisalways0.
m_flagBits
Thisstoresanumberofdifferentflagsthatdescribethepage.Forexample,0x200meansthatthepagehasapagechecksumonit(asourexamplepagedoes)and0x100meansthepagehastorn-pageprotectiononit.
SomebitsarenolongerusedinSQLServer2005.
m_objId
m_indexId
InSQLServer2000,theseidentifiedtheactualrelationalobjectandindexIDstowhichthepageisallocated.InSQLServer2005thisisnolongerthecase.Theallocationmetadatatotallychangedsotheseinsteadidentifywhat'scalledtheallocationunitthatthepagebelongsto(I'lldoanotherpostthatdescribestheselatertoday).
m_prevPage
m_nextPage
Thesearepointerstothepreviousandnextpagesatthisleveloftheb-treeandstore6-bytepageIDs.
Thepagesineachlevelofanindexarejoinedinadoubly-linkedlistaccordingtothelogicalorder(asdefinedbytheindexkeys)oftheindex.Thepointersdonotnecessarilypointtotheimmediatelyadjacentphysicalpagesinthefile(becauseoffragmentation).
Thepagesontheleft-handsideofab-treelevelwillhavethem_prevPagepointerbeNULL,andthoseontheright-handsidewillhavethem_nextPagebeNULL.
Inaheap,orifanindexonlyhasasinglepage,thesepointerswillbothbeNULLforallpages.
pminlen
Thisisthesizeofthefixed-lengthportionoftherecordsonthepage.
m_slotCnt
Thisisthecountofrecordsonthepage.
m_freeCnt
Thisisthenumberofbytesoffreespaceinthepage.
m_freeData
Thisistheoffsetfromthestartofthepagetothefirstbyteaftertheendofthelastrecordonthepage.Itdoesn'tmatterifthereisfreespacenearertothestartofthepage.
m_reservedCnt
Thisisthenumberofbytesoffreespacethathasbeenreservedbyactivetransactionsthatfreedupspaceonthepage.Itpreventsthefreespacefrombeingusedupandallowsthetransactionstoroll-backcorrectly.There'saverycomplicatedalgorithmforchangingthisvalue.
m_lsn
ThisistheLogSequenceNumberofthelastlogrecordthatchangedthepage.
m_xactReserved
Thisistheamountthatwaslastaddedtothem_reservedCntfield.
m_xdesId
ThisistheinternalIDofthemostrecenttransactionthataddedtothem_reservedCntfield.
m_ghostRecCnt
Theisthecountofghostrecordsonthepage.
m_tornBits
Thisholdseitherthepagechecksumorthebitsthatweredisplacedbythetorn-pageprotectionbits-dependingonwhatformofpageprotectionisturndeonforthedatabase.