T-SQL script to create SQL Job for daily database backup

This script will create an SQL Server Agent job, which will take daily database backup to a folder with date appended in the filename.


DECLARE @job varchar(100) = 'Backup_testdb_daily' -- Name of Job
DECLARE @db varchar(100) = 'testdb' -- DB to backup
DECLARE @bakfile varchar(100) = 'd:\_temp\' + @db -- Backup file path

DECLARE @date varchar(8) = '20180720' -- Job Start date
DECLARE @time varchar(8) = '135400' -- Job run time. Eg: Run At 23rd hour

--------------------------------------------------------------------

DECLARE @cmd varchar(200) = CONCAT ('DECLARE @bakfile varchar(200) = ''' , @bakfile , ''' + ''_'' + convert(varchar(100),GetDate(),112) + ''.bak'';') +

CONCAT('BACKUP DATABASE ', @db, ' TO DISK = @bakfile');

USE msdb

EXEC dbo.sp_add_job
@job_name = @job;

EXEC sp_add_jobstep
@job_name = @job,
@step_name = 'Backup database',
@subsystem = 'TSQL',
@command = @cmd

EXEC sp_add_jobschedule
@job_name = @job,
@name = 'DB Backup Schedule',
@freq_type = 4, -- daily
@freq_interval = 1,
@active_start_date = @date,
@active_start_time = @time

EXEC dbo.sp_add_jobserver
@job_name = @job,
@server_name = @@SERVERNAME