How to Create Job using SQL Server Agent - SQL Server DBA Tutorial

In this video you will learn how to create a job using SQL Server Agent in SQL Server. It shows step by step process of creating the job using SQL Server Management Studio as well as T-SQL Script in SQL Server. It teaches you how to configure Job parameters such as names, job owner, categories and best practices of choosing Job owner while creating SQL Server Agent Jobs. It helps you to configure Job step parameters and will walk through different step options and how to use them under what circumstances. Also you will learn how to configure Job history options such as storing Job output to a file, loading into a table and including job execution history in Windows Application Log. It talks about best practices of storing Job history as well as Job retentions in SQL Server Agent.

Script used in the video to create SQL Server Agent Job.

USE [msdb]

GO

DECLARE @jobId BINARY(16)

EXEC msdb.dbo.Sp_add_job
  @job_name=N'Test',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=2,
  @notify_level_netsend=2,
  @notify_level_page=2,
  @delete_level=0,
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'TECHBROTHERS\clusteradmin',
  @job_id = @jobId OUTPUT

SELECT @jobId

GO

EXEC msdb.dbo.Sp_add_jobserver
  @job_name=N'Test',
  @server_name = N'TBSCLIENT\SQLMIRROR'

GO

USE [msdb]

GO

EXEC msdb.dbo.Sp_add_jobstep
  @job_name=N'Test',
  @step_name=N'AllDBView',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_fail_action=2,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0,
  @subsystem=N'TSQL',
  @command=N'select * from sys.databases',
  @database_name=N'master',
  @output_file_name=N'C:\Data\dbnames',
  @flags=2

GO

USE [msdb]

GO

EXEC msdb.dbo.Sp_update_job
  @job_name=N'Test',
  @enabled=1,
  @start_step_id=1,
  @notify_level_eventlog=0,
  @notify_level_email=2,
  @notify_level_netsend=2,
  @notify_level_page=2,
  @delete_level=0,
  @description=N'',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'TECHBROTHERS\clusteradmin',
  @notify_email_operator_name=N'',
  @notify_netsend_operator_name=N'',
  @notify_page_operator_name=N''

GO 

How to Create Job using SQL Server Agent