May 7, 2010 at 6:17 am
Hi,
I need to determine the sql server version from the .mdf file. Something similar to RESTORE HEADERONLY command which gives information of the backup file (.BAK). I don't want to use any S/W like alcohol etc.
Will be deeply appreciated. Thank you.
May 7, 2010 at 6:37 am
What would be the point? You could only even attempt this for a detatched DB.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
May 7, 2010 at 6:41 am
You could try opening it in a hex editor and manually cracking the value out of the database header page (9th page of 8kb each) and getting out the database version. The DB version isn't the SQL version, for eg a SQL 2008 DB is version 655.
Good luck, you'll need it. The page layout is not documented anywhere and you'll have to work out where the various values are stored and convert them from binary yourself.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 6:42 am
i don't believe this is possible;
i was under the impression that the mdf is a standard file format for each version(2000/05/08)...meaning that all versions of SQL save to the same format for the mdf and .bak files you'd create...so they can be restored on any other version of SQL. noting specific to the server that is creating/using it in the mdf(unless you are talking the master database)
kind of like Office 2007 being able to save to a Word 97-2003 file format...you wouldn't know if it was saved by which version of Office(preimum,SmallOffice,Basic,Standalone Word, etc) that touched the file, jsut that it was in the expected format.
Lowell
May 7, 2010 at 6:45 am
GregoryF (5/7/2010)
What would be the point? You could only even attempt this for a detatched DB.
Likely that's exactly the point, to tell what version a detached DB came from so that you know what version to attach it to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 6:47 am
Lowell (5/7/2010)
i was under the impression that the mdf is a standard file format for each version(2000/05/08)...meaning that all versions of SQL save to the same format for the mdf and .bak files you'd create...so they can be restored on any other version of SQL.
The formats are different. That's why a SQL 2008 database cannot be attached to SQL 2005. The format is different and the lower version doesn't know how to handle it.
Sure, they're all based on 8kb pages with headers, but there are differences to the file structure for all versions of SQL. Higher database engines can (within reason) understand the lower formats, but not vis versa.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 6:51 am
GilaMonster (5/7/2010)
GregoryF (5/7/2010)
What would be the point? You could only even attempt this for a detatched DB.Likely that's exactly the point, to tell what version a detached DB came from so that you know what version to attach it to.
In which case, just attach it to 2008 and see what compatibility mode it is, if you don't have 2008 in your enterprise, then attach to 2005.
But in either case, I've never really encountered many MDF and LDF just laying around detached. And if they were, they would likely easily attach to what version of SQL is running on the server that they are residing on.
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
May 7, 2010 at 6:51 am
i mistook the requirement to be info in @@Version, to see if it was Express/Workgroup/Standard/Enterprise/Developer that had been using the file.
Lowell
May 7, 2010 at 6:58 am
GregoryF (5/7/2010)
In which case, just attach it to 2008 and see what compatibility mode it is, if you don't have 2008 in your enterprise, then attach to 2005.
Problem with that is if you do so and find that it was a 2000 or 2005 database, it can no longer be placed onto those versions. Once attached to SQL 2008, it's a 2008 database.
If it's important not to change the DB version and you have access to multiple versions, what you can do is try and attach it to the lowest version you have, if it fails move up the versions. The first one it will attach to is the version that the database was.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2010 at 7:04 am
GilaMonster (5/7/2010)
GregoryF (5/7/2010)
Likely that's exactly the point, to tell what version a detached DB came from so that you know what version to attach it to.Problem with that is if you do so and find that it was a 2000 or 2005 database, it can no longer be placed onto those versions. Once attached to SQL 2008, it's a 2008 database.
If it's important not to change the DB version and you have access to multiple versions, what you can do is try and attach it to the lowest version you have, if it fails move up the versions. The first one it will attach to is the version that the database was.
Is that new to 2008? I know in 2005 the compatibility is left at the original. I just thought 2008 limited attaches to 200 and later
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
May 7, 2010 at 7:13 am
GregoryF (5/7/2010)
Is that new to 2008? I know in 2005 the compatibility is left at the original. I just thought 2008 limited attaches to 200 and later
I'm not talking about compatibility level. I'm talking about database version.
Take a SQL 2005 database (compat mode 90). Detach it and attach it to SQL 2008. It now becomes a SQL 2008 database (database version 655) with compatibility level set to 90.
The compat mode means that some T-SQL constructs are interpreted as they were on SQL 2005, nothing more. The database version shows what version the file format and system tables are in, and the lowest version that the DB can then be attached/restored to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply