Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'.

share|improve this question
    
did you remember to set the compatibility mode on the database? Restore leaves it at the 2000 level. –  Tony Hopkinson Dec 7 '11 at 23:03
    
Changed it and it didn't help... –  jbd Dec 8 '11 at 0:32
add comment

migrated from stackoverflow.com Dec 7 '11 at 23:41

This question came from our site for professional and enthusiast programmers.

2 Answers

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+

share|improve this answer
    
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 Dec 8 '11 at 0:34
    
@jbd: check this first: support.microsoft.com/kb/935950 It is a late hotfix for SQL Server 2000 –  gbn Dec 8 '11 at 19:10
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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