December 18, 2006 at 6:52 am
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.
December 18, 2006 at 6:58 am
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
December 18, 2006 at 7:05 am
john, thanks so much for the lighting speed response.
i was hoping i did not have to transfer DTS one by one.
December 18, 2006 at 9:24 am
Did you try the table, sysdtspackages, in msdb for DTS packages, then, transfer the packages?
December 18, 2006 at 9:32 am
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.
December 18, 2006 at 9:52 am
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.
December 18, 2006 at 12:04 pm
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
January 4, 2007 at 7:54 am
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
January 4, 2007 at 12:29 pm
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
January 4, 2007 at 2:25 pm
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.
March 27, 2009 at 9:00 am
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
April 15, 2009 at 12:47 pm
I have found that restoring msdb on the new serer is much easier and less
when transfering sqlagent jobs
April 15, 2009 at 2:56 pm
Hello,
I am using SQL DTSBackup 2000, it is working fine at our IT Department. You can download it from the following location.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply