how to export jobs and maintenance plans?

[DLu]

Hi:

2 SQL2000 ent sp4 servers.
Try to 'one step' to export all jobs and related maintenance plans from serverA to serverB, via backup msdb from A and restore to B.

After restore msdb to serverB, I saw all jobs and related maintenance plans, but the jobs are not runable.

Do I have to save jobs scripts one by one from A, then rerun on B one by one?

thanks
David

[rmiao]

Can you edit maintenance plan on server b?

[DLu]

Hi Ramio:

Yes, I could edit both maintenance plan and jobs.
but the job exec failed with error "The job failed. The owner () of job Job_DB_Integrity_Checks_Daily_11PM does not have server access."

thanks
-D

[rmiao]

Who's job owner then? You may need modify job's originating server in msdb..sysjobs.

[DLu]

Hi Ramio:

since it is a SQL2000 sp4 ent, I could update its systable in MSDB.

update sysjobs set originating_server = 'serverB' where originating_server <> 'serverB'

restart sql, rerun job,
"The job failed. The owner () of job Job_DB_Integrity_Checks_Daily_11PM does not have server access."

thanks
David

[rmiao]

Did you check job owner?

[DLu]

Hi Ramio:

You are right. After checking the owner and changed to 'sa', all are running fine.

which sys table in MSDB holding that owner field?
Now I can use this approach to migrate all DB mainteance plans and Jobs from serverA to serverB, with bk and restore!

p.s. does this approach also works between sql2005? and between sql2000 and sql2005? siince sql2005 does not allow system table update any more...

thanks
David

[rmiao]

owner_sid in msdb..sysjobs.

[DLu]

Hi Ramio:

That is why I missed the owner_SID in sysjobs table, since it is kind of encryped to '0x01'. probably the only way is to changed it at sql console.

thanks
David