Attaching a SQL 2000 database to SQL 2005 looses the DTS packages

Question

Attaching a SQL 2000 database to SQL 2005 looses the DTS packages

Rod at work

SSC-Dedicated

Points: 34008
More actions
September 19, 2006 at 1:54 pm

#115736

Double check me on this, please. I am testing our applications against SQL Server 2005 on a test server. We didn’t properly upgrade our SQL 2000 databases to SQL 2005 (it is a long story). What we did was installed SQL 2005, found that the databases were still in SQL 2000 format, uninstalled SQL 2000 and then attached all of the databases to SQL 2005.

I have found that none of our DTS packages are available, and I believe it is because of the way we “upgraded” them. Am I correct?
Kindest Regards, Rod Connect with me on LinkedIn.

Viewing 7 posts - 1 through 6 (of 6 total)

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

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 1

There is, or was, (I think it's part of SP1 now) a backward compatibility kit that will allow you to view and run DTS packages. It also has an upgrade wizard I believe.

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 2

So, MG, is it the case that being able to view the DTS packages is not on by default? I spend some time on Microsoft's SQL site to see if I can find that backward compatibility kit you are referring to.

Kindest Regards, Rod Connect with me on LinkedIn.

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 3

have you been here:

http://msdn2.microsoft.com/en-us/library/ms143706.aspx#runtime_installing

MG

"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 4

iirc, DTS packages are stored in the MSDB database. Since that is a system database, I suspect you did not 'attach' that database to SQL2K5.

Rod at work SSC-Dedicated Points: 34008 More actions · Answer 5

You are correct, Wanderer, we did not attach any of the system databases, when we attached our databases.

I've noticed that none of our maintenance plans came over as well. Are things like maintenance plans or sq

Kindest Regards, Rod Connect with me on LinkedIn.

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 6

that's right. MSDB houses all your maintenance plans, etc. Also, if you configured your model DB in SQL2000 to specifiy deploy certain SP's etc. you may want to make sure they are in the new SQL2005 model db. Also, you will find that anything you deployed to master ( which hopefully is nothing) is missing.

Finally, while you retain the SQL Users at the DB level, you will have lost the SQL Logins. I haven't done this, but I believe there is a SSIS process to 'look' at a SQL 2000 server and script the logins, and then try and create them on your SQL2005 server (google / search here for more on this). However, unless you have a lot of logins, you may find it simpler and quicker to re-create the logins on the SQl2005 server, and then run sp_change_users_login 'auto_fix' ... check BOL for more details.