Quick question re: msdb and DTS packages

• mazzz

  SSCertifiable

  Points: 6139

  More actions

  February 12, 2009 at 9:50 am

  #94975

  I'm intending to run a script against a large number of DTS packages, to change some connection names.

  While I have of course tested this, I need to be sure I can recover the packages if it all goes pear-shaped...

  As far as I can tell, all I would need for this is a backup of msdb - is this correct, or am I missing something?

  Many thanks

  ------------------------------------------------------------------------
  Bite-sized fiction (with added teeth) [/url]

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  February 12, 2009 at 10:14 am

  #943024

  That would work, but remember that msdb also contains jobs and backup information. A less intrusive way to recover DTS packages would be to export them to files or to another SQL Server instance. I like to use DTSBackup2000 to export all the packages in msdb at once. It's free and available here: http://www.sqldts.com/242.aspx

  Greg

• mazzz

  SSCertifiable

  Points: 6139

  More actions

  February 12, 2009 at 10:29 am

  #943033

  Thanks for the heads-up and the link, Greg.

  The sql server instance these packages reside on is used purely to host these packages and the SqAgent jobs that run them, so I wouldn't expect an msdb restore to cause any damage. Also, I don't think there have been any changes to that server for *years*

  Either way though, I think I'll be using that tool you recommended to export the packages elsewhere, just to be safe!

  Many thanks again

  ------------------------------------------------------------------------
  Bite-sized fiction (with added teeth) [/url]