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.
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
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. :)
Comments
Anonymous
April 15, 2012
Is there a way of moving a db from 706 to 661?Anonymous
June 05, 2014
now how we should upgrade our database server???