Merge two SQL Server databases

Hi I have this situation:

• SQL Server 1 (production) with obj A, B, C
• SQL Server 2 (development) with objects A, B, C, D, E, F, G, H, I.

I would like to know how I can hold every objects of SQL Server 1 (A,B,C) but at the same time I want to add every new objects from SQL Server 2. In SQL Server 2 A,B,C have same schema but fake data respect the same objects in SQL Server 1 so I can't make a full backup of SQL Server 2 and restore it in SQL Server 1.

Both server are running SQL Server 2008.

I know there obviously a smart procedure to do this but my small experience in SQL Server man开发者_如何学Cagement makes me blind!

Thanks in advance for any help!

---

You need some good tools! Like:

• Red-Gate SQL Compare helps you compare and sync the structure of your databases (e.g. database objects like tables, views, procs, constraints etc.)

• Red-Gate SQL Data Compare helps you compare and sync your data in your tables, e.g. in your lookup and system tables or even your actual data tables

---

You could use different schemas, e.g. on both databases do:

CREATE SCHEMA something;
GO

Then you can move the "duplicate" objects in that schema on one side:

ALTER SCHEMA something TRANSFER dbo.A;
...

Then you can just move the something.objects over to the other database using tools like SQL Compare etc. You'll have two objects called A, but they'll be in different schemas. Your code will have to reference the correct schema, naturally.

But why do they have to be in the same database? You can keep them as separate databases and use synonyms in one to point to the objects in the other, so everything "looks" local. How do you plan to query from A if they're both in the same schema and in the same database?

Could you explain exactly what you are trying to achieve? Are you trying to make your production database backward compatible, so that you can push your dev changes without breaking existing apps? I think it's much cleaner to just point your testing app at a different database, and then bring the production database up to date completely (not like Frankenstein).

---

You can make an easy PHP script to run.

This script schould connect to the first data and import the data into the second one