Merge 2 SQL Server databases

Question

I need to merge 2 SQL Server 2008 databases.

The scenario is as follows: I have a test server and a production server. They differ a little from structure. The test server has some extra tables. Now I need the structure of the test database on the production server, with the data from the production database.

What is the easiest way to do this?

Answer 1

It's not hard to do manually, once you know the list of tables that need changing. The best part about the third party tools is that they run a scan and show you all the differences, then you can right click right in the tool, generate the scripts that add the columns/tables, and run them, again, right in the tool.

I use AdeptSQL for this purpose.

Answer 2

Simply use a tool like Red-Gate SQL Compare to synchronize your databases. There are other tools on the market (some freeware) that do the same job, but Red-Gate have an excellent reputation within the community.

Answer 3

There's a Schema Compare tool in VS2012 if you are using that at all

Answer 4

If its just a handful of extra tables on the test database, just script them out manually (right click each table, Script Table As >> Create) then run the scripts on the prod server to create the tables.

As Kane mentioned, there are 3rd party automated tools that can generate scripts like this automatically, but its not so hard to do manually.