Transferring DTS packages between servers

Question

Transferring DTS packages between servers

Carl Farmer

SSCrazy

Points: 2197
More actions
September 9, 2003 at 6:48 am

#64387

What is the best way to transfer DTS packages from one server to another? Will a backup and restore of a particular DB (ie MASTER) do it, or can I transfer the packages another way?
This is for a server replacement so all the naming conventions in the DTS packages will not need to be changed as the new server will eventually have the same name as the old server.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

mybid2003 SSCertifiable Points: 6620 More actions · Answer 1

You select "SAVE AS" option after opening the DTS package. Choose "Structured Storage File" in "Location". Give the path and File Name you would like to save to. Then copy this file to the new Server.

Open EM in the target machine, right click on the "Data Transmission Services" --> Open Package --> select package --> then save the package to local SQL Server. Done.

.

Carl Farmer SSCrazy Points: 2197 More actions · Answer 2

Thats great thank you. What if the machines cannot be on at the same time (same names). Presumably go via a third server?

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

There are some articles talking about this in http://www.sqldts.com.

philcart SSC-Forever Points: 47796 More actions · Answer 4

Definately check out www.sqldts.com, especially DTSBackup2000, great little product.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

jack-64275 SSCrazy Points: 2017 More actions · Answer 5

Open the package in design view, choose save as, and change the server to the new location.

xuthus SSC-Addicted Points: 403 More actions · Answer 6

For a complete server swap I've always just backed up and restored the msdb database to retain the DTS packages and all the job information. What's the benefit to copying every DTS individually?

RD Francis SSCarpal Tunnel Points: 4867 More actions · Answer 7

quote:
For a complete server swap I've always just backed up and restored the msdb database to retain the DTS packages and all the job information. What's the benefit to copying every DTS individually?

I've tried that, but it didn't work for me. The various jobs didn't seem to be attached correctly any more. Are there any secrets to it?

R David Francis

xuthus SSC-Addicted Points: 403 More actions · Answer 8

quote:
I've tried that, but it didn't work for me. The various jobs didn't seem to be attached correctly any more. Are there any secrets to it?
R David Francis

I don't know if there's a secret to it, but this is the method I went through recently moving sql from one server to another. (Both servers had the same name, so only one was on the network at one time)

1.back up all databases (except tempdb)

2.on new server, restore all user databases

3.restore model

4.restore msdb

5.restore master

6.restart SQL services

The only thing I had to set up on the new server was to reset some of the server configuration options.

If you just backed up and restored msdb from one server to another, I'm not sure how that would work. The job steps seem to reference databases by name, but I would suspect there are some database id's in there somewhere, which could cause problems, but I'm afraid I don't really know.

Veraon SSC Veteran Points: 246 More actions · Answer 9

quote:
I've tried that, but it didn't work for me. The various jobs didn't seem to be attached correctly any more. Are there any secrets to it?
R David Francis

Are you talking about jobs that was created as a result of scheduling DTS package? At the design stage, if to create a job and add a step with DTSRun command in it, it will not be a problem with attaching job after restoring DTS package.