SQL 2000 Server Agent Job Migration

[FT65]

I am upgrading the hardware of our MS SQL 2000 server, and I want to migrate all the jobs to the new server. I know how to export them as scripts, but have been unable to find out how to import them to the new server. If anyone can point me in the right direction, as to how this is done, I'd be grateful - thanks!

 

[memdiscount]

Hi;

1- Create script of all the jobs in one file, or up to if you want to do one by one then create one file for each job

2- Script has server name, find replace server name with your new server name carefully and make sure you must have proper users as well in new servers.

3- Run the script in new server

4- you should have all the jobs created in new server

Thanks

 

[FT65]

Thanks very much for the reply, I'll give it a go.

I wasn't sure if I would have to use DTS on MSDB on

SysJobHistory
SysJobs
SysJobSchedule
SysJobServers
SysJobSteps

Seemed rather over the top!

 

[memdiscount]

Hi;

About sysjobhistory, you will not get the history, those will be brand new jobs for that new server.... I won't be worried about history on new server.

The rest of the items will be added when you run that script to create jobs....

Try for one job first and then for the rest of the jobs..It is better to see how things are running using the script, take a look at the script, it will be useful..

Thanks

 

[TheQuestioner]

Dear SQL Server Overlords,

I'm part of a development team that uses MSDE 2000 to manage the database for our software product. As with any software, we periodically update the front-end and back-end of our product to all of our clients.

My problem is that I'm trying to find an easy way of updating all the views, stored procs, udt's, table structures of each of our client's databases so that it is synch with our master version.

Currently I'm using a range of third party products that compare the last released database to the master one, and then generate the appropriate TSQL DROP AND CREATE SQL statements.

However, if I then try and run this script on my client's MSDE server (using a third-party query analyser), I keep running into problems. Some of the views and stored procedures cannot be created, as the views that they are based on don't yet exist(as they are created afterwards in the script).

So, my questions are:-

1) Rather than manually trying to find a way of ordering the script so that the initial views/stored procedures are created first, is there a way of "bulk creating/adding" all of the views and stored procedures whilst temporarily suppressing any checks on underlying table/view/stored procedure existence?

2) Force each view/stored procedure to "rebuild/refresh" itself so that any internal structure changes have been updated and saved.

Thanks.

 

[TheQuestioner]

Sorry, ignore my last post. All those late-night caffeine injections has impaired my ability to start a new thread rather than add on to this thread.