Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

up the Internal Database Version Number?

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
25112
Asked:
25112
  • 7
  • 6
  • 4
  • +2
10 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Where you got those numbers from?
0
 
RayCommented:
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
 
PadawanDBACommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Racim BOUDJAKDJICommented:
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
 
25112Author Commented:
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
 
PadawanDBACommented:
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
 
25112Author Commented:
>>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
 
PadawanDBACommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
25112Author Commented:
thanks PadawanDBA...
0
 
25112Author Commented:
>>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
 
25112Author Commented:
>>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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
25112Author Commented:
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
 
25112Author Commented:
>>Must be created in 515 and migrated after.
that is what we are trying to do now, too, right?
0
 
PadawanDBACommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to change the database compatibility to SQL 2005 before migrating it to SQL 2012.
0
 
Racim BOUDJAKDJICommented:
<<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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
@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
 
Racim BOUDJAKDJICommented:
<<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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now