Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-21 : 06:59:18
|
| Paul writes "Hi,Using SQL 2K, sp2, and Win2K, sp3.The question: Using DTS (because we can't move to a replication or log-shipping solution anytime soon), how can I copy the data, functions, and sps from one source DB on Srv1 to destination DBs on Srv2, Srv3, and Srv4?Info: This is required so that we can have local backup (Srv2), and remote main (Srv3), and remote backup (Srv4) servers.Catch #1: Some of the DBs are huge. They normally take over 2 hours to transfer using the Copy SQL Server Objects task. My nightly window is 5 hours (1 a.m. - 6 a.m.). As you can see, moving the DBs serially will not respect this constraint (3 copies = 6 hrs, and I have 27 other, smaller DBs to copy).Catch #2: In all but 2 of these DBs, the tables are identical, or very nearly so.Info: I have asked this question in several forums (not yours!) MS public.sqlserver.dts, p2p.Wrox.com, etc... I keep getting the same answer: It should be possible to do this in parallel, but no How to do it. I have tried to set up multiple Copy Objects tasks in one package, but they fail because, executing in parallel, there is a locking conflict on the files generated on the source server (regardless of where I specify the scipts folders).The question: How can I set up DTS packages (one per DB) such that each package copies its DB to the 3 destination servers in parallel? As it is now, I have 27 serial packages running within the time constraint, but only copying their DB to one server.Thanks,Paul" |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-25 : 12:58:26
|
| Are you copying the objects plus the data or just the data? If you are copying objects plus the data, then on the 2 databases where the tables are identical, why don't you just do a backup and a restore for this?Tara |
 |
|
|
|
|
|
|
|
Copying contents of multiple DBs to multiple servers