Post reply

Backup Scenario

  • July 1, 2013 at 6:26 am

    #279786

    Hi all,

    I want to know the backup scenario for the accidental deletion of table in a database.

    1. we have every day full backups ( 1 AM)

    2. every 4 hour differential backups

    3. every 15 min t log backups.

    one of the table was accidentally deleted from database around 4.35 PM and the issue was reported to DBA team 1 hour later. so how to deal with this issue.

    we need all the data before and after the deletion of table for the database and we need to restore table as well.

  • July 1, 2013 at 6:33 am

    #1628679

    This question looks like homework, so I only give you an overview. That would be enough to solve your question.

    Restore, as a new database, the the most recent FULL (and DIFF, if applicable) backup prior to the time of deletion. Restore all LOGs in sequence between the time of the FULL (or DIFF) and the time of deletion. Restore the first LOG backup taken after the time of deletion, using the point-in-time option and specify the time of deletion (minus ~0,5 seconds). Copy the data of the deleted table from the restored database to the original database. Remove the restored database.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • July 1, 2013 at 6:55 am

    #1628688

    Thanks for your immediate reply..

  • July 1, 2013 at 7:13 am

    #1628692

    we have one more issue recently.. the table exists in database and the data before 5 hours is not available. As the table has all the data for last 5 hours. how can we solve this issue.

    here is my plan.

    like previous issue .. from the backups available we create a separate database and restore it to point in time before 5 hours of existing database. and by using the script database task we take the data from that table and

    insert into the existing table.

    am i right.

  • July 1, 2013 at 7:18 am

    #1628696

    You are correct. Keep in mind possible issues regarding IDENTITY columns and/or FOREIGN KEY relations.

    But basically you can use "INSERT INTO [target]..table SELECT ... FROM [source]..table" to get the data older then 5 hours back into the target/live database.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Viewing 5 posts - 1 through 4 (of 4 total)

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