How to determine version of Local Sql Instance and your database

How to determine version of Local Sql Instance and your database

  • Comments 2

 

 

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.

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

 

 

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

http://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. :)

Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post
  • Is there a way of moving a db from 706 to 661?

  • now how we should upgrade our database server???

Page 1 of 1 (2 items)