25112
asked on
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)?
Where you got those numbers from?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Interesting. Never heard about those internal version numbers. Living and learning :)
Anyway, you can set their compatibility level to 90 (SQL Server 2005):
Anyway, you can set their compatibility level to 90 (SQL Server 2005):
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 90
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.
Hope this helps.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>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-."
https://www.experts-exchange.com/questions/28580407/RESTORE-situation.html
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-."
https://www.experts-exchange.com/questions/28580407/RESTORE-situation.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks PadawanDBA...
ASKER
>>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-d0 a281fb7154
DBINFO STRUCTURE: DBINFO @0x000000006E8BD7F0 dbi_maxLogSpaceUsed 5773926400
~~~
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-d0
DBINFO STRUCTURE: DBINFO @0x000000006E8BD7F0 dbi_maxLogSpaceUsed 5773926400
~~~
ASKER
>>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!?!
interestingly, we are out of 2000 instances.. 2005 is min now.. and we are dealing with a 7 db!?!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'OhnaT'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
ASKER
>>Must be created in 515 and migrated after.
that is what we are trying to do now, too, right?
that is what we are trying to do now, too, right?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You need to change the database compatibility to SQL 2005 before migrating it to SQL 2012.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.