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

发布时间：2020-12-12 16:01:29 所属栏目：MsSql教程来源：网络整理

导读：先执行一下以下SQL语句，我的测试环境为 SQL2005 dbcc ?traceon( 3604 ) ??? go dbcc ?page(master, 1 , 0 , 2 ) 可以看到MDF文件的一些物理结构信息，其中包括重要的头96个字节。也就是第一个页面的文件头。 ........ PAGE HEADER: Page @0x03FA0000 m_page

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

This?identifies?the?file?number?the?page?is?part?of?and?the?position?within?the?file.??(1:143)?means?page?143?in?file?1.

m_headerVersion

This?is?the?page?header?version.?Since?version?7.0?this?value?has?always?been?1.

m_typea

This?is?the?page?type.?The?values?you're?likely?to?see?are:

1?-?data?page.?This?holds?data?records?in?a?heap?or?clustered?index?leaf-level.

2?-?index?page.?This?holds?index?records?in?the?upper?levels?of?a?clustered?index?and?all?levels?of?non-clustered?indexes.

3?-?text?mix?page.?A?text?page?that?holds?small?chunks?of?LOB?values?plus?internal?parts?of?text?tree.?These?can?be?shared?between?LOB?values?in?the?same?partition?of?an?index?or?heap.

4?-?text?tree?page.?A?text?page?that?holds?large?chunks?of?LOB?values?from?a?single?column?value.

7?-?sort?page.?A?page?that?stores?intermediate?results?during?a?sort?operation.

8?-?GAM?page.?Holds?global?allocation?information?about?extents?in?a?GAM?interval?(every?data?file?is?split?into?4GB?chunks?-?the?number?of?extents?that?can?be?represented?in?a?bitmap?on?a?single?database?page).?Basically?whether?an?extent?is?allocated?or?not.?GAM?=?Global?Allocation?Map.?The?first?one?is?page?2?in?each?file.?More?on?these?in?a?later?post.

9?-?SGAM?page.?Holds?global?allocation?information?about?extents?in?a?GAM?interval.?Basically?whether?an?extent?is?available?for?allocating?mixed-pages.?SGAM?=?Shared?GAM.?the?first?one?is?page?3?in?each?file.?More?on?these?in?a?later?post.

10?-?IAM?page.?Holds?allocation?information?about?which?extents?within?a?GAM?interval?are?allocated?to?an?index?or?allocation?unit,?in?SQL?Server?2000?and?2005?respectively.?IAM?=?Index?Allocation?Map.?More?on?these?in?a?later?post.

11?-?PFS?page.?Holds?allocation?and?free?space?information?about?pages?within?a?PFS?interval?(every?data?file?is?also?split?into?approx?64MB?chunks?-?the?number?of?pages?that?can?be?represented?in?a?byte-map?on?a?single?database?page.?PFS?=?Page?Free?Space.?The?first?one?is?page?1?in?each?file.?More?on?these?in?a?later?post.

13?-?boot?page.?Holds?information?about?the?database.?There's?only?one?of?these?in?the?database.?It's?page?9?in?file?1.

15?-?file?header?page.?Holds?information?about?the?file.?There's?one?per?file?and?it's?page?0?in?the?file.

16?-?diff?map?page.?Holds?information?about?which?extents?in?a?GAM?interval?have?changed?since?the?last?full?or?differential?backup.?The?first?one?is?page?6?in?each?file.

17?-?ML?map?page.?Holds?information?about?which?extents?in?a?GAM?interval?have?changed?while?in?bulk-logged?mode?since?the?last?backup.?This?is?what?allows?you?to?switch?to?bulk-logged?mode?for?bulk-loads?and?index?rebuilds?without?worrying?about?breaking?a?backup?chain.?The?first?one?is?page?7?in?each?file.

m_typeFlagBits

This?is?mostly?unused.?For?data?and?index?pages?it?will?always?be?4.?For?all?other?pages?it?will?always?be?0?-?except?PFS?pages.?If?a?PFS?page?has?m_typeFlagBits?of?1,?that?means?that?at?least?one?of?the?pages?in?the?PFS?interval?mapped?by?the?PFS?page?has?at?least?one?ghost?record.

m_level

This?is?the?level?that?the?page?is?part?of?in?the?b-tree.

Levels?are?numbered?from?0?at?the?leaf-level?and?increase?to?the?single-page?root?level?(i.e.?the?top?of?the?b-tree).

In?SQL?Server?2000,?the?leaf?level?of?a?clustered?index?(with?data?pages)?was?level?0,?and?the?next?level?up?(with?index?pages)?was?also?level?0.?The?level?then?increased?to?the?root.?So?to?determine?whether?a?page?was?truly?at?the?leaf?level?in?SQL?Server?2000,?you?need?to?look?at?the?m_type?as?well?as?the?m_level.

For?all?page?types?apart?from?index?pages,?the?level?is?always?0.

m_flagBits

This?stores?a?number?of?different?flags?that?describe?the?page.?For?example,?0x200?means?that?the?page?has?a?page?checksum?on?it?(as?our?example?page?does)?and?0x100?means?the?page?has?torn-page?protection?on?it.

Some?bits?are?no?longer?used?in?SQL?Server?2005.

m_objId

m_indexId?

In?SQL?Server?2000,?these?identified?the?actual?relational?object?and?index?IDs?to?which?the?page?is?allocated.?In?SQL?Server?2005?this?is?no?longer?the?case.?The?allocation?metadata?totally?changed?so?these?instead?identify?what's?called?the?allocation?unit?that?the?page?belongs?to?(I'll?do?another?post?that?describes?these?later?today).

m_prevPage

m_nextPage

These?are?pointers?to?the?previous?and?next?pages?at?this?level?of?the?b-tree?and?store?6-byte?page?IDs.

The?pages?in?each?level?of?an?index?are?joined?in?a?doubly-linked?list?according?to?the?logical?order?(as?defined?by?the?index?keys)?of?the?index.?The?pointers?do?not?necessarily?point?to?the?immediately?adjacent?physical?pages?in?the?file?(because?of?fragmentation).

The?pages?on?the?left-hand?side?of?a?b-tree?level?will?have?the?m_prevPage?pointer?be?NULL,?and?those?on?the?right-hand?side?will?have?the?m_nextPage?be?NULL.

In?a?heap,?or?if?an?index?only?has?a?single?page,?these?pointers?will?both?be?NULL?for?all?pages.

pminlen

This?is?the?size?of?the?fixed-length?portion?of?the?records?on?the?page.

m_slotCnt

This?is?the?count?of?records?on?the?page.

m_freeCnt

This?is?the?number?of?bytes?of?free?space?in?the?page.

m_freeData

This?is?the?offset?from?the?start?of?the?page?to?the?first?byte?after?the?end?of?the?last?record?on?the?page.?It?doesn't?matter?if?there?is?free?space?nearer?to?the?start?of?the?page.

m_reservedCnt

This?is?the?number?of?bytes?of?free?space?that?has?been?reserved?by?active?transactions?that?freed?up?space?on?the?page.?It?prevents?the?free?space?from?being?used?up?and?allows?the?transactions?to?roll-back?correctly.?There's?a?very?complicated?algorithm?for?changing?this?value.

m_lsn

This?is?the?Log?Sequence?Number?of?the?last?log?record?that?changed?the?page.

m_xactReserved

This?is?the?amount?that?was?last?added?to?the?m_reservedCnt?field.

m_xdesId

This?is?the?internal?ID?of?the?most?recent?transaction?that?added?to?the?m_reservedCnt?field.

m_ghostRecCnt

The?is?the?count?of?ghost?records?on?the?page.

m_tornBits

This?holds?either?the?page?checksum?or?the?bits?that?were?displaced?by?the?torn-page?protection?bits?-?depending?on?what?form?of?page?protection?is?turnde?on?for?the?database.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

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

dbcc ?traceon( 3604 ) ??? go

dbcc ?page(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个字节。