Click here to Skip to main content
13,400,969 members (37,592 online)
Rate this:
 
Please Sign up or sign in to vote.
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 21:31pm
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Write your own stored procedure to to the job as you need and call that from the agent task.
  Permalink  
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)?
Rate this: bad
 
good
Please Sign up or sign in to vote.

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.180218.2 | Last Updated 16 Oct 2012
Copyright © CodeProject, 1999-2018
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100