Solved

Move MSDB from SQL7 to SQL2000 on a different server.

Posted on 2006-06-14
11
294 Views
Last Modified: 2012-06-27
How do I transfer all my DTS packages and jobs from SQL7 to SQL2000. Need an easy way because I have hundreds of packages.

/RG
0
Comment
Question by:entronet
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 16904762
Nothing built in to SQL Server....

go to www.red-gate.com and see if the SQL DTS compare has a function to help....14 day free trial, fully functional
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16904793
Jobs can be scripted and run on the 2000 server
0
 

Author Comment

by:entronet
ID: 16904812
I don't think it has. I heard there are some ways to do it, but I'm not sure how.

Thanks!
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 20

Expert Comment

by:Sirees
ID: 16904867
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 16904873
Jobs yes, DTS no....going to have to be done manually, or quite difficultly

MAYBE you can backup msdb, restore it on a test sql 7 box, upgrade the test box to SQL 2000 (same service pack as the prod SQL 2000) and then move the data from the relevant dts tables in msdb

Need lots of coffee and pizza for this one
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 16905071
For the pkgs stored in SQL, copy the contents of the table "msdb.dbo.sysdtspackages" from one server to the other.  Be sure to stop the SQL agent on *both* servers *first*.  And naturally you will need to enable updates to system tables on the receiving server.  You should not have to empty out the table beforehand on the receiving server.

Another method would be save each pkg as a file and then load each file onto the new server, but that must be done pkg by pkg (it's still better than recreating the pkg though!).

The jobs can be scripted but you will need to modify the scripts to correct server names, etc., before running them on the new server.
0
 

Author Comment

by:entronet
ID: 16905201
Sorry for my ignorance Scott, but how do I enable updates on the systables on the SQL2000 box?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 16905317
On the receiving server:

1. Backup the existing MSDB and master dbs
2. Stop the SQL Server Agent
3. Execute these commands in Query Analyzer:
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
4. Use DTS to copy the msdb.dbo.sysdtspackages from the source server to the receiving server
5. Run these commands in QA:
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
6. Look at the copied pkgs, verify that they can be "designed" and changed as needed
7. Start SQL Server Agent
0
 

Author Comment

by:entronet
ID: 16905357
I was able to export the table with DTS. It looks like everything went fine (no errors). I did not do the sp_configure 'allow updates'

I'll verify that all the packages looks fine. I appreciate your help, it looks like your method worked great.

Now, on to the Jobs ;)

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 16905758
>> I'll verify that all the packages looks fine. <<

Great!  Especially the connections, which will usually be based on the old server name not the new one.

Congrats, though, because as indicated above by other comments the DTS pkgs are the hard part (without a "hack" method :-) ) so you're more than halfway home!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

807 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