Friday, July 3, 2009

Creating SQL Job using T-SQL Statements

The following shows how to create and add scheduled SQL jobs in MS SQL Server.

EXECUTE msdb.dbo.sp_add_job
@job_name = 'Database Backup',
@enabled = 1,
@owner_login_name = 'sa'

EXECUTE msdb.dbo.sp_add_schedule
@schedule_name = 'Daily database backup',
@enabled = 1,
@freq_type = 4, -- daily
@freq_interval = 1, -- daily
@active_start_time = '180000'

EXECUTE msdb.dbo.sp_attach_schedule
@job_name = 'Database Backup',
@schedule_name = 'Daily database backup'

EXECUTE msdb.dbo.sp_add_jobserver
@job_name = 'Database Backup',
@server_name = 'ServerName'

EXECUTE msdb.dbo.sp_add_jobstep
@job_name = 'Database Backup',
@step_name = 'Backup database on daily basis',
@subsystem = 'TSQL',
@command = 'BACKUP DATABASE TestDatabase TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\TestDatabase.bak''