1

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 management makes me blind!

Thanks in advance for any help!

Improve this question
2
  • What editions of SQL 2008, standard, express etc.? Have you looked at replication or linked servers Commented Aug 26, 2011 at 14:56
  • Express for development and I think standard for production one, I think I will import all production data in my dev database and then I'll make a full backup of my database so I can then import it in production server. Doing this I will just lose data that will be written in production server in the meantime. Commented Aug 30, 2011 at 7:16

3 Answers 3

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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).

Improve this answer

Comments

0

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

Improve this answer

1 Comment

I know but I was wondering if there's a better method to do so. Writing some code to import data in a modern and complex database as SQL Server is not so comfortable and funny.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.