Database Duplication
-
SImple replication question. I am running MS-SQL 2005 on a couple of machines. How can I copy the data from one machine to another. The target machine has some of the data, but I would like to update the dataset with want is on another machine. Simple summary: Replicate the data from production to debug without using backup and restore. There are some tables on the debug machine that are in process of being worked on that have yet to be added to the production, so all I want is productions data to be copied. There are auto incremented keys that need to remain the same. Thanks,
Leo T. Smith Program/Analyst Supervisor
-
SImple replication question. I am running MS-SQL 2005 on a couple of machines. How can I copy the data from one machine to another. The target machine has some of the data, but I would like to update the dataset with want is on another machine. Simple summary: Replicate the data from production to debug without using backup and restore. There are some tables on the debug machine that are in process of being worked on that have yet to be added to the production, so all I want is productions data to be copied. There are auto incremented keys that need to remain the same. Thanks,
Leo T. Smith Program/Analyst Supervisor
There is no easy way to do this without writing some programming. Take these things into consideration: 1) What are the dependencies among the tables? This will determine the order of the tables to be processed. For example, Invoice Header is processed before Invoice Details. 2) Auto increment keys could be a killer for you. On production the key may be 1001, but in debug that key is already used. Therefore the program would have to look at the data as a “business object”, meaning it would have to re-create the Invoice Header with a new key and the subsequent invoice details would have to have the proper foreign key to point back to this new Invoice Header. 3) Get yourself a good ER (entity relationship) diagram of your system and start thinking about how you could pull data over in chunks. Sounds like an interesting challenge :cool:, but once you have it made it will become invaluable. Good luck.
-
There is no easy way to do this without writing some programming. Take these things into consideration: 1) What are the dependencies among the tables? This will determine the order of the tables to be processed. For example, Invoice Header is processed before Invoice Details. 2) Auto increment keys could be a killer for you. On production the key may be 1001, but in debug that key is already used. Therefore the program would have to look at the data as a “business object”, meaning it would have to re-create the Invoice Header with a new key and the subsequent invoice details would have to have the proper foreign key to point back to this new Invoice Header. 3) Get yourself a good ER (entity relationship) diagram of your system and start thinking about how you could pull data over in chunks. Sounds like an interesting challenge :cool:, but once you have it made it will become invaluable. Good luck.
Thanks for the information. I really was hoping this would not be the case. So then the next questions will to be; Is there a way via stored procedure to: A. Command for turning on and off constrants? B. Command to turn off and on auto increment, or would truncating reset increments to 0? C. Is there a command that will allow for copying data between one DB server and DB another in SQL 2005? D. Is any of this easier in CLR functions? Thanks,
Leo T. Smith Program/Analyst Supervisor