Suspect Database Causing Primary Key Error!
-
Hello - One of my customers ran defrag on his server running SQL 2000 which then caused one of their databases to become "suspect".....I was able to detach the log files and reattach them to another server / database but now I'm getting errors throughout the application when trying to write to the database. The error says there are duplicate primary keys in tables, which is actually not the case.
As a test, I created a new table and inserted the contents of one of the tables giving me the error. I deleted the contents of the original table and re-inserted the data and it corrected the error. Do I need to do that for every table in the database??
-
Run the DBCC CHECKDB first before doing anything to see what kind of corruption you have and how deep it is...
MohammedU
Microsoft SQL Server MVP -
Thanks for the reply MohammedU. I ran DBCC CheckDB ([databasename],repair_rebuild) right off the bat and it didn't give me any errors. I'm now in the process of creating a DTS package to rip the data out of the corrupt database and insert it into a clean database. It's extremely time consuming though because there are over 300 tables and in order to maintain referential integrity, I need to wipe out all of the tables in the clean database....all look up tables, etc. so if anyone can think of another way to correct the problem, I would greatly appreciate it!
-
How come checkdb returns no errors when you said you can't access the data and has corruption...
Only best option would be either bcp or dts...
MohammedU
Microsoft SQL Server MVP -
The database was in suspect mode so I put it in emergency mode to detach the logs as I was unable to backup the database in suspect mode. When reattaching the logs, everything appears fine until I use my application to make a change to the data and that's when I get the primary key violation error when in fact, there aren't any duplicates in the table it gives me the error for. I could fix just that one table except it's happening to many of the tables any time I try to make an update. DTS would be fine except the database has many customizable look up tables and 300 + tables so I'd have to drop all constraints on the destination database in order to import the original keys so it looks like I'm out of luck
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply