Is there a way to determine the version of SQL Server that was used to create an MDF or BAK file?

Solution 1:

Use RESTORE HEADERONLY, e.g.

RESTORE HEADERONLY FROM DISK = 'D:\whatever.bak'

You'll get a lot of columns, but the ones of interest are SoftwareVersionMajor, SoftwareVersionMinor, and SoftwareVersionBuild, which should give you the version number of SQL Server. On our system, for example, these are 10, 0, and 4000, meaning 10.0.4000 (2008 SP2).

Not sure what happens if you try to do this with a backup that's too old to be restored on the version the server is running, however - you might just get an error and no info (though that in itself would at least provide some clues on the version it's from).

Solution 2:

You can determine the version of the primary MDF file of a database by looking at the two bytes at offset 0x12064. See How to determine the database version of an MDF file.

In .bak files lower byte is 0xEAC and higher is 0xEAD.

You can find most internal database version numbers for MS SQL here.

---

Solution 3:

For MDF files try this command:

dbcc checkprimaryfile ('c:\MyApp\AppData\foo.mdf', 2)

It will output 3 properties with values: Database name, Database version and Collation.

The syntax is following (the command is undocumented, therefore more info here):

DBCC CHECKPRIMARYFILE ({'FileName'} [, opt = {0|1|2|3}])

FileName is nothing but the actual path of the SQL Server Database Primary Data File .mdf file.

Opt = 0 - Verifies whether the file is a SQL Server Database Primary Data file (.mdf).

Opt = 1 - Returns Database Name, Size, Max Size, Growth, Status and Path of all files associated to the database.

Opt = 2 - Returns Database Name, Version and Collation information.

Opt = 3 - Returns Name, Status and Path of all files associated to the database.