How to Create a Job in SQL SERVER?

How to Create a Job in SQL SERVER

A job is the core container for a single logical task, such as backing up a database. That task can contain one or more job steps. The job may include notifications (for example, email the DBA if a job fails), schedules (when you want the job to run), and may even run on multiple systems.

Now we are going to create a job that will take the backup of our database that is TestDb.

To create a new job, open SSMS and connect to your instance of SQL Server. Expand the SQL Server Agent node, and then right click on Jobs. Select the option to create a new job ("New Job…") as shown in below Figure:

When you will click on new job, a new windows will come up as below; A job has a number of components,  In General, you have to fill up the certain field like Name(name of job), owner, category, description (give a nice description so that later you can better know why this job was created). Now we will  name the job "Back Up DBTest Database". This will be the name used to refer to the job in the SSMS.

Job Steps

A job within SQL Server Agent is made up of at least one job step. A job step is defined by the type of action you want to perform in that particular job, and it includes the ability to run the following job subsystems:

·         ActiveX

·         Operating System (CMDExec)

·         PowerShell

·         A variety of replication tasks

·         SQL Server Analysis Services (SSAS) Command (i.e. XML/A)

·         SQL Server Analysis Services (SSAS) Query (MDX)

·         SQL Server Integration Services (SSIS) Package (DTS Package in SQL Server 2000)

·         Transact-SQL Script (T-SQL)

The majority of your jobs will be using the last type of job step, T-SQL.

Now Click the "New" button, and the "New Job Step" dialog will come up:

Give the Job Step a useful name – For this we are going to use "Backup". The job step type will be T-SQL, as we will use a BACKUP command for our database backup. We can ignore "Run as", as by default we will run the job step as the owner of the job. Now in command you can paste the below TSQL code :

BACKUP DATABASE master

TO DISK='C:\LOG\DBTest.bak' WITH INIT

When complete, Click OK to create the job step. Now it will look like as below:

Now the next tab is job schedule. Let’s check what is this.

Job Schedules

Job Schedules control when a job will actually be started. There are four types of job schedules:

Start automatically when SQL Server Agent starts

Start whenever the CPUs become idle

Recurring

One Time

For our job, we want the database to be backed up weekly. So, click on the Schedules tab, and select New to create a new job schedule. Name the job schedule. We'll call this schedule

"Weekly backups".

Click OK when you've named the schedule, then you can see the schedule list for that particular job as below:

Click OK again to finish the job creation as below: You can create notification and alerts for this job but we will be discussing it in another article.

Run the Job you just created 

Now we have created our backup job, now it will be good to run the job to verify we did everything correctly. Even we scheduled this job to run weekly, we can always run a job manually on demand. To do that, In SSMS, under the Jobs folder, now you can see your job. Right-click on the Job, and select "Start Job at Step…", as in figure 1.8. The job will run, and now your master database is backed up.

Right-click on the Job, and select "Start Job at Step…", as in below figure. The job will run, and now your master database is backed up.

After successful completion you will get dialog box as below:

Now in C drive you can verify the backup file and it is present.

So this way you can create a job to automate your works in SQL Server.

Thanks!!!!!