21

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?

6 Answers 6

8

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.

1
  • 1
    Note! This seem to be different on x64 editions of MS SQL. In all cases there are 4 blocks - SFMB, SSET, VOLB, MSCI. Version is in MSCI block. Problem is block size is not constant. Fortunately it seems block size can be divided by 512 (0x200). So just seek every 512 bytes and look for "MSCI". Then jump 172 bytes (0xAC) for lower byte and next byte for higher byte.
    – Nux
    Sep 8, 2016 at 11:27
22

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

2
  • 1
    I was going to try this, but I don't have any backup devices set up :-(. Thanks for the suggestion, though! Apr 4, 2011 at 19:55
  • 1
    Note that you will get no usable results from this query when you test e.g. SQL 2012 backup on SQL 2008.
    – Nux
    Jul 29, 2016 at 14:21
8

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.

1
  • 1
    you may want to add an explanation to your answer so that it makes more sense. Sep 19, 2013 at 10:25
0

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/

0

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/

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .