locked
Restoring a record from backup RRS feed

  • Question

  • I need to restore a record from backup but the record in the main DB has been deleted along with its primary key.

    Can I restore that same record (with its primary key kept in place) to the main DB from the backup DB ? I don't want to generate a new primary key, just add it to where it was before. Theoretically, filling in the hole. Is this possible and so how? Thanks.


    - Tackling one issue after another..
    Thursday, September 16, 2010 3:42 PM

Answers

  • Yes.  However you will have to do a little bit of work ...

    1) Restore the backup to another database (Source DB).

    2) Drop/Disable FK Constraints for the Table you want to restore information into (Destination DB).

    3) If your PK is Identity column, execute "SET IDENTITY_INSERT DBNAME.SCHEMA.TABLE ON" (Destination DB).

    4) INSERT INTO DestinationDB.SCHEMA.Table (FieldList,...) SELECT FieldList, ... FROM SourceDB.SCHEMA.Table (Destination DB).

    5) Execute "SET IDENTITY_INSERT DBName.SCHEMA.Table OFF" (Destination DB).

    6) Recreate/Enable FK Constraints for the Table affected in step 2 (Destination DB).

     

    1 Note: If you just want to restore the single table you must be using some kind of 3rd party tool as SQL Native does not give such functionality.

    2 Note: The data must exist in your backup, you cannot restore deleted record if it is not in backup (even if ghost record is still there).

    3 Note: If you have transaction log backup + full backup + differential of you database; you can do point-in-time restore to possibly get your data back.

     

    Thanks.

    Mohit.

    • Proposed as answer by Christa Kurschat Thursday, September 16, 2010 3:52 PM
    • Marked as answer by KJian_ Wednesday, September 22, 2010 7:11 AM
    Thursday, September 16, 2010 3:50 PM

All replies

  • Yes.  However you will have to do a little bit of work ...

    1) Restore the backup to another database (Source DB).

    2) Drop/Disable FK Constraints for the Table you want to restore information into (Destination DB).

    3) If your PK is Identity column, execute "SET IDENTITY_INSERT DBNAME.SCHEMA.TABLE ON" (Destination DB).

    4) INSERT INTO DestinationDB.SCHEMA.Table (FieldList,...) SELECT FieldList, ... FROM SourceDB.SCHEMA.Table (Destination DB).

    5) Execute "SET IDENTITY_INSERT DBName.SCHEMA.Table OFF" (Destination DB).

    6) Recreate/Enable FK Constraints for the Table affected in step 2 (Destination DB).

     

    1 Note: If you just want to restore the single table you must be using some kind of 3rd party tool as SQL Native does not give such functionality.

    2 Note: The data must exist in your backup, you cannot restore deleted record if it is not in backup (even if ghost record is still there).

    3 Note: If you have transaction log backup + full backup + differential of you database; you can do point-in-time restore to possibly get your data back.

     

    Thanks.

    Mohit.

    • Proposed as answer by Christa Kurschat Thursday, September 16, 2010 3:52 PM
    • Marked as answer by KJian_ Wednesday, September 22, 2010 7:11 AM
    Thursday, September 16, 2010 3:50 PM
  •  

    Hi,

    Restore database in different name and move required data from new database to original database using insert into sql script.

    For example, your original database name is MyTest and you need to restore backup file in SQL server with named MyTestTempDB. Now you can access data in MyTestTempDB. For eg, you have deleted a record in table1 and the old data is available in MyTestTempDB,

     

    SELECT * FROM MyTestTempDB..table1 -- You will get old data

     

    insert into MyTest..table1(id, name)

    select id, name from MyTestTempDB..table1 where id = 675 --- eg 675 is deleted record.

     


    Ramesh S
    www.sequalserver.com
    Thursday, September 16, 2010 3:53 PM