For this month’s homework let’s try something a bit different. SQL Agent jobs are a very powerful tool frequently used for backups and maintenance but can also be used for batch processes, exports, etc. To be fair there is better job scheduling software out there but SQL Agent is quite good and as either a DBA or a developer you are likely to run into it at some point. So let’s give it a shot.

Specs:

• The job should have 4 steps.
• The first step should be a T-SQL script of some kind and should continue on to the next step when complete.
• The second step should be a different kind of step. I’d recommend either a CmdExec or Powershell step but it could also just as easily be an SSIS package or something else. Again continue on to the next step if successful.
• The 3rd step will print “Complete”, or if you feel like it set it up to send a DBMail message that the job completed successfully. Upon successful completion of this step, the job will end successfully.
• The fourth step will print “Failed”, or again if you feel like it send a DBMail message that the job failed. Then when this step finishes (successfully or not) mark the job as failed.
• Each step should write the results of the step to the job history.
• The job should run on every Saturday evening at 11pm. It should also run on the third Wednesday of each month at 6am.
• The owner of the job should not be you and should not be sa.
• Make sure the job does not actually run, but that it is still scheduled to run as above.

 
There is a LOT more here to play with, but we will give some of that a shot at a later date.