Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Move MSDB from SQL7 to SQL2000 on a different server.

Posted on 2006-06-14
11
Medium Priority
?
298 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
[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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 70

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 70

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 70

Accepted Solution

by:
Scott Pletcher earned 1500 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

722 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