Moving data from one DB to another DB

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008

hello friends,
I want to generate script to move DB entities from one DB to another.

I have 3 different servers (development, test and live)

I will generate script on development server, this script should be common to all servers

I tried following

DB->Tasks->Generate Scripts..

It opens script generating wizard

In this wizard i select entities and select types of data to script option to
Schema and data

Problem is, all 3 server will have different data so running this script will copy wrong data

Any idea how to do it..?

Thanks in advance

Posted 15-Oct-12 20:31pm

dhage.prashant01

Add a Solution

Comments

AmitGajjar 16-Oct-12 2:33am

So do you want to synchronize database or just want to copy from source to destination and ignore conflicted one ?

dhage.prashant01 16-Oct-12 2:47am

I want to move the as it is table from one DB to another, including all constraints
Even i want to change the name of table, it should be prefix with RR_TableName

Any idea how to do it??

AmitGajjar 16-Oct-12 2:49am

If you have linked server then you can directly write select....insert statement to insert record in another table.

dhage.prashant01 16-Oct-12 2:59am

I have 100 tables, it will be tedious task
Any automation??

AmitGajjar 16-Oct-12 3:01am

see my answer, also let me know if mirroring is possible.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2012-10-15T20:35:00

Solution 1

Write your own stored procedure to to the job as you need and call that from the agent task.

Posted 15-Oct-12 20:35pm

Zoltán Zörgő

Comments

dhage.prashant01 16-Oct-12 2:59am

Any link for reference?

Zoltán Zörgő 16-Oct-12 3:27am

Any google? Reference for what: for how to make a stored procedure that transfers the data (http://msdn.microsoft.com/en-us/library/ms190923(v=sql.105).aspx) or how to make the job start a stored procedure (http://msdn.microsoft.com/en-us/library/ms187910.aspx)?

**AmitGajjar** · Answer 2 · 2012-10-15T21:01:00

Solution 2

Hi,

Check http://stackoverflow.com/questions/10028805/transfer-data-from-one-database-to-another-database[^] discussion. although i have not tested but you can try it.

Thanks.

Posted 15-Oct-12 21:01pm

AmitGajjar