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:
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.
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 Version | Internal Database Version | Database Compatibility Level | Supported Database Compatibility Levels |
| SQL Server 2022 RTM | 957 | 160 | 160,150,140,130,120,000,000 |
| SQL Server 2022 RC 1 | 950 | 160 | 160,150,140,130,120,000,000 |
| SQL Server 2019 CTP 3.2 / RC 1 / RC 1.1 / RTM | 904 | 150 | 150,140,130,120,110,000 |
| SQL Server 2019 CTP 3.0 / 3.1 | 902 | 150 | 150,140,130,120,110,000 |
| SQL Server 2019 CTP 2.3 / 2.4 / 2.5 | 897 | 150 | 150,140,130,120,110,000 |
| SQL Server 2019 CTP 2.1 / 2.2 | 896 | 150 | 150,140,130,120,110,000 |
| SQL Server 2019 CTP 2.0 | 895 | 150 | 150,140,130,120,110,000 |
| SQL Server 2017 | 868 / 869 | 140 | 140,130,120,110,100 |
| SQL Server 2016 | 852 | 130 | 130,120,110,100 |
| SQL Server 2014 | 782 | 120 | 120,110,100 |
| SQL Server 2012 | 706 | 110 | 110,100,90 |
| SQL Server 2012 CTP1 (SQL Server 2011 Denali) | 684 | 110 | 110,100,90 |
| SQL Server 2008 R2 | 660 / 661 | 100 | 100,90,80 |
| SQL Server 2008 | 655 | 100 | 100,90,80 |
| SQL Server 2005 SP2+ | 612 | 90 | 90,80,70 |
| SQL Server 2005 | 611 | 90 | 90,80,70 |
| SQL Server 2000 | 539 | 80 | 80,70 |
| SQL Server 7.0 | 515 | 70 | 70 |
| SQL Server 6.5 | 408 | 65 | 65 |
| SQL Server 6.0 | 406 | 60 | 60 |
This website uses cookies.
