Solved

Move MSDB from SQL7 to SQL2000 on a different server.

Posted on 2006-06-14
11
296 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

634 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