Recover those DTS Packages!!!!

Introduction

Recently I did a stupid thing. Not accidental, not excusable, but really, really dumb, stupid. Actually, I had done a series of stupid things and then capped it all off with a beauty of an absolutely, lame-brained, basic rookie-mistake. Fortuneatly I was able to recover from my mishap and remain employed to write about it.

Making Mistakes

The first mistake I made, which I continued to make over and over for a few weeks, was to not perform backups of my packages. I had been backing up the server, but I had not specifically made a file system backup of my packages, something that I usually do. This was a QA server, and most of the packages were not important, but a couple were. In fact, one of the packages is used to automatically update the QA databases from the production systems. This is important to preparing out weekly QA server for testing the new enhancements that are being released from development.

So, if you haven't made a backup of your DTS packages, please do so now. Open each package (that you can't afford to lose) and save it as a "Structure Storage File" format. This is easy to do and outlined in the Save a DTS Package Locally. Take your time, I'll wait while you get a backup of your stuff going.

Now that everyone has backups of their packages, I'll continue my explanation of how a DBA can compound his mistakes in a hurry.

Since the particular server that I was working on was a QA server, I thought this would be a great place to test the installation of Service Pack 1. While this is an important server, it is used only 2-3 days a week, so I would have some time to work with the installation. Unfortunately, the installation did not go that smoothly and the only way to "uninstall" the service pack is to Uninstall and Reinstall SQL Server. So, I detached my databases (see Detaching and Attaching a Database) and uninstalled SQL Server. Then I reinstalled SQL Server and attached the databases. Unfortuneately, I didn't detach the master or msdb databases. After all, this isn't a production server, so I didn't think about the packages and scheduled items running on this server. I had a recent backup of the server, but I am always wary of restoring the system databases.

Recovery

Of course, I then proceeded to create a new package and perform some other work on the server, including setup a couple new security accounts. It was after the weekend when my automated restore did not work that I realized my mistake. I was then faced with a dilemna. I take good notes when I change things, (see Log Your Changes) so I could restore msdb and then reapply my recent changes from the notes.

Or, I could see if there was another way to recover my packages. I first scanned the SQL Server FAQ and found that I could transfer packages using DTS and using msdb.dbo.sysdtspackages as the source. So I restored my msdb database on the server as oldmsdb. I queried oldmsdb.dbo.sysdtspackages and found all my packages there.

Next I tried to build a DTS package to transfer this table. No luck. The drop down for the destination of the Transform Data Task doesn't display the system tables. Same thing for the wizards. I could have built a package using VB and manually populated all the locations, but I decided to take the easy way out. I ran a simple:

  insert msdb.dbo.sysdatapackages
    select * from oldmsdb.dbo.sysdtspackages

Conclusions

I realize this may not be the best way to recover lost packages, but in the event you make a mistake like I did, and compound it by making other changes to the server, this does provide a method for merging changes between two msdb databases back together. This will also allow you to "roll back" to different versions of packages if you have a backup and do not wish to restore all packages. Of course, be sure you qualify the insert or update statement with a WHERE clause that limits the query to the packages you are trying to recover.

I also learned a valuable lesson and as soon as my package was back, I backed up all DTS packages to .dts files and loaded them into our version control system.

Hopefully this is a useful technique for some of you and it comes in handy. I welcome feedback on this article using the links below and please rate the article. We authors love to know what you think.