Solved

Move MSDB from SQL7 to SQL2000 on a different server.

Posted on 2006-06-14
11
295 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

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

Title # Comments Views Activity
Creating a View from a CTE 15 46
T-SQL Query 9 35
sql server string_split 4 28
Rewriting a simple query 2 33
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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

734 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