How to Restore an Agent Job that was Dropped

Question

How to Restore an Agent Job that was Dropped

February 3, 2021 at 4:07 am

This was removed by the editor as SPAM

Viewing 5 posts - 16 through 19 (of 19 total)

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

CC-597066 SSCommitted Points: 1637 More actions · Answer 1

Thanks for the script. I had a quick play today and the it failed on the first job I tried it on 🙁

It doesn't handle single quotation marks well in the body of the text so I had to go through and work out which ones should be double in the output it creates for sp_add_jobstep.

Your script also does a join on sysoperators so returns NULL for jobs that don't have email notification set up. That means that the command to create the job becomes NULL too as it is then concatenating strings with NULL.

Mikey Bronowski Old Hand Points: 357 More actions · Answer 2

Jeff Moden wrote:

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.

I agree 100% with that, having a copy of the jobs would be much easier and safer. Can you share how does your jobs deployment look like?

Do you do it automatically or just copy/paste the jobs?

This reply was modified 3 years, 10 months ago by Mikey Bronowski.

Mikey Bronowski Old Hand Points: 357 More actions · Answer 3

CC-597066 wrote:

Thanks for the script. I had a quick play today and the it failed on the first job I tried it on 🙁
It doesn't handle single quotation marks well in the body of the text so I had to go through and work out which ones should be double in the output it creates for sp_add_jobstep.

Good catch, I will have a look.

CC-597066 wrote:

Your script also does a join on sysoperators so returns NULL for jobs that don't have email notification set up. That means that the command to create the job becomes NULL too as it is then concatenating strings with NULL.

That has been updated and waiting for the publication.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Mikey Bronowski wrote:

Jeff Moden wrote:
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.
I agree 100% with that, having a copy of the jobs would be much easier and safer. Can you share how does your jobs deployment look like?
Do you do it automatically or just copy/paste the jobs?

I generate the thoroughly tested/signed off jobs from the Object Explorer Detail window to another window. I save that to an SVN folder for the purpose of holding all the things that need to be deployed during an installation. That code can be executed using any number of methods including the oldest of them all... a batch file that calls SQLCmd and uses the jobs.sql file I created as a part of the deployment package as input. You could also use an over-arching installation script that you'd open in SSMS and press the {f5} button on or, like I said, any other of a dozen other methods. Choose your flavor of the day. 😀

Of course, each job is also saved in SVN individually with versioning and any special purposes noted.

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