Schedule a Job Using SQL Server Management Studio

Jobs are nothing but a feature of the Microsoft SQL server that allows you to automate the execution of one more SQL operation in your daily routines or in response to specific tasks. We can create jobs using SQL server management (SSMS) or Transact-SQL. In this session, we are going to create a job using SSMS.

1.Expand object explorer, right-click on SQL server agent, create a new job, then open a new window as a new job that contains pages as general, steps, etc.

2. In general, describe the name and description of the job.

3. Go to the next page to create steps for when the job will be executed. You can create more steps and align them one by one.

4. Create a step and write the command that you want to execute against the database.

5. Next, we have to schedule the job and mention the date and time. After that, we have to go for alerts if you want any alerts when the error is encountered.

6. Notifications when the job fails, when the job succeeds, or when the job is completed can be enabled in the notification page

7. The final tab is the Targets tab which shows you the options of the target server. You can also select multiple target servers. 

To create job using transact SQL also, create a new query and given below steps using query to execute.

• sp_add_job (Transact-SQL)
• sp_add_jobstep (Transact-SQL)
• sp_add_schedule (Transact-SQL)
• sp_attach_schedule (Transact-SQL)
• sp_add_jobserver (Transact-SQL)

USE msdb ;  
GO  
EXEC dbo.sp_add_job  
    @job_name = N'Weekly Sales Data Backup' ;  

GO

This the Transact SQL Query for add job and we can run the query such as add job steps, add schedule, add notifications and server.