Solved

up the Internal Database Version Number?

Posted on 2014-12-15
21
118 Views
Last Modified: 2014-12-22
if the DB's Internal Database Version Number is 515, is it possible to upgrade it to 539 or 611 on a SQL 2005 box with a command or GUI (without creating a new db)?
0
Comment
Question by:25112
  • 7
  • 6
  • 4
  • +2
21 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40500633
Where you got those numbers from?
0
 
LVL 7

Accepted Solution

by:
Ray earned 50 total points
ID: 40500649
These are internal database version, correct?

515 is SQL 7
539 is SQL 2000
611 is SQL 2005

In Microsoft SQL Server 2005, you can create a new database by restoring a database backup created using Microsoft SQL Server 7.0, Microsoft SQL Server 2000, or SQL Server 2005. However, backups of master, model and msdb that were created by using SQL Server 7.0 or SQL Server 2000 cannot be restored by SQL Server 2005. Also, SQL Server 7.0 log backups that contain create-index operations cannot be restored to SQL Server 2000 or SQL Server 2005.

http://technet.microsoft.com/en-US/library/ms190436(v=sql.90).aspx
0
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 200 total points
ID: 40500657
So the version of the database is written in the boot page for the database.  It indicates the version of the instance the db was most recently attached to.  I would give this article a read: http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/04/26/what-s-the-difference-between-database-version-and-database-compatibility-level.aspx

Edit: Oh yeah.  So the long and the short of it is: attach it to a more recent version of SQL Server and it will upgrade the database version to the instance version.  Be aware that that means you can't re-attach it to an earlier version of SQL Server once you do that (in fact, when you attach the data file, you get to see all the db version upgrades in the query results window).
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40500666
Interesting. Never heard about those internal version numbers. Living and learning :)
Anyway, you can set their compatibility level to 90 (SQL Server 2005):
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL =  90

Open in new window

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40500727
If your question is whether you could host an older compatibility database on newer binaries the answer is yes.  As Ray mentioned, simply restore/backup.  Then migrate all your logins/jobs.  Do not restore system databases.

Hope this helps.
0
 
LVL 5

Author Comment

by:25112
ID: 40501256
ok.. here is my situation..

the database Internal Version Number is 515, but compatibility is 2000. if I take a backup from it and restore in any other 2005 instance, it is fine; but if I take it to 2008R2, it fails. (not able to upgrade).

any tweaks available to update the version of the database directly? (in this case, 515 to 539) (I am hoping that may help it to go smoothly to 665.
http://weblogs.sqlteam.com/dang/archive/2011/07/17/internal-sql-server-database-version-numbers.aspx
0
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 200 total points
ID: 40501277
So the database version should be updated by simply restoring/attaching it to the 2005 instance.  What version of SQL Server is it currently on? (if it's on a SQL 2000/7 version, you would need to double hop it and restore to 2005, then back it up, and restore that to 2008r2)
0
 
LVL 5

Author Comment

by:25112
ID: 40502580
>>database version should be updated by simply restoring/attaching it to the 2005 instance
the database compatibility is 2000, but database Internal Version Number points to SQL 7. (it is sitting on a SQL 2005 instance). If I backup and restore to same or another 2005 instance, the compatibility & database Internal Version Number remain the same.

If I take the backup from 2005instance (with compatibility "2000" & "7" database Internal Version Number) and take it to 2008R2, the restore won't happen and gives the error "RESTORE could not start db;  RESTORE DATABASE' is terminating abnormally.  Possible Schema Corruption-."
http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/Q_28580407.html
0
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 200 total points
ID: 40502707
Due to the age of the internal database version, have you tried attaching/restoring it to a 2000 instance to see if you can get it to upgrade the db version? (reaching for straws here - if you can't get the db version to upgrade by restoring it somewhere, the dbcc checks are coming back fine, and you can't copy the schema out to a more recent version and  then import the data into that, then you may be looking at a support ticket with MS)
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 150 total points
ID: 40502745
the database Internal Version Number is 515, but compatibility is 2000. if I take a backup from it and restore in any other 2005 instance, it is fine; but if I take it to 2008R2, it fails. (not able to upgrade).
The minimum database version that you are allowed to migrate to a SQL Server 2008R2 is a SQL Server 2000 (internal version 539).

Since you already migrate the database from 7.0 to 2005, before you doing something else, run the following script before doing anything else:
USE DatabaseName
GO
DBCC CHECKDB WITH DATA_PURITY 

Open in new window

0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 5

Author Comment

by:25112
ID: 40509457
thanks PadawanDBA...
0
 
LVL 5

Author Comment

by:25112
ID: 40509462
>>The minimum database version that you are allowed to migrate to a SQL Server 2008R2 is a SQL Server 2000 (internal version 539).

can you pl get a reference for this..

in one of the sql 2012 instances, I ran

DBCC DBINFO (OhnaT) WITH TABLERESULTS
and I got this: (gone from 515 to 706.. does not make sense?!)
~~~
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_version      706
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_createVersion      515
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_SEVersion      0
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_dvSplitPoint      0:0:0 (0x00000000:00000000:0000)
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_dbbackupLSN      195419:18835:1 (0x0002fb5b:00004993:0001)
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_LastLogBackupTime      2014-12-18 12:00:19.180
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_nextseqnum      1900-01-01 00:00:00.000
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_status      0x00010000
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_crdate      2014-09-15 11:38:53.457
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_dbname      OhnaT
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_dbid      5
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_cmptlevel      110
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_masterfixups      0
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_maxDbTimestamp      619700
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_dbbackupLSN      195419:18835:1 (0x0002fb5b:00004993:0001)
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_RebuildLogs      0
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_differentialBaseLSN      195419:18961:12 (0x0002fb5b:00004a11:000c)
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_RestoreFlags      0x0040
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_checkptLSN      195419:18997:1 (0x0002fb5b:00004a35:0001)
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_dbccFlags      0
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_COWLastLSN      0:0:0 (0x00000000:00000000:0000)
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_DirtyPageLSN      195419:18997:1 (0x0002fb5b:00004a35:0001)
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_RecoveryFlags      0x00000000
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_lastxact      0x700cad9
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_collation      872468488
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_relstat      0x41000000
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_familyGUID      c8ddc4e3-43e6-47d0-accd-d0a281fb7154
DBINFO STRUCTURE:      DBINFO @0x000000006E8BD7F0      dbi_maxLogSpaceUsed      5773926400
~~~
0
 
LVL 5

Author Comment

by:25112
ID: 40509464
>>have you tried attaching/restoring it to a 2000 instance
interestingly, we are out of 2000 instances.. 2005 is min now.. and we are dealing with a 7 db!?!
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 150 total points
ID: 40509473
and I got this: (gone from 515 to 706.. does not make sense?!)
Must be created in 515 and migrated after.
Did you run the DBCC CHECKDB WITH DATA_PURITY ?
0
 
LVL 5

Author Comment

by:25112
ID: 40509917
CHECKDB with purity gives this:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'OhnaT'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
 
LVL 5

Author Comment

by:25112
ID: 40509919
>>Must be created in 515 and migrated after.
that is what we are trying to do now, too, right?
0
 
LVL 10

Assisted Solution

by:PadawanDBA
PadawanDBA earned 200 total points
ID: 40510177
So the results you are seeing from your dbcc command indicate that the database was migrated at least two times.  I think that's what Vitor was getting at.  You can't go straight to 2012 with a db that has a db version of 515, you would have to upgrade it to a supported version and then back it up and restore that new backup in 2012.  Which should work fine (I've done it migrating a SQL2005 db to SQL2014), it's the double hop I alluded to earlier.  Hopefully you have an msdn subscription, because I think your best bet really is going to be standing a sql2k instance real fast and try restoring there to see if the db version upgrades.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40512488
You need to change the database compatibility to SQL 2005 before migrating it to SQL 2012.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 40512526
<<You need to change the database compatibility to SQL 2005 before migrating it to SQL 2012.>>
A few words of caution on this..

Bouncing multiple versions to avoid cross version migration is an interesting idea in theory but I see big hazards in this.  The principle of backward compatibility on later versions has shown its limits especially on the client side so I would not go that way only for that reason.  Also, I would remind that there are underlying support issues regarding cross versionning: MS support may not support some deprecated features.

Some testing and validation from business side are mandatory on this.  

That said, I believe this is more a management issue than a technical issue.  This DBA should not be put in a position where he has to find hacks to host of a legacy system over 2 generations.  In my opinion, the OP should assess the cost and feasibility of migration to current version first and have a serious conversation with his management to make them aware of the consequences of trying to find a technical solution to this problem.  

The solution you suggest may work for a while but if for some reasons the system fails, the responsibility will fall on the DBA when it should not.

That is just my opinion but I may just be wrong about this.  Personally, I refuse to cope with management inability to budget cross versionning.  They often choose software without the DBA but they ask the DBA to take responsibility for the fact that the code is not ported or bad quality.

Hope this helps.
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 150 total points
ID: 40512575
@Racimo: Caution it's always a wise recommendation but in this case the only caution that the author can have is to perform a backup of the database before migrating and run the migration advisor. These are the best practice and should be performed in any migration, not only for this specific one.
It's  a technical issue because you can't migrate directly from MSSQL 2000 to MSSQL 2012, so it always need a middle migration step here (other MSSQL 2005 or 2008).
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 100 total points
ID: 40512962
<<These are the best practice and should be performed in any migration, not only for this specific one. >>
Agreed.  I am just trying to bring a new perspective on the subject that does not concern feasibility or technical aspect (except for client side problems).  Please do not take my comment as a contradiction of anything you have said.  If it was perceived in such a way, I apologize because it was not my intent.

<<It's  a technical issue because you can't migrate directly from MSSQL 2000 to MSSQL 2012>>
Fair enough.

My point is not about how but what is the best course of action to be suggested in a context.  We do not want the medicine (how) being worse than the disease (stay in current version) to due unanticipated problems related to cross version migration (especially when jumping 2 versions).  Sometime to best decision is to NOT migrate immediately. And since we don't know the criticality of the system I would first consider all angles on the problem.

I believe you have more than enough experience to see what I am referring to.  I believe the OP has now enough information to make his mind about the best course of action.

Regards
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
combine an MS SQL string in Idera DM 9 31
Sql query 34 19
Convert char to decimal in a SQL Server View 14 21
sql query Help 12 29
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now