Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a local copy of a SQL Server DB stored as an MDF file. Is there a way to tell what version of SQL Server was used to create that file?

share|improve this question

6 Answers 6

up vote 3 down vote accepted

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.

share|improve this answer

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).

share|improve this answer
    
I was going to try this, but I don't have any backup devices set up :-(. Thanks for the suggestion, though! –  Ben McCormack Apr 4 '11 at 19:55
    
This worked for me. Thanks! –  Igor Jerosimić Dec 3 '12 at 21:01
dbcc checkprimaryfile ('c:\MyApp\AppData\foo.mdf', 2)

Sql MDF File version Product version Sql7-515
Sql2000-539 Sql2005sp1-611
Sql2005sp2-612
Sql2008sp1(dev10sp1)-655
sql2008sp2 Sql2008sp3
Sql2008r2-661
Sql2012(RC0)-705-Microsoft SQL Server 2012 RC0 - 11.0.1750.32 Sql2012(RC1/RTM)-706-Microsoft SQL Server 2012 RC0 - 11.0.1913.38

share|improve this answer
    
you may want to add an explanation to your answer so that it makes more sense. –  Drew Khoury Sep 19 '13 at 10:25

Good question! I don't believe so, apart from the trial-and-error process of - say - trying to restore a SQL Server 2008 R2 backup file onto SQL Server 2005. Obviously, that won't work. I can't remember off-hand whether using Management Studio - and clicking on the contents button for a restore - will show you anything interesting.

I haven't tried them, but it's possible that a third party tool such as Red Gate's Virtual Restore will tell you - it allows you to look at the database "inside" the backup file. http://www.red-gate.com/products/dba/sql-virtual-restore/

share|improve this answer

You can find this using the information in the boot page of the database. I wrote about this at http://sankarreddy.com/2010/05/database-internal-version-create-version-and-current-version/

share|improve this answer

Best way I've ever seen to do this was gleaned from this post on the SQL Server MSDN forums.

Basically it involves getting into the file and examining the boot page of the mdf file.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.