Build a Backup Strategy to MSDE

				
--**************************************
-- Name: Build a Backup Strategy to MSDE
-- Description:This code permits to make a backup strategy using osql.
-- By: Jairo Abraham Bernal Villanueva
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1099&lngWId=5--for details.--**************************************

use master
go
DECLARE @ServerName		varchar(400)
DECLARE @DatabaseName	varchar(400)
DECLARE @BackupPath		varchar(400)
DECLARE @TSQLCommand	varchar(2000)
DECLARE @FullBackupJob	varchar(2000)
DECLARE @DiffBackupJob	varchar(2000)
DECLARE @BackupDevice	varchar(2000)
DECLARE @FullStepName	varchar(2000)
DECLARE @DiffStepName	varchar(2000)
DECLARE @FullSchName	varchar(2000)
DECLARE @DiffSchName	varchar(2000)
SET @ServerName			= HOST_NAME()
SET @DatabaseName		= 'Database name'
SET @BackupPath			= 'Unit:\CompletePath\' + @DatabaseName + '.bk!'
SET @FullBackupJob		= @DatabaseName + '_Weekly_Full_Backup'
SET @DiffBackupJob		= @DatabaseName + '_Daily_Diff_Backup'
SET @BackupDevice		= @DatabaseName + '_Backup'
SET @FullStepName		= @DatabaseName + '_Full_Backup'
SET @FullSchName		= 'Schedule_' + @DatabaseName + '_Weekly_Full_Backup'
SET @DiffSchName		= 'Schedule_' + @DatabaseName + '_Daily_Diff_Backup'
SET @DiffStepName		= @DatabaseName + '_Diff_Backup'
-- Delete the device if exists
IF EXISTS (SELECT name FROM master.dbo.sysdevices WHERE name = @BackupDevice) 
BEGIN
	EXEC sp_dropdevice @BackupDevice
END
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @FullBackupJob)
BEGIN
	EXEC msdb.dbo.sp_delete_job @job_name = @FullBackupJob
END
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @DiffBackupJob)
BEGIN
	EXEC msdb.dbo.sp_delete_job @job_name = @DiffBackupJob
END
EXEC sp_addumpdevice 'disk', @BackupDevice, @BackupPath
EXEC msdb.dbo.sp_add_job @job_name = @FullBackupJob, 
	@owner_login_name = 'sa'				-- Creates the job of Full Backup
EXEC msdb.dbo.sp_add_jobserver @job_name = @FullBackupJob, 
	@server_name = @ServerName
SET @TSQLCommand = 'BACKUP DATABASE [' + @DatabaseName + '] TO [' + @DatabaseName + '_Backup] WITH INIT , NOUNLOAD , NAME = N''' + @DatabaseName + '_Weekly_Full_Backup'', NOSKIP , STATS = 10, NOFORMAT' 
EXEC msdb.dbo.sp_add_jobstep @job_name = @FullBackupJob,
@step_name = @FullStepName,
@subsystem = 'TSQL',
@command = @TSQLCommand, 
@retry_attempts = 5,
@retry_interval = 5
EXEC msdb.dbo.sp_add_jobschedule @job_name = @FullBackupJob, 
	@name = @FullSchName,					-- Creates the Job Schedule
	@freq_type = 8,						-- Weekly
	@freq_interval = 2,					-- Monday
	@freq_recurrence_factor = 1,				-- Every week
	@active_start_time = 083000				-- HHMMSS (08:30:00 AM)
	
EXEC msdb.dbo.sp_add_job @job_name = @DiffBackupJob, 
	@owner_login_name = 'sa'				-- Creates the job of Differencial Backup
EXEC msdb.dbo.sp_add_jobserver @job_name = @DiffBackupJob, 
	@server_name = @ServerName
	
EXEC msdb.dbo.sp_add_jobschedule @job_name = @DiffBackupJob, 
	@name = @DiffSchName,					-- Creates the Job Schedule
	@freq_type = 8,						-- Weekly
	@freq_interval = 60,					-- Tuesday, Wednesday, Thursday and Friday
	@freq_recurrence_factor = 1,				-- Every week
	@active_start_time = 083000				-- HHMMSS (08:30:00 AM)
	
SET @TSQLCommand = 'BACKUP DATABASE [' + @DatabaseName + '] TO [' + @DatabaseName + '_Backup] WITH NOINIT , NOUNLOAD , DIFFERENTIAL , NAME = N''' + @DatabaseName + '_Daily_Diff_Backup'', NOSKIP , STATS = 10, NOFORMAT '
EXEC msdb.dbo.sp_add_jobstep @job_name = @DiffBackupJob,
@step_name = @DiffStepName,
@subsystem = 'TSQL',
@command = @TSQLCommand,
@retry_attempts = 5,
@retry_interval = 5