Thursday, June 5, 2008

Unraveling MS SQL 2000 Database Format (Part 1)

This time, I would like to discuss about the physical format of Microsoft's SQL 2000 Database. As you probably already know, each MS SQL 2000 database corresponds to one or more file. The file has the default extention of .MDF.

The raw data of MDF file is logically divided by blocks of size 8192 bytes and it is called page. In each block, the first 96 bytes is the header, and the rest of it, is the data, depends of the page type that is defined in the page header.

Now, let's see the first block, which corresponds to the page 0 (zero) in some sample physical SQL 2000 database, using hex editor program :

Offset 0 1 2 3 4 5 6 7 8 9 A B C D E F
00000000 01 0F 00 00 08 01 00 00 00 00 00 00 00 00 00 00
00000010 00 00 00 00 00 00 01 00 63 00 00 00 01 1F DE 18 c Þ
00000020 00 00 00 00 01 00 00 00 67 00 00 00 78 01 00 00 g x
00000030 13 00 00 00 00 00 00 00 00 00 00 00 42 D2 00 00 BÒ
00000040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00000050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00


Please by reminded that I will not explain further about each variable in each bytes (for instance, I will not explain what is m_type, etc.). You can consult other documentation regarding these property.

Offset 0 which is Hex 01 is headerVersion
Offset 1 which is Hex 0F is Page Type (m_type)
Offset 2 which is Hex 00 is m_typeFlagBits
Offset 3 which is Hex 00 is m_level
Offset 4 which is Hex 08 is m_flagBits
Offset 5 which is Hex 01 is at the time of writing is still not yet identified
Offset 6-7 which is Hex 00 00 is m_indexId
Offset 18-1B which is Hex 63 00 00 00 which is 00000063 Hex which is 99 Dec is m_objId
Offset 20-23 which is Hex 00 00 00 00 is second part of m_PageId
Offset 24-25 which is Hex 01 00 is first part of m_PageId or m_FileId

If time permitted, I will continue with next deciphering task. That's all folks !

2 comments:

Krzysztof Kluz said...

The fifth offset, the one that you couldn't identify, is a part of m_flagBits. This flag just covers 2 bytes. I know that this post is quite old but maybe some other people may find this information useful (but what for? ;) )

daspeac said...

I have heard about another way of file pdf corrupt repair. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.