How to Restore an Agent Job that was Dropped

  • Comments posted to this topic are about the item How to Restore an Agent Job that was Dropped

  • 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

  • Theo Ekelmans wrote:

    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

    Thanks Theo. I am glad you find it useful ๐Ÿ™‚

  • 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? ๐Ÿ™‚

  • Theo Ekelmans wrote:

    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.

  • 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.

  • Bind wrote:

    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.

  • 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).

  • 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!

  • 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.

  • Mr Pinal Dave to the rescue!

    https://blog.sqlauthority.com/2016/06/03/sql-server-maintain-carriage-return-enter-key-ssms-2016-copy-paste/

    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!

     

  • Here's a mostly point and click method involving little coding.

    1. Restore msdb to a non-production server as msdb_backup
    2. Stop the agent on that server
    3. Swap the msdb and msdb_backup database names
    4. Use SSMS to extract the "create" DDL for the dropped job to a query window
    5. Connect the query window to the original server and run the extracted DDL
    6. On the non-prod server swap the msdb database names back to their original names.
    7. drop msdb_backup
    8. restart agent

     

  • 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.

  • 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.

  • 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply