Recovering lost data from SQL Server 2005

10 pts.
Tags:
INSERT statement
SQL Server 2005
SQL Server 2005 backup
SQL Server backup
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.
Brad Clawson


Software/Hardware used:
SQL Server 2005
ASKED: March 30, 2011  6:19 PM
UPDATED: April 2, 2011  7:08 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The only reason that the error would come up would be if SQL Server already had one or more of the records there.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BClawson
    [...] 7. Mrdenny reassured a member worried that he needed to recover lost data from SQL Server 2005. [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following