July 30, 2014 at 12:33 pm
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
July 30, 2014 at 1:32 pm
July 30, 2014 at 1:53 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 30, 2014 at 2:19 pm
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 *
FROMmsdb_from_neptunium.dbo.sysjobschedules
WHEREjob_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*
FROMmsdb.dbo.sysjobschedules
WHERE1=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!
July 31, 2014 at 6:38 am
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
July 31, 2014 at 7:41 am
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"
July 31, 2014 at 10:24 pm
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!
August 5, 2014 at 8:49 am
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
August 5, 2014 at 9:37 am
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
August 11, 2014 at 9:04 am
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!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply