How do I rescue a small portion of data from a SQL Server database backup?

Question

I have a live database that had some data deleted from it and I need that data back. I have a very recent copy of that database that has already been restored on another machine. Unrelated changes have been made to the live database since the backup, so I do not want to wipe out the live database with a full restore.

The data I need is small - just a dozen rows - but those dozen rows each have a couple rows from other tables with foreign keys to it, and those couple rows have god knows how many rows with foreign keys pointing to them, so it would be complicated to restore by hand.

Ideally I'd be able to tell the backup copy of the database to select the dozen rows I need, and the transitive closure of everything that they depend on, and everything that depends on them, and export just that data, which I can then import into the live database without touching anything else.

What's the best approach to take here? Thanks.

Everyone has mentioned sp_generate_inserts. When using this, how do you prevent Identity columns from messing everything up? Do you just turn IDENTITY INSERT on?

Answer 1

I've run into similar situations before, but found that doing it by hand worked the best for me.

I restored the backup to a second server and did my query to get the information that I needed, I then build a script to insert the data sp_generate_inserts and then repeated for each of my tables that had relational rows.

In total I only had about 10 master records with relational data in 2 other tables. It only took me about an hour to get everything back the way it was.

UPDATE To answer your question about sp_generate_inserts, as long as you specify @owner='dbo', it will set identity insert to ON and then set it to off at the end of the script for you.

Answer 2

you'll have to restore by hand. The sp_generate_inserts is good for new data. but to update data I do it this way:

SELECT 'Update YourTable '
    +'SET Column1='+COALESCE(''''+CONVERT(varchar,Column1Name)+'''','NULL')
    +', Column2='+COALESCE(''''+CONVERT(varchar,Column2Name)+'''','NULL')
    +' WHERE Key='+COALESCE(''''+CONVERT(varchar,KeyColumn)+'''','NULL') FROM backupserver.databasename.owner.YourTable

you could create inserts this way too, but sp_generate_inserts is better. Watch those identity values, and good luck (I've had this problem before and know where you're at right now).

useful queries:

--find out if there are missing rows, and which ones
SELECT
    b.key,c.key
    from backupserver.databasename.owner.YourTable b
        LEFT OUTER JOIN YourTable                  c ON b.key=c.key
    WHERE c.Key is NULL

--find differences
SELECT
    b.key,c.key
    from YourTable                                                c 
        LEFT OUTER JOIN backupserver.databasename.owner.YourTable b ON c.key=b.key
    WHERE  b.Key is not null
        AND (   ISNULL(c.column1,-9999)     != ISNULL(b.column1,-9999)
             OR ISNULL(c.column2,'~')       != ISNULL(b.column2,'~')
             OR ISNULL(c.column2,GETDATE()) != ISNULL(b.column2,GETDATE())
            )

Answer 3

SQL Server Management Studio for SQL Server 2008 allows you to export table data as insert statements. See http://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx. This approach lacks some of the flexibility of sp_generate_inserts (you cannot specify a WHERE clause to filter the rows in your table, for example) but may be more reliable since it is part of the product.