February 2, 2021 at 12:00 am
Comments posted to this topic are about the item How to Restore an Agent Job that was Dropped
February 2, 2021 at 9:42 am
Wow...
I never thought about using a restore of msdb in this way. ??
I'm going to paste a link to this article in our operations wiki as a "howto"
Thanks for this article!
Theo
February 2, 2021 at 11:10 am
I have been looking at the code, and I think this could also be used to create rollout scripts for the jobs on new SQL servers from a template SQL server.
Do you have a script handy that saves a list of jobnames to a #JOBCREATION table? Or should I simply write a loop around this code? 🙂
February 2, 2021 at 11:24 am
I have been looking at the code, and I think this could also be used to create rollout scripts for the jobs on new SQL servers from a template SQL server. Do you have a script that saves a list of jobnames to a #JOBCREATION table? 🙂
Not sure if i get it right, but for the new jobs I would use dbatools. Dbatools can copy the SQL Agent jobs between instances as well.
February 2, 2021 at 1:37 pm
This is great - and thanks for sharing!
I did notice that if your job includes any TSQL steps, this will restore the step without any carriage returns. Which may/may not be an issue if you have any -- comments included in the step.
February 2, 2021 at 2:08 pm
This is great - and thanks for sharing!
I did notice that if your job includes any TSQL steps, this will restore the step without any carriage returns. Which may/may not be an issue if you have any -- comments included in the step.
You are very welcome.
Would you be able to provide an example of such job? I wonder wat are the differences between the script from SSMS of existing job and the one that has been restored using the script from the article.
February 2, 2021 at 2:40 pm
If the original TSQL step has tabs, CRs, and/or LFs, they won't be included in the new job. For example if your TSQL step contains:
UPDATE tablename
SET field1 = 'a'
WHERE field2 = 'b'
--AND field3 = 'c'
AND field4 = 'd'
It will be restored as:
UPDATE tablename SET field1 = 'a' WHERE field2 = 'b' --AND field3 = 'c' AND field4 = 'd'
The SQL statement functionality has been changed. I have handled this in the past using REPLACE but there may be better ways of doing it. At a minimum, I suspect that you will want to account for CHAR(9), CHAR(10), and CHAR(13).
February 2, 2021 at 3:04 pm
Thanks for that. You are right, the script in this form does not handle it well. I have a workaround similar to what you have suggested, but I need to test.
Thanks for this - it was a really good catch!
February 2, 2021 at 4:23 pm
In the original [comand] all the new lines \ carriage returns, tab exist.
If we change this line in step 3 of the script to replace it with \n, \r and \t
+ ', ' + '@command='+ 'N''' + COALESCE(command,'') + ''''
to this
+ ', ' + '@command='+ 'N''' + COALESCE(replace(replace(replace(command,char(10),'\n'),char(13),'\r'),char(9),'\t'),'') + ''''
We would get a job step in one line as well, but then we could try something like that after the job is restored, but this is directly updating the system table which I do not really like. ]
DECLARE @JobName NVARCHAR(MAX) = N'JobName_RESTORED';
UPDATE sjs
SET sjs.command = replace(replace(replace(command,'\n',char(10)),'\r',char(13)),'\t',char(9))
FROM dbo.sysjobs AS sj
JOIN dbo.sysjobsteps AS sjs ON sj.job_id = sjs.job_id
WHERE sj.name = @JobName;
I would prefer to "patch" the restored job afterwards with msdb.dbo.sp_update_jobstep. My initial test shows it works if we do not print out the step command into the grid.
It is a good exercise.
February 2, 2021 at 4:39 pm
Mr Pinal Dave to the rescue!
When we change that setting in SSMS (Query > Query Options... > Grid > Retain CR/LF on copy save )and run the script it works without shady updates on system objects.
Thank you Pinal!
February 2, 2021 at 5:42 pm
Here's a mostly point and click method involving little coding.
February 2, 2021 at 5:51 pm
Thank you for your input. That would work nicely in non-production environment I guess.
For live systems or those with busy jobs that could be difficult.
I like the discussion here, so many different approaches.
February 2, 2021 at 7:04 pm
The idea is to restore a production msdb on a non-production system so you can copy the job back to the production system. I've used this technique before.
You could actually do it right on the production system if you can afford to stop the agent for a few minutes.
February 3, 2021 at 12:50 am
Good article and thank you for taking the time to write and publish it.
As a bit of a sidebar, though, I just go to "Source Control" and grab a copy of the deleted job. This is especially important if you have a backup system that auto-magically deletes backup files from disks after the "archive bit" has been reset (indicating that they have been backed up to tape) after a certain number of days. I've had my fair share of people that have intentionally (or otherwise) deleted weekly, monthly, quarterly, semi-annual, and annual jobs only to find out weeks or months later that they need the job back. By then, the on-disk backups are long gone and making a trip to the tape warehouse simply takes too bloody long.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply