BCP Corrupts Table Index by Inserting Duplicate Rows
BCP is the SQL Server utility that you can use to copy bulk data between a data file and instance of MS SQL Server. You can also use it to import/export data in/from SQL Server tables. But sometimes when you perform a bulk copy operation, it corrupts index pages of unique nonclustered index. It occurs when this operation results into duplicate rows insertion into a table. In such situations, you might receive an error message that the table is corrupt. Thus, to restore the missing information, you should use your last backup. It might occur that you don’t have such backup that could give required results. To cope up such problems, you can use powerful SQL recovery software.
When you run DBCC CHECKTABLE command to check SQL table consistency, you might receive below errors:
Server: Msg 8951, Level 16, State 1, Line 0
Table Corrupt: Table ‘table_name’ (ID 1797581442). Missing or invalid key in index ‘IDX_2’ (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 0
Data row (1:97:0) identified by (RID = (1:97:0) ) has index values (Col1 = 1 and Col2 = 1 and Col3 = 12 and Col4 = Sep 30 2000 12:00AM!t©~0WeG).
Cause
The most probable cause of such error messages to occur is that you have used bulk copy operation that has corrupted index pages of unique no clustered index. It occurs when bulk copy operation or DTS (Data Transformation Services) import package inserts duplicate rows into a table, when any of the following condition exists:
– The input file you are using contains duplicate rows
– The table to be targeted doesn’t contain any clustered index and contains a unique no clustered index that has been created with ignore_dup_key option
– You have turned on the Select Into/Bulk Copy option
Solution
Apply any of these methods to fix such issues:
– You should clean the target table by dropping no clustered index. You can then remove the duplicate rows and recreate the unique no clustered index
– If problem persists, you can use commercial .SQL Repair software to repair and restore table contents. Since using DBCC CHECKTABLE command with repair option cannot correct this issue, using SQL Recovery software is the only way lefts.
Stellar Phoenix SQL Recovery is an advanced tool to repair and restore damage SQL databases and its components. It supports safe SQL Repair for SQL Server 2008, 2005 and 2000. It can restore database tables, triggers, stored procedures, views, rules and other database components. It is compatible with Windows Vista, XP, 2003, 2000 and NT.