SQLServerCentral Article

Recover Those Packages!


Recover those DTS Packages!!!!


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.


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!!!!


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

Return to Steve Jones Home



5 (1)

You rated this post out of 5. Change rating




5 (1)

You rated this post out of 5. Change rating