Running SQL Server 2005.
I found that I had lost about 2000 records the Assets table and more records in the associated tables Timeassets and AssetBillingrates. Each of the tables uses an Id field as the Primary Key.
I was able to identify the key records that had been lost and tried inserting them From a backup I had made back into the tables, using Set Identity_Insert to make that possible. The attempt yields this error: Violation of PRIMARY KEY constraint 'PK_timeasset'. Cannot insert duplicate key in object 'dbo.timeasset'.
Having made sure that the primary key records I was trying to insert into the table didn't actually exist I thought that the problem might be with the index for the PK. After rebuilding the index and trying the insert again the result is the same.
I'd like to understand what is happening.
We ended up making a copy of the current critical files to another database, restoring a previous backup and are now inserting the then current records into the older restored backup files.
SQL Server 2005
March 30, 2011 6:19 PM
April 2, 2011 7:08 AM