Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Move MSDB from SQL7 to SQL2000 on a different server.

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
entronet
Asked:
entronet
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Kevin3NFCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
Jobs can be scripted and run on the 2000 server
0
 
entronetAuthor Commented:
I don't think it has. I heard there are some ways to do it, but I'm not sure how.

Thanks!
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
SireesCommented:
0
 
Kevin3NFCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
entronetAuthor Commented:
Sorry for my ignorance Scott, but how do I enable updates on the systables on the SQL2000 box?
0
 
Scott PletcherSenior DBACommented:
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
 
entronetAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now