Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help Re-Generating MS Agent Jobs from dead Server Expand / Collapse
Author
Message
Posted Wednesday, July 30, 2014 12:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:52 AM
Points: 210, Visits: 378
This morning one of our DB servers died.

They restored backups of the DB's to another server but these didn't include the many MS Agent jobs the server had.

Anyway I have managed to get a backup of the old MSDB table where all the tables used to create MS Agent Jobs are held added to our new server.

Therefore I need a script to re-crerecreateon the new server.

There must be a job somewhere in MS SQL to script them out as you can do it from the management console?

Therefore does anyone know of a script to do this or where to find the MS one please let me know.


Any help would be much appreciated.

Thanks
Post #1597905
Posted Wednesday, July 30, 2014 1:32 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:13 PM
Points: 592, Visits: 924
Do you have a backup for the entire MSDB? If so, you can simply restore that over the new MSDB database.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1597931
Posted Wednesday, July 30, 2014 1:53 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:55 PM
Points: 17,728, Visits: 15,593
Keith Tate (7/30/2014)
Do you have a backup for the entire MSDB? If so, you can simply restore that over the new MSDB database.


That would be the easiest solution.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1597946
Posted Wednesday, July 30, 2014 2:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:52 AM
Points: 210, Visits: 378
Hi

I do have a full table but certain "sys admins" have already added some "important - must have" jobs.

Therefore I am doing this one by one at the moment which is working apart from setting the Target Server option.

I am trying to find out which table this is in so I can set it but I haven't had much luck so far.

The job below creates the job ok apart from this one thing. Which errors with "You must specify the servers on which this multi server job will execute"

I only want it to run on the local server. Which is an unticked box. I think it should be defaulted to on. But I cannot find the code to add it in. I am already using @@servername when I insert the new job records which I thought maybe would have triggered this.

I am also constantly getting FK errors when inserting the schedule records and no schedule records ever get added so I have to do them manually - which isn't a problem at the moment considering I have to set this TargetServer option manually anyway.

This is the code I am using - I have been constantly coding since 3:00AM BST this morning over 18 hours now so please excuse any obvious mistakes or issues. I am supposed to be off work due to Cellulitus on my swollen leg as well but I am constantly called on to do this work - nightmare!!!!

Let me know what you can see wrong with this code and how I can insert this TargetServer option so its always local server. I thought maybe it was to do with old server_id records somewhere in the backup table.

The debug is under the code


DECLARE @JobID UNIQUEIDENTIFIER
SELECT @JobID = job_id FROM msdb_from_neptunium.dbo.sysjobs WHERE NAME='Nightly Job'
PRINT @JobID

-- SELECT Name FROM msdb_from_neptunium.dbo.sysjobs WHERE Name NOT IN(SELECT Name FROM msdb.dbo.sysjobs)
INSERT msdb.dbo.sysjobs
SELECT * FROM msdb_from_neptunium.dbo.sysjobs
WHERE job_id=@JobID

INSERT msdb.dbo.sysjobsteps
SELECT * FROM msdb_from_neptunium.dbo.sysjobsteps
WHERE job_id=@JobID

--select top 1 * from msdb_from_neptunium.dbo.sysjobhistory select @@servername
SET IDENTITY_INSERT msdb.dbo.sysjobhistory ON
INSERT msdb.dbo.sysjobhistory
(instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
[message],run_status,run_date,run_time,run_duration,operator_id_emailed,
operator_id_netsent,operator_id_paged,retries_attempted,[server])
SELECT
instance_id,job_id,step_id,step_name,sql_message_id,sql_severity,
[message],run_status,run_date,run_time,run_duration,operator_id_emailed,
operator_id_netsent,operator_id_paged,retries_attempted,@@servername
FROM msdb_from_neptunium.dbo.sysjobhistory
WHERE job_id=@JobID
SET IDENTITY_INSERT msdb.dbo.sysjobhistory OFF

/*
select * from msdb.dbo.sysjobhistory
select * from msdb.dbo.sysjobsteps
select * from msdb.dbo.sysjobstepslogs
Select * from msdb.dbo.sysjobschedules
Select * from msdb.dbo.sysschedules
select * from msdb.dbo.sysjobschedules
select * from msdb_from_neptunium.dbo.sysjobschedules
where Schedule_id in(SELECT Schedule_id FROM msdb.dbo.sysjobschedules)
*/

PRINT 'DO Job Schedules' -- Select * from msdb.dbo.sysjobschedules where job_id='F5A0ECCB-4E0A-4D85-9A19-0867CA43F0EA'
IF EXISTS(SELECT Job_ID FROM msdb.dbo.sysjobschedules WHERE Job_ID=@JobID)
PRINT 'This Job exists in sysjobschedules'
ELSE
PRINT 'This Job does not exist in sysjobschedules'

DECLARE @ScheduleID INT
SELECT @ScheduleID = Schedule_ID
FROM msdb_from_neptunium.dbo.sysjobschedules
WHERE job_id = @JobID

PRINT 'ScheduleID = ' +CAST(@ScheduleID as varchar)

-- try and handle existing FK issues
IF @ScheduleID IN(SELECT schedule_id FROM msdb.dbo.sysjobschedules WHERE Job_ID=@JobID AND schedule_id=@ScheduleID)
AND @ScheduleID IN(SELECT schedule_id FROM msdb.dbo.sysschedules WHERE schedule_id=@ScheduleID)
BEGIN
PRINT 'This schedule_id exists in sysjobschedules'
PRINT 'This schedule_id exists in sysschedules'

END
ELSE
BEGIN
PRINT 'This schedule_id does not exist in sysjobschedules'

INSERT msdb.dbo.sysjobschedules
SELECT *
FROM msdb_from_neptunium.dbo.sysjobschedules
WHERE job_id = @JobID
AND schedule_id=@ScheduleID
AND schedule_id NOT IN(SELECT schedule_id FROM msdb_from_neptunium.dbo.sysschedules)
END

-- never returns any results
SELECT 'Schedule Details'
SELECT *
FROM msdb.dbo.sysjobschedules
WHERE 1=1
AND schedule_id = @ScheduleID
AND job_id = @JobID

PRINT 'END'


-- debug

42D05898-EDC3-4E2B-B5E7-AFD1BA8D23D4

(1 row(s) affected)

(1 row(s) affected)

(100 row(s) affected)
DO Job Schedules
This Job does not exist in sysjobschedules
ScheduleID = 92
This schedule_id does not exist in sysjobschedules

(0 row(s) affected)

(1 row(s) affected)

(0 row(s) affected)
END

Any help would be appreciated!



Post #1597966
Posted Thursday, July 31, 2014 6:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:48 AM
Points: 1,406, Visits: 588
Could you not just restore the old MSDB database to a dev/test/staging server somewhere and script out the jobs from there to import into your production server?

I think this is the quickest way if you can't just restore over the new production instance.


Regards,

Phil
Post #1598225
Posted Thursday, July 31, 2014 7:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:13 PM
Points: 592, Visits: 924
Don't make this harder then it needs to be. Either script out the "must have" jobs and then restore MSDB and reapply the jobs from the script or restore MSDB somewhere else and script the jobs from there or create an SSIS package to do a one time move using the "Transfer Jobs Task"



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1598264
Posted Thursday, July 31, 2014 10:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:52 AM
Points: 210, Visits: 378
Hi

Well, I would I have restored the whole msdb except I am not "in charge" of the DBS and the BODS who are seem to know less about SQL Servers than than webdevs like me it seems.

Also because of the existing MS Agent jobs in the new DB Server I couldn't just over-rule them (I was working a 35hour shift from my bedroom here!)

The script DID work apart from the FK problems at the bottom of the script. I did try numerous attempts at fixes but couldn't get them to work for some reason. So I just went into each job after and re-set the schedule which I had to do anyway for the Target option.

A search from the new table to old one to find jobs NOT IN the new MSDB helped me find which jobs to run then I just ran then one by one. set up the schedule and turned on the Target Server spec.

However seeing the script worked so well I am just wondering how I can fix those last two problems so that it could be used to
-rebuild jobs that have been accidentally deleted
-copy jobs from one sys to another by script
-and of course restore from backup MISSING jobs

The thing is that the old DB was MS 2005 the new MS 2012 so I am guessing the fact there were lots of jobs already IN there was because they were SYSTEM jobs?

So overwriting the MSDB table would have caused more problems wouldn't it?

Being able to put this in a loop with error handling and checks etc would be good to have seeing that no-one seems to backup MS-AGENT jobs so does anyone know how to turn the Target Local Server option on with SQL AND handle the FK issue?

I am guessing the Target feature was added after the script was made, maybe a 2008-2012 feature?

Thanks for your help etc - trust me if I could have overwritten the whole MSDB table without ringing up the server bod at 3 am I would have tried!
Post #1598568
Posted Tuesday, August 5, 2014 8:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:52 AM
Points: 210, Visits: 378
Any ideas on how to fix those two problems e.g the FK errors and Target Server without "backing up" the whole msdb server (which I don't have permission to anyway)

A script to copy out and add new jobs with all the right settings from a 2005 box for a 2012 box would be good as we have more old boxes and I have no doubt some will die in the near future.

Thanks
Post #1599763
Posted Tuesday, August 5, 2014 9:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 1,980, Visits: 3,347
Hi Rob, don't envy you this situation.
As far as I can see, the FK issues are because at no point does the script insert into the sysschedules table, this is required before inserting to sysjobschedules.

The local/multi server issue I'm not sure on, sorry. (Originating_)Server_id field in sysjobs, sysjobservers and sysschedules would be the first places I check though.

Cheers
Gaz
Post #1599811
Posted Monday, August 11, 2014 9:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:52 AM
Points: 210, Visits: 378
LOL I don't envy my whole existence! Let alone job which includes being off ill with a serious illness yet still called upon to "help" the person who manages the servers (but doesn't know anything about SQL or code - just networking and building computers etc) and the person he would normally have asked was not there.

So no I don't envy it. But it is now done.

I used that script, ignored the Fk errors and then just went into each job "of mine - let the other owners of their DBS sort their own jobs out!) and added in the scheduled and target server option.

It just seems like it should be such a simple thing to fix but I could find no mention of a TargetServers column in any of the tables. I thought if I just ignored any schedule inserts if a record already existed with that ID then that would solve the FK issue but it just meant no schedule part was added so I was obviously still missing something like someone said earlier.

I thought maybe the target servers thing was down to the MSDB DB knowing the data being inserted was from "another server somehow", there were some columns where you had to insert the server name which I replaced with @@servername but that didn't seem to fix it.

Anyway thanks and any ideas let me know!
Post #1601925
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse