Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to restore a DTS package in SQL Server 2000

Posted on 2008-10-20
5
Medium Priority
?
834 Views
Last Modified: 2012-05-05
Hi all.

I accidentally deleted a DTS package from SQL Server 2000, how can I get it back?

I tried restoring the msdb database to msdb_test and then went to the sysdtspackages table and copied the dts from there and pasted it to the msdb database.

Now, you can see the DTS in the list but when you try to run it, the following error appears:

Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: No data for the specified Package was retrieved from the specified SQL Server.

Any ideas? Thank you in advance.
0
Comment
Question by:printmedia
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:randy_knight
ID: 22759684
there are several dts tables in msdb.  You'll need to find all of the entries and move them.  Even then you might have issues.  what I woud do is restore your msdb backup to a different instance as msdb, not msdb_test.  The package should show up in EM in that instance.  Then you can save it to the other instance.  

0
 

Author Comment

by:printmedia
ID: 22759721
Thanks for the reply randy.

I don't quite understand, I should restore my backup of msdb over my existing msdb?
0
 
LVL 4

Accepted Solution

by:
randy_knight earned 2000 total points
ID: 22760705
if you do that you will overwrite your existing msdb.  My idea was to restore it to another SQL Server instance (even if it's a named instance you install on the same server for this purpose).  
0
 

Author Comment

by:printmedia
ID: 22761175
I only have 1 SQL Server, how can I create another instance?
0
 

Author Comment

by:printmedia
ID: 22761586
Nevermind I got it. Thanks Randy.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question