Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2638
  • Last Modified:

T sql script to alter SQL Server agent job if exists

Hi Experts,

  Could you please help me with T-Sql script to alter SQL Server agent job.  If the job exists, then I need to alter the job script ,to  add more steps and also edit the existing steps.  
I don't want to add it manually through wizard as we want to run the T-Sql scripts only (as we don't have access to production environments)

I need a sample Alter script . Your help is greatly appreciated in this regard.

Please Note : I don't want to drop and create the existing job in production as I would loose history of the job.

Regards,

Sreekanth.
0
n_srikanth4
Asked:
n_srikanth4
1 Solution
 
Anthony PerkinsCommented:
I would go through the steps with the wizard, just don't finish and instead save the SQL Scripts off so that you can optionally edit them and have them ultimately run in Production.

Let me know if you need help with that.
0
 
n_srikanth4Author Commented:
Hi Savant ,

     I could not go through the wizard , It will just add the step , but not generate any script .  Secondly , the  jobs are already created in Dev server , so i cannot undo the wizard.  So could you please walk through an example sample script please.

Regards,

Srk.
0
 
Anthony PerkinsCommented:
i would drop the step in development and start again using the wizard, but this time save the SQL scripts, before you finish.

P.S.  The name is Anthony (you will see it as it is just below where it says "Your Comment" and after "by:")
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Jim P.Commented:
My suggestion is that you create your own "history" table that you write the results to as the steps run. Basically as the first part of that particular step you insert the step name, time, server, db and other details at the beginning into the history table. Then at the end of the step you have a similar insert statement.

That way you aren't dependent on the job history table.

Then adding steps or replacing the script you don't lose the history. And if you build out the script for more stuff, you can just hand it off to the production team and move on.
0
 
David ToddSenior DBACommented:
Hi,

If you examine the script of the existing job, that script should have all the clues you need. That is, the job steps are added to the job one at a time. Of course you'll have to add logic that finds how many existing steps there are, and appends them. And don't forget to modify existing last step to go on to the next on success.

HTH
  David
0
 
n_srikanth4Author Commented:
good
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now