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.

[code]
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

[/code]