How to determine the database version of an MDF file

April 4th, 2011

If you have an MDF file laying around and you don’t know what version is, how can you determine the version, preferably without altering the file? You could try to attach it to a SQL Server instance, but if you’re not careful the operation may end up upgrading the MDF file to that instance’s current version. Even if it doesn’t upgrade it because it happens to be the same version as the one supported by that instance, it can still run recovery on the database and thus alter the file. Is there a way to determine the version in a non destructive fashion?

Lets look into a database and dump a page. Not any page, but page 9 of the primary filegroup. This page happens to be the database boot page:

dbcc traceon(3604)
dbcc page(1,1,9,3);

the important bits of information are these:

Memory Dump @0x00000000124FA060
0000000000000000:   0000a405 95029502 00000000 00000000 †..¤.•.•......... 
DBINFO @0x00000000124FA060
dbi_dbid = 1                         dbi_status = 65544                   dbi_nextid = 1723153184
dbi_dbname = master                  dbi_maxDbTimestamp = 4000            dbi_version = 661
dbi_createVersion = 661              dbi_ESVersion = 0                    

So at offset 0x60 on the page (96 decimal, which we know is the size of the page header) there is a DBINFO structure. This structure contains the dbi_version 661, which is 0x295 in hex. In the DBCC PAGE output this would show as 9502, and we can see that there are two such values at offset 0x64 and 0x66. So the database version is stored in the two bytes at offset 0x64 on the 9th page of the primary filegroup. The 9 page will be at offset 0x12000 in the file (just take the page size, 8k, and multiply it by 9). So the version of the MDF will be the DWORD value at offset 0x12064 in the file.

There are many ways you can read these bytes using your favourite hex file viewer/editor. Even Powershell can do it:

PS > get-content -Encoding Byte "...\foo.mdf" | select-object -skip 0x12064 -first 2
PS > 2*256+149

The first Powershell line dumped the two bytes at offset 0x12064 in the file: 149 and 2 (Powersell displays the value in decimal encoding…). Convert the two bytes to a DWORD gives us the MDF file version: 661, which is the SQL Server 2008 R2 version.

Comments are closed.