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.
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
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!
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.
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.
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.
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.
