Restoring tables from a full backup

Question

Post reply

Restoring tables from a full backup

eseosaoregie

SSCertifiable

Points: 6889
More actions
December 3, 2008 at 8:29 am

#124712

I need to restore 2 tables from for a database for me to able to run some necessary queries for work. The IT engineers have said they can restore a full backup from sunday to a different location and then I can import the tables back into the the database. Would I use DTS to import the tables back into the database? What do i do with the current tables in the database now. Would they be overwritten if the previous copies are imported into the database? The database is set to simple reocvery mode. Will that have any impact on this process.

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

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

Jerry Hung SSChampion Points: 12968 More actions · Answer 1

For Restore

- there is no object level recovery in SQL natively (3rd party tools yes)

- restore as a new DB, and copy the tables manually

As for data differences, you have to figure out how and WHAT to copy

INSERT INTO dbA.table1

SELECT * FROM dbB.table1

WHERE NOT EXISTS IN dbA.table1 -- idea1

or simply drop table1 and copy the entire table over, etc...

There are 3rd party tools like Red Gate Data Compare to compare data between objects for this reason

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Third party tools make this easy, and if you do it much, or you have issues, they might be worth looking at.

DTS, the wizard, might be the easiest way to do this. Watch out for the drop part of the DTS wizard. You might need to just copy data (and possibly truncate the destination tables first).

If you need to specify specific rows, like with a WHERE clause, then you might want to just use a custom DTS package. Set up a query and enter that in the transform data task.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

eseosaoregie (12/3/2008)
I need to restore 2 tables from for a database for me to able to run some necessary queries for work. The IT engineers have said they can restore a full backup from sunday to a different location and then I can import the tables back into the the database. Would I use DTS to import the tables back into the database? What do i do with the current tables in the database now. Would they be overwritten if the previous copies are imported into the database? The database is set to simple reocvery mode. Will that have any impact on this process.

That's up to you. Do you want to overwrite the current data ?? Or do you want the old data restored under a different name so you have both the current data and the old data from the restore ?

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

You can do this multiple ways.

You can insert all the data from the restored tables, which may result in some duplicates if you don't have constraints to overwrite this.

You can update data in the existing table from the restored tables, and insert data that is in the restored table and not in the new one, but you could be putting in old data.

I'd recommend you run some SELECT queries that compare the old and new and determine what might need to move. If you have questions about specific moves, ask again.