Post reply

Accidental deletion of DTS package

  • August 4, 2005 at 7:38 am

    #67829

    Hello,

    I have accidentally deleted a DTS package from an instance of SQL Server 2000.  Does anyone know if it is possible to recover it?  I have DB backups/ server backup tapes, but don't know where the info that makes up a DTS package is stored.

    Any help would be much appreciated!

    Thanks,

    -James

     

  • August 4, 2005 at 7:45 am

    #579562

    Did you save the package to bas file?

    Do you have a backup of msdb?

  • August 4, 2005 at 7:53 am

    #579567

    Hi Remi,

    I don't have a saved bas file, but do have a backup of MSDB - will this help?

    Thanks,

    -James

     

  • August 4, 2005 at 8:18 am

    #579591

    This is were the packages are kept... I don't know the best route to take from there. But here's one that seems safe...

    Restore msdb to a new clean server. Open the package, then export it to the other server... I know there are many ways but I can't explain them in details. But on top of my head, you can save to bas then reimport on the other server. I also saw a few scripts/articles on this site that could perform this task, just use the search engine to find them.

  • August 4, 2005 at 9:34 am

    #579629

    James,

    As Remi suggested, restore msdb to another instance.  Open the package, click on Package, Save as..., and save it back to the original server.  There's also a utility at DTS.com that works well:  http://www.sqldts.com/default.aspx?204 

    Greg

    Greg

  • August 4, 2005 at 9:36 am

    #579631

    Thanks a lot for your help guys, I have now restored the package, and have taken care to save a copy in .bas format!  Phew!

    - James

  • August 4, 2005 at 9:53 am

    #579641

    Ya that's alway safer that way .

  • August 5, 2005 at 8:59 am

    #580060

    The packages are stored in a table called msdb..sysdtspackages.  There will be a row in that table for each version of the package.  

    Opening and saving to a new location will not restore all of the old versions, but copying the rows from sysdtspackages will. 

    hth jg

     

  • August 5, 2005 at 9:12 am

    #580070

    Is there any other relevant info to copy besides that (like the history).

  • August 5, 2005 at 10:52 am

    #580111

    I use a tool called DTSBackup2000 that is quite nice and easy to use. I have scheduled the backups of all DTS packages on the servers and so far so good.

    You can get it from http://www.sqldts.com/default.aspx?272 and the price is perfect!!

    Good Luck,

    Darrell

  • August 5, 2005 at 11:51 am

    #580120

    Remi,  I've written a simple .Net utility that will allow for a connection to 2 different SQL Servers and compare most recent versions of DTS Packages and allow you to compare/backup/update/overwrite DTS Packages from your Source Server to your Destination server without the need to export to SSF or .BAS.

    I use it to sync development to production environments... If you are interested in a copy to test out, lemme know.   mgercevich_NOSPAM@gmail.com  (remove "_NOSPAM").

    -Mike Gercevich

  • August 8, 2005 at 2:52 am

    #580341

    DTSBackup 2000 looks like an interesting tool - I will check it out. 

    Thanks for all the replies, I now have a much better understanding of how DTS packages work! 

    - James

     

     

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply