SQL Server agent can be used to create JOBS which can be scheduled to execute automatically. SQL Server Agent uses SQL Server to store job information. Jobs can have one or more job steps. Each step contains its own task.Each action in a job is a job step.We can schedule the job based on need as daily, weekly, monthly, hourly etc
For example, Let us create a job to back up a database automatically.
Steps to create Job in SQL Server Agent
1. Expand SQL Server Agent in Object Explorer.(If SQL Server Agent is stopped ,right click and start it)
2.Right click Job->New job.
3.Fill the Name field with a name for your job,Give description.
4. On the left side of the New Job window, you’ll see a Steps icon under the Select a page.Next add the individual steps for your job. Click the New button to create a new job step and you will see the New Job Step window.
- Use the Step Name textbox to provide a name for the Step. select the database where the job has to be applied.
- Use the Command textbox to give the Transact-SQL syntax corresponding to the desired action for this job step. Once you have completed entering the command, click the Parse button to verify the syntax.
- Validate the syntax, click OK to create the step.
5.Schedule the job by clicking the Schedule icon in the Select a Page.Then schedule new job.
6.Create alerts for your job by Giving alert name and also select the database.Also select the type of alert and severity of your alert
7.Specify the actions to be performed when the job completes or when job fails.
Note :All the above steps can be performed by TSQL (with out the GUI) using follow stored procedures
sp_add_job to create a job.
sp_add_jobstep to create one or more job steps.
sp_add_schedule to create a schedule.
sp_attach_schedule to attach a schedule to the job.