1

I'm getting integrity issues after I migrate a DB from SQL Server 2000 to SQL Server 2008 R2.

I ran a DBCC CHECKDB on the old db, and it returned no issues. Then I FTP-ed the backup file to the new server and restored it to a new database. I immediately ran a DBCC CHECKDB on the new DB and got the errors below. Is there something I'm doing wrong in the migration?

Could the FTP process be corrupting the file? (sizes were the same) Or, is there a way to fix these errors? (I tried various CHECKDB repair options and they didn't work).

Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=27147142,key_index_id=2) of row (object_id=1052582838) in sys.foreign_keys does not have a matching row (object_id=27147142,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=748581755,key_index_id=2) of row (object_id=828582040) in sys.foreign_keys does not have a matching row (object_id=748581755,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=748581755,key_index_id=2) of row (object_id=844582097) in sys.foreign_keys does not have a matching row (object_id=748581755,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=506133244) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=522133301) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=1010154694) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=1042154808) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=914154352,key_index_id=2) of row (object_id=1723921263) in sys.foreign_keys does not have a matching row (object_id=914154352,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=964914509,key_index_id=2) of row (object_id=686989874) in sys.foreign_keys does not have a matching row (object_id=964914509,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=1778821399,key_index_id=2) of row (object_id=1084582952) in sys.foreign_keys does not have a matching row (object_id=1778821399,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=1904061869,key_index_id=2) of row (object_id=1936061983) in sys.foreign_keys does not have a matching row (object_id=1904061869,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=2092586543,key_index_id=2) of row (object_id=73103351) in sys.foreign_keys does not have a matching row (object_id=2092586543,index_id=2) in sys.indexes. Msg 8992, Level 16, State 1, Line 1 Check Catalog Msg 3853, State 1: Attribute (referenced_object_id=2092586543,key_index_id=2) of row (object_id=1739921320) in sys.foreign_keys does not have a matching row (object_id=2092586543,index_id=2) in sys.indexes. CHECKDB found 0 allocation errors and 13 consistency errors not associated with any single object. Msg 2508, Level 16, State 3, Line 1 The In-row data RSVD page count for object "tblUnit_bak2", index ID 0, partition ID 32571823816704, alloc unit ID 32571823816704 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. CHECKDB found 0 allocation errors and 1 consistency errors in table 'tblUnit_bak2' (object ID 497006589). CHECKDB found 0 allocation errors and 14 consistency errors in database 'XXXXXX'.

Improve this question
2
  • did you remember to set the compatibility mode on the database? Restore leaves it at the 2000 level.
    – Tony Hopkinson
    Commented Dec 7, 2011 at 23:03
  • Changed it and it didn't help...
    – jbd
    Commented Dec 8, 2011 at 0:32

2 Answers 2

Reset to default
1

This is a known problem: orphaned entries

Run DBCC CHECKCATALOG on the SQL Server 2000 (not included in DBCC CHECKDB until later versions) and fix there. It's easier to fix system table manually on SQL Server 2000: way more difficult on SQL Server 2005+

Improve this answer
2
  • Ok. I can now see the errors on the 2000 server. Basically a bunch of objects that do "not match between 'SYSREFERENCES' and 'SYSINDEXES'." Can you point me toward instructions on how to fix these? Googling let me down...
    – jbd
    Commented Dec 8, 2011 at 0:34
  • @jbd: check this first: support.microsoft.com/kb/935950 It is a late hotfix for SQL Server 2000
    – gbn
    Commented Dec 8, 2011 at 19:10
0

Looks like the foreign keys may have been added to the source database with the NOCHECK option when they were created. Therefore there may be orphaned rows in the source database. To preclude this, drop and recreate the foreign keys on the old sql server without the NOCHECK option - if it throws an error, that's your problem.

When the database is upgraded as part of the restore process on the new server, the attempt to recreate these FKs fails due to violation

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.