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

2 Answers
2

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+

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...
–
jbdDec 8 '11 at 0:34

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