How to copy SQL jobs from one SQL instance to another.

rwheeler23
rwheeler23 used Ask the Experts™
on
I am trying to copy several SQL jobs from our test server to production. When I run the scripts to drop and create these jobs I get messages like these.  What must I do to these scripts to get them to run successfully on the production server?

Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 43
The specified @job_id ('C09EF46A-E6EC-4074-B5F3-A932F5B5B495') does not exist.
Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 203
The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> The specified @job_id ('C09EF46A-E6EC-4074-B5F3-A932F5B5B495') does not exist.

Please be noted that Job ID is a uniqueidentifier and hence can't force the values to be same across Servers.
If you have IF EXISTS or IF NOT EXISTS for DROP/CREATE job, then request you to change it to use Job name instead to get it work..
or else, you can simply ensure that those jobs are not already there in the target server and simply execute the CREATE job scripts to get it work fine.

>> The specified '@owner_login_name' is invalid (valid values are returned by sp_helplogins [excluding Windows NT groups]).

This indicates that one of the Job owner login in Test Server is not available in Prod Server. either create the Login in Prod or just check for these login names in your script(Just Ctrl+F) and replace those values with some valid prod login to get it work..
rwheeler23President

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial