Need to Script all DTS Packages (SS 2000) 2

[JohnBates]

happy Friday to everyone,

I'm building an alternate server, to be used when the primary SQL Server is down for an extended period.

I want to script all the DTS packages so I can build them on the new server. I know I can copy the msdb database to the new server to accomplish this, but that requires the SQl Serer services be stopped on the primary server (while copying) and I'm trying to avoid that.

This is SQL Server 2000 Std.

I searched the forums here but did not find a=such a script.

Any ideas?

Thanks. John

 

[SQLDenis]

>>I know I can copy the msdb database to the new server to accomplish this, but that requires the SQl Serer services be stopped on the primary server (while copying)

Says who?

backup msdb and move it over to the new server but beware of this message
Cannot add, update, or delete a job that originated from an MSX Server

if the server name is different
To see a resolution for that read this (

http://sqlservercode.blogspot.com/2005/11/cannot-add-update-or-delete-job-that.html)

you can also open up the packages individually and do save as and spcify the new SQL server or save the as Structured storage files, see here for pics(

http://sqlservercode.blogspot.com/2006/10/saving-dts-package-on-another-server.html)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

Are you sure your DTS packages are stored in MSDB? There are 3 different storage methods and other developers might not have used the MSDB method.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[JohnBates]

Thanks for pointing that out Catadmin. Yes, all are in msdb. That's one of the few standards that is actually followed here.

SqlDenis >>Says who? I've used this technique before with good results. Stopped sql server on source and target servers. detach the db. copy the data and log files to target. attach db at target. start sql server.

I'll look at the 2 alternate options you listed.

Thank you. John

 

[johnscott76]

I've been using this tool for quite a while now and it works beautifully.

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

Back up, restore, and transfer between servers, all seamlessly and without issues.

 

[JohnBates]

johnscott76, this is the best thing since Microsoft released Windows ME - just kidding.

This is exactly what I needed. It worked perfectly. A very good tool.

Thanks very much! John

(I knew about sqldts.com but forgot to search there)