Jobs and Maintenance Plans

Jobs are good. Especially mundane, repetitive, unnoticed-when-they're-done-well jobs that you don't have time or the will to do yourself. There are certain tasks that need to be done on a regular basis, tasks which you can automate to be executed by SQL Server Agent, consistently and reliably.

A Job can have many steps, running tasks sequentially.
A Maintenance Plan is much the same.

Both Jobs and Maintenance Plans can send you emails on success, failure or completion. You'll probably find you favour one, but use both. Personally, I favour Maintenance Plans when I want to do a bunch of things, and Jobs when I want to do just one.

To create a Maintenance Plan

• Expand Management, right-click Maintenance Plans and select New Maintenance Plan, giving it a name
• From the toolbox on the left, drag whatever tasks you want to include on to the blank designer surface on the right
• Right-click and Edit to customise each Task.
• You can attach as many instance of Notify Operator Task as you like
• You'll notice each task you have selected has an arrow hanging off it. Drag these to other tasks to create a flow
• If you have 2 tasks connected, you can right-click an arrow and select Success, Failure or Completion
• Above the designer surface you can define a Schedule, and add Subplans
• Once completed, it is good practice to right-click the Maintenance Plan and Execute to test the Plan

To create a Job

• Expand SQL Server Agent, right-click Jobs and select New Job
• Name and Categorise (optional) your Job
• Click Steps and add the Steps you want to execute, defining what happens On Success or On Failure
• Click Schedules and define when and how often your Job should run
• Alerts can be defined
• Notifications can be setup, assuming you have setup Database Mail
• Once completed, it is good practice to right-click the Job and Start Job at Step... to test the job functions as expected