Query to fetch the desired SQL agent Job SCRIPT

Question

question

askssc asked Sep 20, '11

Query to fetch the desired SQL agent Job SCRIPT

Hi Everyone, My task is i need to schedule a SQL agent job which will fetch one of the scheduled job SCRIPT in a text file as OUTPUT.Can any one help me here. Thanks in Advance. Regards, Paveeen

script job

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2011-09-20T23:16:20Z

Fatherjack answered Sep 20, '11

can you give us any more details please? Are you having trouble creating the schedule? Is the script you want in a text file already or is the problem putting the script into a file? When you say "one of the scheduled job script" how many are there and where are they stored? What have you done so far, what error are you getting?

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askssc commented · Sep 20, 2011 at 11:31 PM

Hi, More simpler i can tell you like Am having SQL job "Testxyz".Now i need to backup this SQL job everyweek.How can i do that with out taking MSDB backup.

0 ·

Fatherjack ♦♦ commented · Sep 20, 2011 at 11:47 PM

the way to back up SQL Agent jobs is to back up MSDB, what do you want to do that means this method is not suitable?

0 ·

askssc commented · Sep 21, 2011 at 11:06 PM

Hi Jack, I am having 'n' number of jobs in our SQL instance,if i will take MSDB backup,i can get the job details only after MSDB restore is done.Right? But my requirement is to fetch the Individual JOB script everyweek as a backup plan.How can i do this.

0 ·

Fatherjack ♦♦ commented · Sep 22, 2011 at 01:40 AM

Yes, if you lose your main server then you would need to restore MSDB from that server to somewhere else to retrive the job settings. However, if you take just the job scripts you will be missing a lot of important information about the jobs such as their schedules, step order, alerts, notifications, descriptions, database context etc etc. Having just the scripts will not let you rebuild the jobs more quickly on a replacement server. If you are looking for resilience for your jobs then I would suggest reviewing PowerShell to generate a create script for your jobs that you can schedule daily.

0 ·

question