How to Export DTS, Jobs and logins to a new server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to Export DTS, Jobs and logins to a new server

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2007-05-24 : 11:08:57

Hi All

I Have taken the backup of production database and restored it in a seperate server successfully

Now i need to Trasport all the SQLAgent related tasks like
Scheduled jobs, DTS packages and logins to the new server.

Please help me to achieve this.

Thanks

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-24 : 13:14:34

DTS has related tasks to transfer jobs and logins, you can save packages in new server with 'save as'.

venkath
Posting Yak Master

202 Posts

Posted - 2007-05-24 : 13:37:14

In Source server i have a database with name DB1 where all by DTS tasks were built

The copy of DB1 in another server is DB2

when i do save as the source table in DTS packages is still shown as DB1.Table1

How can i change it to DB2.table1..in Second server.

Thanks for your Help..

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-24 : 16:45:17

You have to edit packages one by one.

DMcCallie
Yak Posting Veteran

62 Posts

Posted - 2007-05-25 : 14:12:17

1. Stop SQL Agent on the target server.

2. Restore the MSDB database (that's where the jobs and DTS packages are (if they are stored locally in SQL Server)

3. Then change the originating_server for each job that has the old server name to the new server name in MSDB.sysjobs

SELECT * from sysjobs where originating_server = 'oldservername'

UPDATE sysjobs SET originating_server = 'newservername' WHERE originating_server = 'oldservername'

4. Disable all jobs (until you can check each one)

SELECT * from sysjobs WHERE enabled = '1'

UPDATE sysjobs SET enabled = '0' WHERE enabled = '1'

viola!

p.s. check your packages/jobs after that and correct any source/destination servers as needed...

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-05-25 : 16:43:34

To add to the last post - if you had stored procedures going against specific/linked servers (and those sps are executed by some jobs), you might need to change those stored procedures as well ...

OurDai
Starting Member

1 Post

Posted - 2011-05-19 : 05:18:57

The DTS packages can all be transferred in on go:-

On the source server

SELECT * INTO ZSYSDTSPACKAGES FROM SYSDTSPACKAGES

Use export data wizard or DTS to transfer the table to the new server

INSERT INTO SYSDTSPACKAGES SELECT * FROM ZSYSDTSPACKAGES

Drop the two ZSYSDTSPACKAGES tables

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources