February 4, 2002 at 8:38 am
We are moving a single database from our old production server to our new one. Does anyone know how can I migrate DTS and SQL Agent Jobs from one SQL7 to another.
I have looked at the msdb and master databases, but I don't see a simple elegant way of doing it.
February 4, 2002 at 9:45 am
I have never had to do this, but I think you can just copy a backup of msdb and restore it on the new server.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 4, 2002 at 10:31 am
Robert has my sugestion, but this might not do it all. Are teh connection passwords the same? Are all paths valid on the new server? Do the packages reference the server by name?
You have to do some checking besides move the data.
Steve Jones
February 4, 2002 at 10:33 am
I had thought of this Robert. The only problem is that I don't want all of the jobs from that server - just the ones for that db. Also, I believe that some of the users are going to be different on the new one. Any ideas?
February 4, 2002 at 10:37 am
For moving DTS packages that only apply to one databse the best way I know is to open each DTS package and save it on the new server. Then you'll need to do the checks that Steve mentioned. After that you can script the jobs and run that script on the other server. This may have been what you were talking about as not seeing a simple elegant way of doing this kind of move.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 4, 2002 at 10:44 am
February 4, 2002 at 10:51 am
Unfortunately, It's 104 jobs. 48 of which are Web Assistant Jobs, the remainder at DTS Packages.
If I have to do it manually then so be it... But I was hoping that perhaps someone had scripted this before or - knew of a utility.
Thanks for your quick responses guys.
February 4, 2002 at 11:28 am
Your welcome. It'd be interesting to see how it goes. Let us know what works and what doesn't.
Steve Jones
February 5, 2002 at 10:57 am
Wouldnt be too hard to write a VB loop that would open each package and then save it on the new server. Dont know about the web assistance stuff, there are procs and some tables involved too arent there?
Andy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply