Recover deleted Local DTS package

[jhall01]

I have a DTS package that was saved on the SQL 2000 Local Packagew under the Data Transformation Services that got deleted by another admin.

How can I recover??? is it saved in a directory on the server to recover from?

Thanks,

Jon

 

[Corran007]

it is in the msdb database. just restore the msdb database to get it back. Since this is usually unacceptable to do on a prod machine, and i wouldnt recomend it just for this anyhow. HAve a second machine if you can with the same version. then restore it there and save it back over to the machine it was deleted from.

 

[jhall01]

Thanks for the response. Here is how I fixed the problem.

Pulled a backup from the previous day of the MSDB database and made it a new temp database called Restore_MSDB.

I then went into the sysdtspackages table in the restore_MSDB and inserted all the records for the name of the Local Package back into the MSDB database.

It was alot easier than I thought. The key is to insert all historical records for the Local Package name since I guess that table stores incremental changes from the first time it was created.

That was all I had to do.....Thank goodness!!!!

 

[nigelrivett]

You would be better off saving and loading packages from structured storage files then you can use a file backup to save them - makes moving and releasing simpler too.

If you want to save in msdb then I would add a job to save them daily to a directory which is backed up.
I also include a job to script properties so I can save it in sourcesafe and see what has changed.

This saves dts packages (but loses formatting)

http://www.nigelrivett.net/SaveAllDTSPackagesToFiles.html

This scripts proeprties

http://www.nigelrivett.net/ScriptDTSProperties.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.