Post reply

300+ records deleted 2 days ago

  • February 27, 2009 at 6:18 pm

    #218778

    Hello,

    I just found out today that 300+ records where deleted about 2 days ago. I have backups. What's the easiest way to recover the records and not disturb all of the new data since the delete occurred?

    Thanks

  • February 27, 2009 at 6:50 pm

    #951459

    I'm assuming 's one specific table right?

    restore the backup under a different name... once it'se you can investigate what data is missing, and check other tables for missign data as well.

    do a cross database query to find the records that are missing:lame example:

    SELECT * BACKUP.DBO.YOURTABLE BK

    LEFT OUTER JOIN PRODUCTION.dBO.YOURTABLE PD ON BK.YOURTABLEPK = PD.YOURTABLEPK

    WHERE PD.PK IS NULL --finds the records not existing in Production

    --example to insert the data back into production

    SET XACT_ABORT ON

    BEGIN TRAN

    SET IDENTITY_INSERT YOURTABLE ON

    INSERT INTO YORTABLE

    SELECT BK.* BACKUP.DBO.YOURTABLE BK

    LEFT OUTER JOIN PRODUCTION.dBO.YOURTABLE PD ON BK.YOURTABLEPK = PD.YOURTABLEPK

    WHERE PD.PK IS NULL --finds the records not existing in Production

    SET IDENTITY_INSERT YOURTABLE OFF

    --ROLLBACK TRAN

    --COMMIT TRAN

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply