SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Homework – July 2018 – Create a SQL Agent Job

homework-clipart-homework-alert-free-images-at-vector-clip-art-onlineFor 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.


  • 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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...