Hello Everyone in this post we are going to cover how you can set automated backup functionality in a Microsoft SQL Server with Quick and Easy Steps.

Create Stored Procedure
Create Backup Job and Execute
Delete/Modify Jobs

1. Stored Procedure backupDB

CREATE procedure [dbo].[backupDB]
as begin
declare @path varchar(1000);
set @path=’G:\backups\backup_’+CONVERT(CHAR(10), GETDATE(), 121)+’.bak’;
BACKUP DATABASE [DATABASENAME] to DISK=@path;
end

2. Create Jobs for automated backups

EXEC msdb.dbo.sp_add_job
@job_name = N’BackupDBJob’,
@enabled = 1,
@description = N’Procedure execution every day’ ;

EXEC msdb.dbo.sp_add_jobstep
@job_name = N’BackupDBJob’,
@step_name = N’Run Procedure1′,
@subsystem = N’TSQL’,
@command = ‘exec sahakaripro.dbo.backupDB’;

EXEC msdb.dbo.sp_add_schedule
@schedule_name = N’Everyday schedule1′,
@freq_type = 4, — daily start
@freq_interval = 1,
@active_start_time = ‘230000’ ; — start time 23:00:00

EXEC msdb.dbo.sp_attach_schedule
@job_name = N’BackupDBJob’,
@schedule_name = N’Everyday schedule1′ ;

EXEC msdb.dbo.sp_add_schedule
@schedule_name = N’Everyday schedule2′,
@freq_type = 4, — daily start
@freq_interval = 1,
@active_start_time = ‘140000’ ; — start time 23:00:00

EXEC msdb.dbo.sp_attach_schedule
@job_name = N’BackupDBJob’,
@schedule_name = N’Everyday schedule2′ ;

EXEC msdb.dbo.sp_add_jobserver
@job_name = N’BackupDBJob’,
@server_name = @@servername ;

3. DONE