/*****************************************************************************
 * Create backup and maintenance jobs for specified database
 *****************************************************************************
 * Modify variables for the database to be backed up, backup location, specify
 * time, and whether or not transaction logs should be backed up.
 *****************************************************************************/
declare
	@xDatabase	varchar(50),
	@xOperator	varchar(50),
	@xOperatorEmail	varchar(50),
	@xOperatorComp	varchar(50),

	@xJobName	varchar(100),
	@xJobDesc	varchar(100),
	@xCommand	varchar(256),
	@xBackup	varchar(100),
	@xDevName	varchar(55),
	@xDevFile	varchar(100),
	@xReturn	int,
	@xJobID		uniqueidentifier,
	@xTime		int,
	@xFrequency	int,
	@xTranBack	bit,
	@xSuccess	int,
	@xIndex		int,
	@xDBCC		int

declare db_cursor cursor for
	select
		name
	from
		master.dbo.sysdatabases
	where
		name not in('master','msdb','model','tempdb')
for read only

open db_cursor

fetch next from db_cursor into @xDatabase

use msdb

/* Initialize Variables */
set @xOperator = 'Ima Deebeeay'
set @xOperatorEmail = 'dba@dba4life.com'
set @xOperatorComp = '192.168.0.168'

-- Modify for target database
--set @xDatabase = 'COG'

-- Modify for backup location
set @xBackup = 'g:\mssql\backup'

-- Modify for backup time (HHMMSS, 24 hour clock)
set @xTime = 190000

-- Modify to include transaction log backups
set @xTranBack = 0

-- Modify for transaction log backup frequency (hours)
set @xFrequency = 1

-- Modify for weekly index rebuild
set @xIndex = 0

-- Modify for weekly database checks
set @xDBCC = 0

/* Check if operator needs to be created */
if((select count(1) from msdb..sysoperators where [name] = @xOperator)= 0)
begin
	exec @xReturn = sp_add_operator
		@xOperator,
		1,		-- Enabled
		@xOperatorEmail,
		null,		-- Pager
		090000,		-- Weekday	Start Time
		180000,		-- 		End Time
		090000,		-- Saturday
		180000,
		090000,		-- Sunday
		180000,
		127,		-- Valid days
		@xOperatorComp,
		null		-- Category name

	if(@xReturn != 0)
	begin
		print 'Error Creating operator'
	end
end

while(@@fetch_status = 0)
begin
	/* Change recovery mode to full for transaction log backups */
	if(@xTranBack = 1)
	begin
		set @xCommand = 'alter database ' + @xDatabase + ' set recovery full'
		exec(@xCommand)
	end
	
	/* Create Jobs */
	
	-- Daily full backup
	begin
		set @xJobID = null
		set @xJobName = @xDatabase + ' backup full'
		set @xJobDesc = 'Daily backup for ' + @xDatabase
	
		if(right(@xBackup, 1) = '\')
		begin
			set @xBackup = substring(@xBackup, 1, len(@xBackup) - 1)
			print 'ID-10T: Don''t put a trailing backslash in the backup location!'
		end
	
		-- Create backup device, full
		set @xDevName = @xDatabase + '_full'
		set @xDevFile = @xBackup + '\' + @xDevName + '.bak'
		if((select count(1) from master..sysdevices where name = @xDevName) = 0)
		begin
			exec @xReturn = sp_addumpdevice
				'disk',
				@xDevName,
				@xDevFile,
				2,		-- Controller Type (unused)
				null
		
			if(@xReturn != 0)
			begin
				print 'Error creating full backup device'
			end
		end
	
		-- Create backup device, transaction
		if(@xTranBack = 1)
		begin
			set @xDevName = @xDatabase + '_tran'
			set @xDevFile = @xBackup + '\' + @xDevName + '.bak'
	
			if((select count(1) from master..sysdevices where name = @xDevName) = 0)
			begin
				exec @xReturn = sp_addumpdevice
					'disk',
					@xDevName,
					@xDevFile,
					2,		-- Controller Type (unused)
					null
	
				if(@xReturn != 0)
				begin
					print 'Error creating transaction backup device'
				end
			end
		end
	
		-- Job
		exec @xReturn = sp_add_job
			@xJobName,
			1,		-- Enabled
			@xJobDesc,
			1,		-- Start Step ID
			'Database Maintenance',
			null,
			'sa',
			2,		-- Eventlog
			2,		-- Email
			2,		-- Net Send
			0,		-- Page
			@xOperator,	-- Operator, Email
			@xOperator,	-- Operator, net send
			null,		-- Operator, Page
			0,		-- Delete?
			@xJobID output
	
		if(@xReturn != 0)
		begin
			print 'Error Creating daily full backup job'
		end
	
		-- Job Schedule
		exec @xReturn = sp_add_jobschedule
			@job_id = @xJobID,
			@name = @xJobName,
			@enabled = 1,
			@freq_type = 4,			-- Frequency Type, Daily
			@freq_interval = 1,		-- Interval
			@freq_recurrence_factor = 1,
			@active_start_time = @xTime
	
		-- Job Step, Full Backup
		set @xCommand = 'backup database ' + @xDatabase + ' to ' + @xDatabase + '_full with init' + char(13) + char(10) + 'go'
	
		-- Determine next step action
		if(@xTranBack = 1)
		begin
			set @xSuccess = 3	-- Goto next step
		end
		else
		begin
			set @xSuccess = 1	-- Quit with success
		end
	
		exec @xReturn = sp_add_jobstep
			@job_id = @xJobID,
			@step_name = 'backup',		-- Step Name
			@subsystem = 'TSQL',		-- Subsystem
			@on_success_action = @xSuccess,
			@command = @xCommand
	
		if(@xReturn != 0)
		begin
			print 'Error Creating daily full backup job, step 1'
		end
	
		-- Job Step, Transaction Log
		if(@xTranBack = 1)
		begin
			set @xCommand = 'backup transaction ' + @xDatabase + ' to ' + @xDevName + ' with init' + char(13) + char(10) + 'go'
	
			exec @xReturn = sp_add_jobstep 
				@job_id = @xJobID,
				@step_name = 'transaction log',	-- Step Name
				@subsystem = 'TSQL',		-- Subsystem
				@command = @xCommand
	
			if(@xReturn != 0)
			begin
				print 'Error Creating daily full backup job, step 2'
			end
		end
	
		exec @xReturn = sp_add_jobserver @job_id = @xJobID, @server_name = @@servername
	
		if(@xReturn != 0)
		begin
			print 'Error Adding backup job'
		end
	end
	
	-- Daily Transactional
	if(@xTranBack = 1)
	begin
		set @xJobID = null
		set @xJobName = @xDatabase + ' backup tran'
		set @xJobDesc = 'Daily transaction log backup for ' + @xDatabase
	
		-- Job
		exec @xReturn = sp_add_job
			@xJobName,
			1,		-- Enabled
			@xJobDesc,
			1,		-- Start Step ID
			'Database Maintenance',
			null,
			'sa',
			2,		-- Eventlog
			2,		-- Email
			2,		-- Net Send
			0,		-- Page
			@xOperator,	-- Operator, Email
			@xOperator,	-- Operator, net send
			null,		-- Operator, Page
			0,		-- Delete?
			@xJobID output
	
		if(@xReturn != 0)
		begin
			print 'Error Creating daily transaction log job'
		end
	
		-- Job Step, Transaction Log Backup
		set @xCommand = 'backup transaction ' + @xDatabase + ' to ' + @xDevName + ' with noinit' + char(13) + char(10) + 'go'
	
		exec @xReturn = sp_add_jobstep
			@job_id = @xJobID,
			@step_name = 'daily transaction log',	-- Step Name
			@subsystem = 'TSQL',			-- Subsystem
			@command = @xCommand
	
		if(@xReturn != 0)
		begin
			print 'Error Creating daily transaction job step'
		end
	
		-- Job Schedule
		exec @xReturn = sp_add_jobschedule
			@job_id = @xJobID,
			@name = @xJobName,
			@enabled = 1,
			@freq_type = 4,			-- Frequency Type, Hourly
			@freq_interval = 1,		-- Interval
			@freq_subday_type = 8,		-- Hours
			@freq_subday_interval = 2,
			@active_start_time = 060000,
			@active_end_time = 180000
	
		if(@xReturn != 0)
		begin
			print 'Error Creating daily log backup schedule'
		end	
	
		exec @xReturn = sp_add_jobserver @job_id = @xJobID, @server_name = @@servername
	
		if(@xReturn != 0)
		begin
			print 'Error Adding transaction job'
		end
	end
	
	-- Weekly Maintenance, Database
	if(@xDBCC = 1)
	begin
		set @xJobID = null
		set @xJobName = @xDatabase + ' maint dbcc'
		set @xJobDesc = 'Weekly maintenance for ' + @xDatabase
	
		-- Job
		exec @xReturn = sp_add_job
			@xJobName,
			1,		-- Enabled
			@xJobDesc,
			1,		-- Start Step ID
			'Database Maintenance',
			null,
			'sa',
			2,		-- Eventlog
			2,		-- Email
			2,		-- Net Send
			0,		-- Page
			@xOperator,	-- Operator, Email
			@xOperator,	-- Operator, net send
			null,		-- Operator, Page
			0,		-- Delete?
			@xJobID output
	
		if(@xReturn != 0)
		begin
			print 'Error Creating dbcc job'
		end
	
		-- Job Step, Transaction Log Backup
		set @xCommand = 'dbcc checkdb(' + @xDatabase + ')'
	
		exec @xReturn = sp_add_jobstep 
			@job_id = @xJobID,
			@step_name = 'daily transaction log',	-- Step Name
			@subsystem = 'TSQL',		-- Subsystem
			@command = @xCommand
	
		if(@xReturn != 0)
		begin
			print 'Error Creating dbcc job step'
		end
	
		-- Job Schedule
		exec @xReturn = sp_add_jobschedule
			@job_id = @xJobID,
			@name = @xJobName,
			@enabled = 1,
			@freq_type = 8,		-- Frequency Type, Weekly
			@freq_interval = 1,	-- Interval
			@freq_subday_type = 1, 
			@freq_recurrence_factor = 1,
			@active_start_time = 000000
	
		if(@xReturn != 0)
		begin
			print 'Error Creating dbcc schedule'
		end
	
		exec @xReturn = sp_add_jobserver @job_id = @xJobID, @server_name = @@servername
	
		if(@xReturn != 0)
		begin
			print 'Error Adding dbcc job'
		end
	end
	
	-- Weekly Maintenance, Index
	if(@xIndex = 1)
	begin
		set @xJobID = null
		set @xJobName = @xDatabase + ' maint ndx'
		set @xJobDesc = 'Weekly index maintenance for ' + @xDatabase
	
		-- Job
		exec @xReturn = sp_add_job
			@xJobName,
			1,		-- Enabled
			@xJobDesc,
			1,		-- Start Step ID
			'Database Maintenance',
			null,
			'sa',
			2,		-- Eventlog
			2,		-- Email
			2,		-- Net Send
			0,		-- Page
			@xOperator,	-- Operator, Email
			@xOperator,	-- Operator, net send
			null,		-- Operator, Page
			0,		-- Delete?
			@xJobID output
	
		if(@xReturn != 0)
		begin
			print 'Error Creating index rebuild job'
		end
	
		-- Job Step, Transaction Log Backup
		set @xCommand = 'exec master..sp_rebuild ''' + @xDatabase + ''''
	
		exec @xReturn = sp_add_jobstep 
			@job_id = @xJobID,
			@step_name = 'index rebuild',	-- Step Name
			@subsystem = 'TSQL',		-- Subsystem
			@command = @xCommand,
	 		@database_name = @xDatabase
	
		if(@xReturn != 0)
		begin
			print 'Error Creating index rebuild job step'
		end
	
		-- Job Schedule
		exec @xReturn = sp_add_jobschedule 
			@job_id = @xJobID,
			@name = @xJobName,
			@enabled = 1,
			@freq_type = 8,		-- Frequency Type, Weekly
			@freq_interval = 1,	-- Interval
			@freq_subday_type = 1,
			@freq_recurrence_factor = 1,
			@active_start_time = 020000
	
		if(@xReturn != 0)
		begin
			print 'Error Creating index maintenance schedule'
		end	
	
		exec @xReturn = sp_add_jobserver @job_id = @xJobID, @server_name = @@servername
	
		if(@xReturn != 0)
		begin
			print 'Error Adding index job'
		end
	end

	fetch next from db_cursor into @xDatabase
end

close db_cursor
deallocate db_cursor