Solved

How to restore a DTS package in SQL Server 2000

Posted on 2008-10-20
5
796 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

710 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