How to determine the database version of an MDF fileApril 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 0×60 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 0×295 in hex. In the DBCC PAGE output this would show as 9502, and we can see that there are two such values at offset 0×64 and 0×66. So the database version is stored in the two bytes at offset 0×64 on the 9th page of the primary filegroup. The 9 page will be at offset 0×12000 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 0×12064 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 149 2 PS > 2*256+149 661
The first Powershell line dumped the two bytes at offset 0×12064 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.