If you have ever performed a restore of an old SQL database you may have found yourself trying to figure out what version of SQL Server it was running on.

This is because you cannot directly attach an .MDF file:

  1. from an older version of SQL Server to a newer version. The internal structure and features of the database engine may differ significantly between versions, making direct compatibility impossible
  2. from a newer version of SQL Server because .MDF’s, .NDF’s and .LDF’s are not backward compatible

In our case the SQL server that was in use had been shut down and scrapped years ago, but the auditors wanted (i.e. insisted) on having the data.

Fortunately, it is not that hard to figure out what version of SQL server an .MDF came from.

Copy the .MDF to C:\TEMP and rename it to 1.MDF then run this command:

get-content -Encoding Byte "1.mdf" | select-object -skip 0x12064 -first 2

Then multiply the second number by 256 and add it to the first number. In the example below that results in 2 x 256 +194 which equals 706. That is the SQL Server “Internal Database Version”, which you can see in the table below, translates to SQL 2012.

how to determine sql server version from and MDF file using powershell

Of course you do not NEED to rename the MDF or move it to C:\TEMP, but that makes it easy to use the PowerShell script.

Also, note that we did not come up with this formula. We got the idea from HERE and HERE, which explains that SQL .MDF’s store their database version number in offset 0x12064 which you can find using a HEX editor. Note that we like using HxD (free, easy, and contains no junk) and did actually open the .MDF with it to find that those to entries at offset 0x12064 were HEX C2 and 01 which translate to integers 194 and 2, just like we showed in the PowerShell screenshot above.

SQL Server VersionInternal Database VersionDatabase Compatibility LevelSupported Database Compatibility Levels
SQL Server 2022 RTM957160160,150,140,130,120,000,000
SQL Server 2022 RC 1950160160,150,140,130,120,000,000
SQL Server 2019 CTP 3.2 / RC 1 / RC 1.1 / RTM904150150,140,130,120,110,000
SQL Server 2019 CTP 3.0 / 3.1902150150,140,130,120,110,000
SQL Server 2019 CTP 2.3 / 2.4 / 2.5897150150,140,130,120,110,000
SQL Server 2019 CTP 2.1 / 2.2896150150,140,130,120,110,000
SQL Server 2019 CTP 2.0895150150,140,130,120,110,000
SQL Server 2017868 / 869140140,130,120,110,100
SQL Server 2016852130130,120,110,100
SQL Server 2014782120120,110,100
SQL Server 2012706110110,100,90
SQL Server 2012 CTP1 (SQL Server 2011 Denali)684110110,100,90
SQL Server 2008 R2660 / 661100100,90,80
SQL Server 2008655100100,90,80
SQL Server 2005 SP2+6129090,80,70
SQL Server 20056119090,80,70
SQL Server 20005398080,70
SQL Server 7.05157070
SQL Server 6.54086565
SQL Server 6.04066060


Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *