Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We just had an issue where we lost msdb on one of our servers along with all of the jobs which we created for a multitude of databases. The fact that we didn't have a backup of msdb is one issue, which I won't get into, but it did make me think about how we develop the jobs that go along with our databases.

What are the different ways that one could manage jobs? I guess ideally they would be in source control, but I've yet to find any tools which do a good job of managing large systems of databases with their associated jobs.

share|improve this question

migrated from stackoverflow.com Feb 12 '13 at 19:39

This question came from our site for professional and enthusiast programmers.

    
As for source control you can script the job by right clicking it and clicking script job as... Then you can save the script as a text file and put it in source control. It may be tedious with a large number of jobs. –  darin Feb 12 '13 at 18:25
    
@darin not really, you can script multiple jobs by using Object Explorer Details > Select All > Right-click > most of the context menu options are same as if you'd right-clicked an individual job. –  Aaron Bertrand Feb 12 '13 at 18:39
    
You can use the Object Explorer Details page to script out all jobs on a server in one fell swoop. .. EDIT: BEEEEERRRRRTRAAAAND! –  Kyle Hale Feb 12 '13 at 18:41

3 Answers 3

So let us begin with the obvious: Back up your msdb database. Get a maintenance plan going, back it up regularly, and let us never lose our jobs or job history again.

This also leads to the second part of my answer which is: jobs and their steps are just rows in a table. They have steps which invoke subsystem commands using arguments provided in the job step row. The only way ultimately to "manage" the jobs is by managing the SQL that creates / modifies / deletes them. And because there's surprisingly little customization for a given job step, most people have just stuck to static SQL scripts for deployment.

Build your job in dev, test it, script it out to SQL, store it as a post-deployment file in your database project (your databases are in source control ... right?) and you've got the path of least resistance to SQL Agent job management.

Maybe someday in the future there will be ways to "compare" jobs in a project to a target server job a la Schema Compare - again, it's just SQL, shouldn't be too hard to reconcile - and then publish changes (even bidirectionally!) but for now you're just stuck with scripting.

share|improve this answer

There are a couple ways to approach this:

  • Maintain your agent jobs in source control by scripting out the entire job creation and storing that script. The script can be stored in your version control repository or maintained with third party tools such as Red Gate Source Control.
  • Keep your agent jobs themselves as minimal, only calling stored procedures. Then version control the stored procedures so that the Agent Job is maintained only as a scheduling/execution utility.
  • Maintain full documentation on your jobs and schedules that is inventoried on a periodic basis. I recommend this free script that I've used in the past for creating my Agent job run book.

Ultimately, what you use agent jobs for will determine the best approach. In most of my environments, Agent jobs are used strictly for maintenance tasks. We have canned scripts and stored procedures for these tasks, so we maintain one set of scripts to deploy the jobs. In the event of total msdb failure, I simply redeploy all my jobs (along with other management objects) from my script repository. If your jobs are more unique to the server, you'll need to maintain more specific job documentation.

share|improve this answer

We build SQL Agent jobs in the SQL Server Management Studio UI on a developer machine. Then, when we are done, we script it out and delete the job. We add the script to source control and include it in our product's install program. Because it is in source control, we can manage how updates are created and released.

Once installed in a production system, nobody touches the job by hand. Any change to the job must be coded in the script, checked in, and tested first before delivering to the customer system.

(Experienced customers do sometimes modify schedules or setup alerts or the like.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.