Automated SQL Job to Backup All Database(s)

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.sys.objects WHERE name = 'usp_CreateBackupJobs' AND type = 'P')
DROP PROCEDURE [dbo].[usp_CreateBackupJobs]
GO

/****** Object: StoredProcedure [dbo].[usp_CreateBackupJobs] Script Date: 10/02/2008 06:04:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_CreateBackupJobs]
/************************************************************************************************************************/
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
/*
@BackupServerNameInput VARCHAR(50)= N'MYBACKUPSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'\\MYBACKUPSERVER\MYSHAREFOLDER', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
*/
@BackupServerNameInput VARCHAR(50)= N'LOCALSQLSERVER', -- Type the centralized backup server name
@BackupShareInput VARCHAR(200) = N'D:\ICAS\BACKUP', -- Type the IP address of the backup server's backup NIC and the sharename
@DoAFullBackupInput INT = 1 -- If this value is set to 1 a full backup of all the new databases will be taken
-----------------------------------------------------------------------------------------------------------
-- Input section 1/2 end
-----------------------------------------------------------------------------------------------------------
/************************************************************************************************************************/
AS
BEGIN
DECLARE @ServerName VARCHAR(30)
DECLARE @CI VARCHAR(50)
DECLARE @DbName VARCHAR(100)
DECLARE Database_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM master.dbo.sysdatabases
DECLARE @BackupServerName VARCHAR(50)
DECLARE @ShareName VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @DoAFullBackup int
DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @MakeTheJob int
DECLARE @CommandString VARCHAR(4000)
DECLARE @foldermissing int
DECLARE @jobId BINARY(16)
DECLARE @JobName VARCHAR(200)
DECLARE @ReturnCode INT
DECLARE @Backup_Var VARCHAR(100)
DECLARE JobName_Cursor CURSOR FOR SELECT LTRIM(RTRIM(name)) FROM msdb.dbo.sysjobs
DECLARE @JobName2 VARCHAR(200)


SET @BackupServerName = @BackupServerNameInput
SET @BackupShare = @BackupShareInput
SET @DoAFullBackup = @DoAFullBackupInput



/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY('MachineName'))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY('InstanceName'))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + '_' + @CI


/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @MakeTheJob = 0
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @JobName = 'Backup_' + @DbName + '_Full --> ' + @BackupServerName

/*Check if the a full should be made - start*/
IF (@DbName <> 'tempdb' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
/*Check if the a full should be made - end*/

/*Create the Full Backup job - start */
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION

SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Full_Backup_Device'

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Full Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Full_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK= @BackupShare with init'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Full Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup',
@enabled=1,
@freq_type=8,
@freq_interval=32,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080929,
@active_end_date=99991231,
@active_start_time=190000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Full Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
/*Run a full backup - start*/
IF (@DoAFullBackup = 1)
BEGIN
--PRINT @DbName + ' Nu skal der laves en full backup'
EXEC sp_start_job @job_name = @JobName
END
/*Run a full backup - end*/

END
/*Create the Full Backup job - end */

/*Create the Diff Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Diff --> ' + @BackupServerName
IF (@DbName <> 'tempdb' AND @DbName <> 'pubs' AND @DbName <> 'Northwind' AND @DbName <> 'AccentureWorks' AND @DbName <> 'master' AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName)))
SET @MakeTheJob = 1
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION

SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName
SET @NameOfBackupDevice = @DbName + '_Diff_Backup_Device'

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Diff Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Diff_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
backup database @Db_Name TO DISK=@BackupShare with differential'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Diff Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup',
@enabled=1,
@freq_type=8,
@freq_interval=95,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=200000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Diff Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION


END


/*Create the Diff Backup job - end*/

/*Create the Log Backup job - start*/
SET @MakeTheJob = 0
SET @JobName = 'Backup_' + @DbName + '_Log --> ' + @BackupServerName
IF ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) <> 'SIMPLE') AND (NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName))--((SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Backup_' + @DbName + '_Log') <> 'Backup_' + @DbName + '_Log') -- Checks the recovery model
SET @MakeTheJob = 1
ELSE
BEGIN
IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = @JobName) AND ((SELECT DATABASEPROPERTYEX(@DbName, 'recovery')) = 'SIMPLE')
BEGIN
SET @jobId = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName)
EXEC msdb.dbo.sp_delete_job @job_id=@JobId, @delete_unused_schedule=1
END
END
IF @MakeTheJob = 1
BEGIN
/****** Object: Job [tempclb] Script Date: 09/29/2008 14:29:49 ******/
BEGIN TRANSACTION

SELECT @ReturnCode = 0
/****** Object: JobCategory [BACKUP]] Script Date: 09/29/2008 14:29:49 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END


SET @JobID = NULL
SET @ShareName = @BackupShare + '\' + @CI + '\' + @DbName

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Log Backup',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


SET @CommandString = N'DECLARE @NameOfBackupDevice VARCHAR(200)
DECLARE @Db_Name VARCHAR(200)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
SET @Db_Name = ''' + @DbName + '''
SET @BackupShare = ''' + @ShareName+ '''
SET @FileName = convert(varchar, getdate(), 120)
SET @FileName = stuff (@FileName, 11, 1, ''_'')
SET @FileName = stuff (@FileName , 14, 1, ''-'')
SET @FileName = stuff (@FileName, 17, 1, ''-'')
SET @FileName = @Db_Name + ''_Log_'' + @FileName + ''.bak''
SET @BackupShare = @BackupShare + ''\'' + @FileName
BACKUP LOG @Db_Name TO DISK=@BackupShare'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Log Backup',
@step_id=1 ,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,@subsystem=N'TSQL',
@command=@CommandString,
@database_name=@DbName,
@flags=0,
@retry_attempts=3,
@retry_interval=5

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=8,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080213,
@active_end_date=99991231,
@active_start_time=010000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Log Backup - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081003,
@active_end_date=99991231,
@active_start_time=101920,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION


END

/*Create the Log Backup job - end*/

GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
FETCH NEXT FROM Database_Cursor INTO @DbName
END

CLOSE Database_Cursor

DEALLOCATE Database_Cursor

/*****************************************************************************************************************************/

OPEN JobName_Cursor
FETCH NEXT FROM JobName_Cursor INTO @JobName
WHILE @@FETCH_STATUS = 0
BEGIN
IF len(@JobName) < 7 SET @Backup_Var = 'STOP' ELSE SET @Backup_Var = (SELECT LEFT(@JobName, 7)) IF (@Backup_Var = 'Backup_') BEGIN SET @JobName2 = (SELECT LEFT(@JobName, len(@JobName)- 5 - len(@BackupServerName))) IF (SELECT RIGHT(@JobName2, 3)) = 'Log' BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-4)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END ELSE BEGIN SET @JobName2 = (SELECT LEFT(@JobName2, len(@JobName2)-5)) SET @JobName2 = (SELECT RIGHT(@JobName2,len(@JobName2)-7)) END IF NOT EXISTS(SELECT name FROM master.sys.databases WHERE name = @JobName2) BEGIN SET @JobID = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = @JobName) EXEC msdb.dbo.sp_delete_job @job_id=@JobID, @delete_unused_schedule=1 PRINT @JobName + ' is deleted' END END FETCH NEXT FROM JobName_Cursor INTO @JobName END CLOSE JobName_Cursor DEALLOCATE JobName_Cursor /*****************************************************************************************************************************/ END GO /******************************************************************************************************************************************/ /* */ /* Here begins the job creation */ /* */ /******************************************************************************************************************************************/ USE [master] DECLARE @BackupServerName VARCHAR(50) DECLARE @BackupShare VARCHAR(1000) --DECLARE @Backup_Var VARCHAR(100) --DECLARE JobName_Cursor CURSOR FOR SELECT name FROM msdb.dbo.sysjobs --DECLARE @JobName2 VARCHAR(200) DECLARE @JobName VARCHAR(200) DECLARE @CommandString VARCHAR(4000) ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 start - The section below is the one you need to look at each time you run the script ----------------------------------------------------------------------------------------------------------- /* SET @BackupServerName = N'MYBACKUPSERVER' -- Type the backup server name SET @BackupShare = N'\\MYBACKUPSERVER\MYSHAREFOLDER' -- Type the IP address of the backup server's backup NIC and the sharename */ SET @BackupServerName = N'LOCALSQLSERVER' -- Type the backup server name SET @BackupShare = N'D:\ICAS\BACKUP' -- Type the IP address of the backup server's backup NIC and the sharename --SET @DoAFullBackup = 0 -- If this value is set to 1 a full backup of all the new databases will be taken ----------------------------------------------------------------------------------------------------------- -- Input section 2/2 end ----------------------------------------------------------------------------------------------------------- USE [msdb] /****** Object: Job [Create_Backup_Jobs] Script Date: 10/02/2008 05:34:18 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [BACKUP] Script Date: 10/02/2008 05:34:18 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'BACKUP' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'BACKUP' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

IF NOT EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Create_Backup_Jobs',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Creates backup jobs for all the databases',
@category_name=N'BACKUP',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Enable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Enable XP_cmdshell',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 1
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Create Backup Folders] Script Date: 10/02/2008 05:34:19 ******/
SET @CommandString = N'DECLARE @ServerName VARCHAR(50)
DECLARE @CI VARCHAR(100)
DECLARE @DbName VARCHAR(100)
DECLARE @BackupShare VARCHAR(1000)
DECLARE @ShareName VARCHAR(200)
DECLARE @CommandString VARCHAR(500)
DECLARE @foldermissing int
DECLARE Database_Cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases

-----------------------------------------------------------------------------------------------------------
-- Input section start - The section below is the one you need to look at each time you run the script
-----------------------------------------------------------------------------------------------------------
SET @BackupShare = N''' + @BackupShare + ''' -- Type the IP address of the backup server''s backup NIC and the sharename
-----------------------------------------------------------------------------------------------------------
-- Input section end
-----------------------------------------------------------------------------------------------------------

/* Get server and instance name start*/
SELECT @ServerName = CONVERT(varchar(50), SERVERPROPERTY(''MachineName''))
SELECT @CI = CONVERT(varchar(50), SERVERPROPERTY(''InstanceName''))
IF @CI IS NULL
SET @CI = @ServerName
ELSE
SET @CI = @ServerName + ''_'' + @CI

/* Get server and instance name end */

/* Run through all the databases */
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @DbName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @ShareName = @BackupShare + ''\'' + @CI + ''\'' + @DbName

/*Check if the backupfolder exists and if it doesn''t - create it - start*/
SET @CommandString = ''dir '' + @ShareName
EXEC @foldermissing = master..xp_cmdshell @CommandString
IF @foldermissing = 1
BEGIN
SET @CommandString = ''mkdir '' + @ShareName
EXEC master..xp_cmdshell @CommandString
END
FETCH NEXT FROM Database_Cursor INTO @DbName
END

/*Check if the backupfolder exists and if it doesn''t - create it - end*/
CLOSE Database_Cursor

DEALLOCATE Database_Cursor
go'

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Folders',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=4,
@on_fail_step_id=5,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@CommandString,
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''xp_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [Create Backup Jobs] Script Date: 10/02/2008 05:34:19 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Create Backup Jobs',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command= N'EXEC msdb.dbo.usp_CreateBackupJobs',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Disable XP_cmdshell on failure] Script Date: 10/03/2008 10:59:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Disable XP_cmdshell on failure',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=2,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=3,
@retry_interval=5,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC master.dbo.sp_configure ''Show_Advanced_Options'', 1
RECONFIGURE
EXEC master.dbo.sp_configure ''XP_cmdshell'', 0
RECONFIGURE',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=170000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Create Backup Jobs - Run once',
@enabled=0,
@freq_type=1,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20081002,
@active_end_date=99991231,
@active_start_time=55710,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

COMMIT TRANSACTION

GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:


go


/* Uncomment the below code if you like to create Backup jobs now*************/

IF EXISTS (SELECT name FROM msdb.dbo.sysjobs WHERE name = 'Create_Backup_Jobs')
EXEC sp_start_job Create_Backup_Jobs