February 21, 2006 at 1:45 am
Hello comrades - I have an SQL Server 2000 developers edition and I need (god knows if actually need... maybe I would just like to) to upgrade it to SQL 2005 Express. My database contains ONLY tables and views - no specific users - no stored procedures - nothing else.
If just backup the development db from SQL 2000 to a file and try to restore it to SQL 2005 Express do you think it might work?
Automatic upgrading from 2000 developer edition to SQL 2005 express is not supported...
February 21, 2006 at 1:30 pm
You can restore a sql 2000 (or 7) database straight onto a sql 2005 server.
Watch the compatibility mode as I seem to remember it stays at the old level. Hence if you want to use the new features you will need to put the database in sql 2005 mode.
Steven
February 22, 2006 at 2:27 am
Moving from 2000 Developer edition to 2005 Express is a downgrade, that's why it's not supported.
February 22, 2006 at 7:07 am
As Steven pointed out, after the restore it is important to run the following command to set the compatibility mode to SQL Server 2005:
sp_dbcmptlevel 'DBNAME', 90
replace DBNAME with the name of the database
Norene Malaney
December 8, 2008 at 3:30 am
In more or less the same situation, I am trying to restore a backup of a database that was taken in SQL Server 2000 (Instance) onto SQL Server 2005. It appears the action completes, but I get a list of errors. I have ran a checkdb (on the safe side) on the entire database and it shows no errors. At first I thought there "was" problem with the backup but it tirned out that when I restored this onto a SQL Server 2000 instance, the restore completes and no errors reported. Any ideas?
I kind of remember reading somewhere that the "correct" way to do this is to copy the MDF from a SQL Server 2000 and ATTACH this to the SQL Server 2005 and dhange the compatibility to 90. Can anyone verify this please>?
Converting database 'MyDatabase' from version 539 to the current version 611.
Database 'MyDatabase' running the upgrade step from version 539 to version 551.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableA" (index_id = 2) on object ID 418100530 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableB" (index_id = 4) on object ID 418100530 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_fico_status_18EBB532" (index_id = 9) on object ID 418100530 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableC" (index_id = 2) on object ID 450100644 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableC" (index_id = 4) on object ID 450100644 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "cPK_tableD" (index_id = 5) on object ID 450100644 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableE" (index_id = 2) on object ID 466100701 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableF" (index_id = 4) on object ID 466100701 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_time_stamp_1BC821DD" (index_id = 6) on object ID 466100701 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableG" (index_id = 2) on object ID 485576768 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableH" (index_id = 4) on object ID 485576768 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableAI" (index_id = 2) on object ID 565577053 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableJ" (index_id = 4) on object ID 565577053 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Database 'MyDatabase' running the upgrade step from version 551 to version 552.
Database 'MyDatabase' running the upgrade step from version 552 to version 553.
Database 'MyDatabase' running the upgrade step from version 553 to version 554.
Database 'MyDatabase' running the upgrade step from version 554 to version 589.
Database 'MyDatabase' running the upgrade step from version 589 to version 590.
Database 'MyDatabase' running the upgrade step from version 590 to version 593.
Database 'MyDatabase' running the upgrade step from version 593 to version 597.
Database 'MyDatabase' running the upgrade step from version 597 to version 604.
Database 'MyDatabase' running the upgrade step from version 604 to version 605.
Database 'MyDatabase' running the upgrade step from version 605 to version 606.
Database 'MyDatabase' running the upgrade step from version 606 to version 607.
Database 'MyDatabase' running the upgrade step from version 607 to version 608.
Database 'MyDatabase' running the upgrade step from version 608 to version 609.
Database 'MyDatabase' running the upgrade step from version 609 to version 610.
Database 'MyDatabase' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 41009 pages in 26.289 seconds (12.778 MB/sec).
December 8, 2008 at 4:00 am
herbiechin (12/8/2008)
In more or less the same situation, I am trying to restore a backup of a database that was taken in SQL Server 2000 (Instance) onto SQL Server 2005. It appears the action completes, but I get a list of errors. I have ran a checkdb (on the safe side) on the entire database and it shows no errors. At first I thought there "was" problem with the backup but it tirned out that when I restored this onto a SQL Server 2000 instance, the restore completes and no errors reported. Any ideas?I kind of remember reading somewhere that the "correct" way to do this is to copy the MDF from a SQL Server 2000 and ATTACH this to the SQL Server 2005 and dhange the compatibility to 90. Can anyone verify this please>?
Converting database 'MyDatabase' from version 539 to the current version 611.
Database 'MyDatabase' running the upgrade step from version 539 to version 551.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableA" (index_id = 2) on object ID 418100530 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableB" (index_id = 4) on object ID 418100530 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_fico_status_18EBB532" (index_id = 9) on object ID 418100530 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableC" (index_id = 2) on object ID 450100644 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableC" (index_id = 4) on object ID 450100644 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "cPK_tableD" (index_id = 5) on object ID 450100644 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableE" (index_id = 2) on object ID 466100701 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableF" (index_id = 4) on object ID 466100701 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "_WA_Sys_time_stamp_1BC821DD" (index_id = 6) on object ID 466100701 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableG" (index_id = 2) on object ID 485576768 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableH" (index_id = 4) on object ID 485576768 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableAI" (index_id = 2) on object ID 565577053 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Warning: A column nullability inconsistency was detected in the metadata of index "PK_tableJ" (index_id = 4) on object ID 565577053 in database "MyDatabase". The index may be corrupt. Run DBCC CHECKTABLE to verify consistency.
Database 'MyDatabase' running the upgrade step from version 551 to version 552.
Database 'MyDatabase' running the upgrade step from version 552 to version 553.
Database 'MyDatabase' running the upgrade step from version 553 to version 554.
Database 'MyDatabase' running the upgrade step from version 554 to version 589.
Database 'MyDatabase' running the upgrade step from version 589 to version 590.
Database 'MyDatabase' running the upgrade step from version 590 to version 593.
Database 'MyDatabase' running the upgrade step from version 593 to version 597.
Database 'MyDatabase' running the upgrade step from version 597 to version 604.
Database 'MyDatabase' running the upgrade step from version 604 to version 605.
Database 'MyDatabase' running the upgrade step from version 605 to version 606.
Database 'MyDatabase' running the upgrade step from version 606 to version 607.
Database 'MyDatabase' running the upgrade step from version 607 to version 608.
Database 'MyDatabase' running the upgrade step from version 608 to version 609.
Database 'MyDatabase' running the upgrade step from version 609 to version 610.
Database 'MyDatabase' running the upgrade step from version 610 to version 611.
RESTORE DATABASE successfully processed 41009 pages in 26.289 seconds (12.778 MB/sec).
detach\attach
backup\restore
same difference
congratulations it looks as though you have system catalogue corruption,
have you ran DBCC CHECKTABLE against each of the affected tables?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 8, 2008 at 4:12 am
Hi Perry,
As I said in my last post, I have run a DBCC CHECKDB on the entire database (just in case). It came back clean.
December 8, 2008 at 4:16 am
herbiechin (12/8/2008)
It appears the action completes, but I get a list of errors. I have ran a checkdb (on the safe side) on the entire database and it shows no errors.
Did you run the checkDB on SQL 2000 or 2005? If on 2000, also run DBCC CheckCatalog as that was not included in CheckDB in SQL 2000.
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
December 8, 2008 at 4:23 am
GilaMonster (12/8/2008)
herbiechin (12/8/2008)
It appears the action completes, but I get a list of errors. I have ran a checkdb (on the safe side) on the entire database and it shows no errors.Did you run the checkDB on SQL 2000 or 2005? If on 2000, also run DBCC CheckCatalog as that was not included in CheckDB in SQL 2000.
I ran DBCC CHECKDB on both SQL 2000 and 2005 - both are clean. I have also taken your advice by running the DBCC CHECKCATALOG on the same backup being restored onto a SQL Server 2000 instance - it is celan too.
February 10, 2009 at 4:40 pm
i think some of your indexes are corrupted. if possible re-create the index using DBCC DBREINDEX ..
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply