Creating Job in SQL Server 2008

SQL Server Job:
A job is a specified series of actions that SQL Server Agent performs. Its internal setup consists of a windows service, that is used to execute jobs and a set of SQL Server tables that has the metadata about these jobs. We use jobs to define an administrative task that can be run one or more times and monitored for success or failure. If the Job encounters any problem, SQL Server Agent can record the event and notify you. A job can run on one local server or on multiple remote servers. In this article, I will demonstrate how to create a SQL Server Job.

Note: Before create a SQL Server Job, make sure that your SQL Server Agent is running.

In below example I have a database named “MyTestDB”. I want to backup this database from server, from Monday to Friday on a specific time. So here using SQL Server Agent Job I can automate this task. Not only for database backup, But we can automate a numerous types of task using SQL Server Agent Job.

Steps To Create SQL Server Job:

Step 1:
Click on SQL Server agent and right-click on “Jobs” and click on “New Job”.

1

Step 2:
There are many steps to create a Job, so let’s go one by one. In the first Step “General” provide a job name and description and select the database owner and click “Ok”.

2

Step 3:
Now click on second Step “Steps”. Click on the “New” button for a new step and provide a step name, type and select database and write a command to save in a location and then click “Ok”.

Note: In the command area, you can write any SQL query or stored procedures according to your requirement for execute in this job.

3

4

Step 4:
Now click on the third step “Schedules”. Click on the “New” button for a new schedule and provide name, schedule type, frequency and click “Ok”.

Note: For this example my job schedule will be weekly at 1 AM.

5

6

Step 5:
Now click on the fourth step “Alerts”. Click on the “Add” button and provide alert name, database name and click “Ok”.

Note: An alert is an automatic response to a specific event. For example, an event can be a job that starts or system resources that reach a specific threshold. You define the conditions under which an alert occurs.

7

8

Step 6:
Now click on the fifth step “Notifications” and select how you want notifications. Here you have option for sending email when job complete or it runs successfully or it fails. Chose the appropriate option as per your requirement and click “Ok”.

9

Step 7:
The final step is the “Targets”, that shows you the options of the target server, you can also select multiple target servers.

10

And here the new job has created in SQL Server Agent.

11

Leave a Reply