Accidentially deleted rows in one table on one MS SQL database

Posted on 2011-10-19
Last Modified: 2012-05-12
We accidentally deleted 10,000 valid rows from one table in our MS SQL 2008 database.  We have a current Backup Exec backup.  How can I restore these rows to the one table?  I can't do a full restore of the entire database because there has been activity on to other tables in the database today.  Note no new rows have been added to the table that had the accidental rows deleted for the last week.

Thanks In Advance
Question by:chasmx1
    LVL 12

    Expert Comment

    If your backup is taken through Litespeed then it has got options to support restore of single table but I am afraid SQL Server intrinsic backup feature does not provide this option as per my knowledge. The only solution left for you is to restore the whole db in some other SQL instance if possible and link that with your current SQL instance through linked server and pull all lost records from desired table.

    Author Comment

    We used Symantec Backup Exec 2010 with the SQL Agent.
    LVL 12

    Expert Comment

    I have personally no idea about Symantec Backup Exec 2010 but following link suggests that with this also its not possible to restore a single table.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I agree with above: you have to restore the full database, and "recover" the table data from there.
    LVL 10

    Accepted Solution

    i suggest you to restore the old db with diffrent name on the same server , use some where condotion to extract missing rows from the old db
    LVL 2

    Expert Comment

    Hi. Before all operations BACKUP live database and test the scenario on testing environment.

    Here is my solution:
    - Restore backup to new database named "restore" etc.
    - Use "Export data..." from restored database to live database

    Inside the "Export data..." wizard use this steps:
    - Select restored database as "Data Source" and live database as "Destination"
    - Select "Write a query to specify the data to transfer"
    - Write query to SELECT all deleted rows in the table
    - Set "Destination" to table where data was deleted, click "Edit Mappings..." and select "Enable identity insert"
    - "Run immediately" -> Finish
    - Check "Success" and check if deleted rows is now in the live database

    When all looks fine, use the same scenario on really live database.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
    This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
    This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now