Avatar of edrz01
edrz01
Flag for United States of America asked on

Export SQL 2000 DTS, Security profiles and Server Agent Jobs to SQL 2008

I am in need of a method to export or create file to transfer all DTS files, Jobs and Security permissions from our older SQL 2000 server into the new SQL 2008 server. Is there an app within either SQL to perform this? Or a commercial app that you cannot promote in this venue? At present I have scheduled to backup each Database Container through the Management Job into the SQL 2008 Backup folder.
Microsoft SQL Server

Avatar of undefined
Last Comment
edlunad

8/22/2022 - Mon
edlunad

DTS files and Jobs are stored in the system database MSDB, logins are stored in the system dabase Master, you can just make a regular backup of those and restore it into the new one (this may overwrite your current 2008 data)

or if you want to copy just some items you can do it with regular t-sql queries, here is a query to view all your DTS jobs...

SELECT     *
FROM         msdb.dbo.sysdtspackages

Open in new window

edrz01

ASKER
edlunad, I am able to see all the packages...however, how am I to export and import into 2008? And I have backed up the MSDB and Master DB's... when I 'restore' those DB's into 2008 are you saying that all the security permissions and Jobs will import?
ASKER CERTIFIED SOLUTION
edlunad

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes