Category: Technology

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