How to determine version of Local Sql Instance and your database

If you have seen this error you must have broken your head on figuring out what happened. Which sql product version does 655 maps to.? How do I know what sql version am I running

The database 'C:\USERS\Foo\APP_DATA\ASPNETDB.MDF' cannot be opened because it is version 655. This server supports version 612 and earlier. A downgrade path is not supported.

Could not open new database 'C:\USERS\Foo\APP_DATA\ASPNETDB.MDF'. CREATE DATABASE is aborted.

An attempt to attach an auto-named database for file C:\Users\Foo\App_Data\aspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

 How do I figure out what was the sql vesion I created my mdf file in. How do I figure out what will be the mdf file version which will be created by the current sql product version.

Following table lists the sql product version and the file version of the mdf file they correspond to.

How to get the file version of an mdf file without connecting via sql

Note: These methods do not upgrade your database

TSQL

In VS Connect to a sql instance(".\SQLEXPRESS" or "(localdb)\v11.0") as follows

T-SQL(Check the file version which will be generated by your sql instance)

Note that this is a T-SQL command, so it needs to be run on a SQL connection to an instance with at least read access to the MDF. The database does not need to be attached to the instance.

The following command will return the version of master. If this version of your mdf file is higher than the version of the primary MDF being checked (as here), the database will be upgraded when attached:

select DATABASEPROPERTYEX('master', 'version')

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

698

(1 row(s) affected)

T-SQL(Check the file version of your mdf)  

This method does not require you to attach the MDF to the instance (so your mdf file is not upgraded).

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

property                                                                                                                         value

-------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Database name                                                                                                                    foo

Database version                                                                                                                 695

Collation                                                                                                                        872468488

(3 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Without using TSQL

Disclaimer: I found this of this person blog. While this works for the most part, this method depends upon internals of mdf file format which are not guaranteed to work the same way and can change in the future

https://rusanu.com/2011/04/04/how-to-determine-the-database-version-of-an-mdf-file/

PS > get-content -Encoding Byte "foo.mdf" | select-object -skip 0x12064 -first 2
149
2
PS > 2*256+149
661

Thus by doing any one of the above you can figure out what is the version of the mdf file that you have and what is the sql product version it corresponds to. :)