Move DTS and JOB from one server to another

  • Hello,

      I need to move all the databases (SQL 2000) from one server to another. Moving databases themselves are done. I just wonder what is the easiest way to move all related DTS and JOBS over.

    Thank you very much for your help.

     

  • If you can "see" one server from the other, the easiest way to move a DTS job is to open it, go to Save As and choose the new server as a destination.  You will want to verify carefully that all objects referred to in the package are local and don't point back to the old server.

    To move your jobs, right-click on them in Enterprise Manager and choose All Tasks, Generate SQL Script.  You can then run the ensuing script on the new server.

    John

  • john, thanks so much for the lighting speed response.

    i was hoping i did not have to transfer DTS one by one.

  • Did you try the table, sysdtspackages, in msdb for DTS packages, then, transfer the packages?

  • no, I did not try sysdtspackages. Is it a straight copy all the data in that table to the new server??? could you elaborate a little?

    thanks a lot.

  • How to transfer DTS packages between servers?

    There are 5 approaches doing it.

    Open Design Package in DTS, then, Save As …

    Using T-Script

    Transfer selected DTS:

    SELECT * FROM dbo.sysdtspackages WHERE [name] IN ('PackageName1', 'PackageName2')

    Transfer the latest DTS:

    SELECT T1.* FROM dbo.sysdtspackages AS T1 INNER JOIN (SELECT [name], [id], MAX([createdate]) AS [createdate]

    FROM dbo.sysdtspackages GROUP BY [name], [id]) AS T2 ON T1.[id] = T2.[id] AND T1.[createdate] = T2.[createdate]

    Transfer the most recent

    n versions:

    SELECT T1.* FROM dbo.sysdtspackages AS T1INNER JOIN (SELECT T2.[name] , T2.[id], T2.[createdate] FROM dbo.sysdtspackages T2

    GROUP BY T2.[name], T2.[id], T2.[createdate] HAVING T2.[createdate] IN (SELECT TOP n T3.[createdate]

    FROM dbo.sysdtspackages T3 WHERE T2.[id] = T3.[id] ORDER BY T3.[createdate] DESC) ) AS T2 ON T1.[id] = T2.[id]

    AND T1.[createdate] = T2.[createdate]

    Import/Export msdb.dbo.sysdtspackages to other server;

    Backup a copy of msdb and restore to new machine and restore to new machine. This will move all pacages but will wipe

    out existing info from msdb such alerts, operators, SQL Agent Jobs and Agnet configuration;

    Backup copy of msdb from first machine and restore it as a new database on the second machine. Set the allow updates

    option on and insert rows into msdb from a select statement on sysdtspackages from the restored copy of msdb. Attention

    needs to be paid to the object owner_sid and owner. These must match an existing login in master sysxlogins.

  • Another option is to use DTSBackup 2000, freeware written by Darren Green.  You can transfer multiple packages and it works great.  Download it here:

    http://www.sqldts.com/default.aspx?242

    As for jobs, I'd script them out and run them on the destination server.  You can script all jobs at once from Enterprise Manager by right-clicking on Jobs, all tasks, Generate SQL Script...

    Greg

    Greg

  • Greg,

    I have used DTSBackup 2000 on a local server and have had great success with it.  I recently did a restore from a default instance to a named instance and have had some issues with jobs.  They run, but the packages they refrence do not fire.  Jobs run with matching times from the previous server, yet tables do not get updated on the local machine.  The packages have had all db connections reconfigured to use server\instance vs (local). All paths to files are valid and accessible.  Do you (or anyone) have any ideas?

    TIA...  Bill  

  • Do the packages run if you execute them manually in the new instance?  Is the account that SQL Server Agent runs under the same as on the old instance?  Do the jobs or packages generate any errors?

    Greg

    Greg

  • Answers in order: Yes,Yes,No.  On one of the pkgs I altered the DTSRun to NOT use the encrypted string.  I was successful with that one.  So, I figure I will create a new 'Best Practices' and have all jobs that run pkgs use DTSRun /S "servername" /E /N "pkg name" and I should be OK. 

    Thank you for your time and effort!

    Bill.

  • This article "Moving DTS Packages" betweene two servers written by Gregory A. Larsen is very helpfull in ths issue

    http://www.dbazine.com/sql/sql-articles/larsen8

    / Peter Levander

  • I have found that restoring msdb on the new serer is much easier and less

    when transfering sqlagent jobs

  • Hello,

    I am using SQL DTSBackup 2000, it is working fine at our IT Department. You can download it from the following location.

    http://www.sqldts.com/default.aspx?242

Viewing 13 posts - 1 through 12 (of 12 total)

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