SQL Server Performance

Saving and restoring DTS packages

Discussion in 'General DBA Questions' started by Sidd, Nov 7, 2007.

  1. Sidd New Member

    Hi friends,
    I have been asked by my mananger to backup all dts for all servers.
    can you please guide me how to save and restore DTS in sql 2000 .
    how do we Backup DTS.
    HOw to restore DTS
    please suggest.
  2. Greg Larsen New Member

    If your DTS packages are store in the MSDB database the get backed and restored when you restored the MSDB database. I also have moved a bunch of packages using SQL-DMO via a SQL Script. Here is an article I wrote on the subject: http://www.dbazine.com/sql/sql-articles/larsen8
  3. Sidd New Member

    thanks a lot for your response and also to throw some light on this topic.
    if have some doubts:
    Actually as i mentioned that I have sql server 2000 and the new server is also sql server 2000.
    i have to restore only two databases from the old server.now, after restoring databases i have to move dts of these two databases A and B from old sql 2000 to new sql 2000.
    now i have to backup and restore all dts packagon new server sql 2000 for two databases.
    please suggest some other way if possible.
    what if i restore msdb of old server to new server.
    will that show up the dts for those two databases.
    please i need it soon!
  4. anandchatterjee New Member

    You can save the DTS accross the server. Please refer BOL.
  5. satya Moderator

    You can restore the MSDB database from source to target server if you have large number of dts packages & other scheduled jobs.
    I have use DTS backup tool from http://www.sqldts.com/ where I have comfortably moved 1000's of packages recently.
  6. Greg Larsen New Member

    If you are only moving a couple of database and DTS packages from the old server then moving the MSDB is probably not an option. This would move all packages and a lot more.
    I'd consider a scripting method to move only those packages you need. I know when I used my scripts as documented in my article I could code it to move only the DTS packages I wanted. One big plus to my script method was I was even able modify the connection strings to refer to the new server while you are moving the packages. This way when the packages where moved they worked without having to modify each package manually to change the connection information.
  7. Sidd New Member

    thanks alot.
    if I have to choose one other methods from
    1. saving dts accross in new server from old server and then opening the dts in new server.
    2. the scripting method mentioned by larson.larson can you please tell me how you scripted the selected dts and exported to new server
    without any problem.
    If I save dts across the sever and open it in new server would i have to make any changes.
    please reccomend.
  8. Greg Larsen New Member

    I'd look at the article I referenced to find out the details. Basically you
    - execute msdb..sp_enum_dtspackages sp to get a list of all the packages on a server
    - use the DTS.Package method LoadFromSQLServer to get the package from the source server
    - Change the Connection Datasource property from the old server name to the new server name
    - use the DTS.Package method SaveToSQLServer to save the modified package to the new target server.


Share This Page