Sql-server – DBCC CHECKDB errors migrating from SQL Server 2000 to SQL Server 2008R2

migrationsql-server-2000sql-server-2008-r2

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

Best Answer

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+