merge tables from two backup files - SQL Server

Question

Hi,

I have two .bak files

These files contain different tables in them, and it is guaranteed that indexing is consistent across both files

I can easily restore database A from A.bak and have tables a1, a2, a3 and likewise table B from B.bak with tables b1, b2, b3

What I really want is to restore database AB from both A.bak and B.bak and have the resulting database contain table a1, a2, a3, b1, b2, b3

Is there a simple way of doing this?

Thanks!

Greg

Answer 1

To my knowledge, there's no means for combining the .bak files - you have to restore them into separate databases before you can merge the data into a single database.

Answer 2

I don't know of a way to restore them and merge them in one step, but you could create an agent job to temporarily restore, run a SSIS package to merge them, then drop them.

Answer 3

You could potentitally:

• restore database A
• use Red Gate's SQL Compare to join in the tables and other db objects from the database B backup file
• use Red Gate's [SQL Data Compare][2] to join in the data from the database B backup file

The Red Gate tools are pretty nifty that way since you can compare (and sync!) a live database against a standard SQL Server backup file (requires the Pro editions of the tools).