Remember to move DTS2000 packages when upgrading msdb from 2005 to 2008

  • Article

Some customers prefer to upgrade to SQL Server 2008 by detaching all databases from their SQL Server 2005 instance and then reattaching them to a completely new SQL Server 2008 instance.

If you choose this upgrade strategy, you should be aware that you cannot detach an msdb database from a SQL Server 2005 instance and reattach it to a SQL Server 2008 instance . If you try, you will get an error message. Backup/restore will not work for msdb in this upgrade scenario either.

The msdb database may contain objects that are relevant to your installation – for example agent jobs and DTS 2000 packages. When upgrading, you should make sure you move all relevant objects in msdb. You can use tasks in Integration Services to move most of these objects. However, DTS 2000 packages cannot be moved using the Integration Services tasks. Instead, you must manually move the contents sysdtspackage.

The CSS team has a great blog entry describing how to move DTS 2000 packages to a SQL Server 2008 instance: How to copy DTS 2000 packages between servers (and from SQL 2000 to SQL 2005 and SQL 2008)