Post reply

Transferring DTS packages between servers

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

  • September 9, 2003 at 6:59 am

    #473194

    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.

    .

  • September 9, 2003 at 7:13 am

    #473198

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

  • September 9, 2003 at 7:44 am

    #473210

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

  • September 9, 2003 at 5:30 pm

    #473332

    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

  • September 10, 2003 at 6:34 am

    #473400

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

  • September 10, 2003 at 8:16 am

    #473433

    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?

  • September 10, 2003 at 9:33 am

    #473463

    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


    R David Francis

  • September 10, 2003 at 11:16 am

    #473507

    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.

  • September 11, 2003 at 12:07 pm

    #473780

    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.

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

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