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
and all my packages were back!!!!
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.
Steve Jones
dkRanch.net July 2001