August 11, 2009 at 9:41 am
I have a DB that is corrupt that my customer is still working with. Going thru backups I found that the last known good copy is from the end of June. I haven't found any problems with our tables/data but I always get errors when I do 'Select * From sys.objects'
The schema has not changed between the working and the current copy they are running with. I made a new DB from the working backup and deleted all entries in all of the tables and reset the identity columns back to 1. I then ran a the export/import data from SQL manager and set the mappings to allow for insert identity. All of the data copies over fine but my identities are all messed up (they reset starting at 2) and therefore FK references are no longer valid.
I have also tried SQL Data Compare (Red Gate) to sync the empty and current DB but it fails when trying to get the list of objects (It gets the same error as 'Select * From sys.objects')
Any ideas on how to copy(sync) all of the data from one DB to another knowing that the schema has not changed?
August 11, 2009 at 9:52 am
Can you run the following code on the corrputed database and post the results here. Someone will surely have a look at it and give you better ideas(if possible) than building database from scratch?
dbcc checkdb with all_errormsgs, no_infomsgs
August 11, 2009 at 10:00 am
Sorry, I forgot to mention that. I used "dbcc checkdb with all_errormsgs, no_infomsgs" to figure out that there were errors. Here is a subset.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3858, State 1: The attribute (type=) of row (object_id=0) in sys.objects has an invalid value.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=472049859) of row (class=0,object_id=472049859,column_id=0,referenced_major_id=121676127,referenced_minor_id=2) in sys.sql_dependencies does not have a matching row (object_id=472049859) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=472049859) of row (class=0,object_id=472049859,column_id=0,referenced_major_id=121676127,referenced_minor_id=3) in sys.sql_dependencies does not have a matching row (object_id=472049859) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=472049859) of row (class=0,object_id=472049859,column_id=0,referenced_major_id=121676127,referenced_minor_id=4) in sys.sql_dependencies does not have a matching row (object_id=472049859) in sys.objects.
....
CHECKDB found 1 allocation errors and 0 consistency errors in table 'LD1' (object ID 2146483817).
CHECKDB found 412 allocation errors and 25 consistency errors in database 'DB0805'.
Also when going into single user mode and running this with the repair allow for data loss, it said it fixed all of the errors but they still existed afterwards like it didn't fix anything
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply