Automated SQL Job to Backup Database(s)

Venkatesh Krishnamurthy, 2009-12-31 (first published: 2009-12-15)

Automated SQL Job to Backup All Database(s)

This is a simple script to backup your new databases and remove the backup schedule for deleted database in SQL Server 2005/2008.

The backup strategy of my script is given as below:

Full backup - every Friday at 7 PM.
Differential - every day at 8 PM.
Transactional Log - every 2 hours once.

(You can change the backup strategy as par your wish, for doing you needs to change the job schedules under sp_add_jobschedule and update date, time and Schedule type)

Pre-Step to be made:

Creating Backup folder

Create a shared folder in your Backup server if the backup need to be move to Centralized server and give write access to SQL service account.
Otherwise if the backup is in local server then create a folder in any one of the local drive and give write access to SQL Service account.

Update the following variables in the script

@BackupServerNameInput - Enter the Central Backup Server Name

Example : 'MYBACKUPSERVER' - If the backup need to be done in Network Backup Server (Refer Line no : 20 and 460)
Example : 'LOCALSQLSERVER' - If the backup is in Local host. (Refer Line no : 20 and 460)

@BackupShareInput - Enter the Share / Local path name.

Example : '\\MYBACKUPSERVER\SQLBACKUP' - where MYBACKUPSERVER - Central Backup Servername and SQLBACKUP is the shared folder in MYBACKUPSERVER and SQL Service account will have write access to this folder. (Refer Line no : 21 and 461)
Example : 'D:\SQLBACKUP' - If you are planning to take the Backup in local disk. (Refer Line no : 21 and 461)

Note : If you are planning to create backup jobs and start full backup immediately once you implement the job, uncomment the last 2 lines in the script.

How can I execute the script?

Simply execute the SQL script and it will create a Job named as 'Create_Backup_Jobs', once you execute the job it will create backup jobs for every databases.

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
-----------------------------------------------------------------------------------------------------------
-- 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 @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
 
*/

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.5 (4)

Log in or register to rate

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.5 (4)

Log in or register to rate

You rated this post out of 5. Change rating

Related content

I Need a Backup and I Need It Now

by Robert Davis

SQLServerCentral.com

Backup and restore

Have you ever wished that creating a backup on the fly was easier and quicker? Whether you do it with the GUI or with T-SQL, you still have to open up Management Studio, connect to the database, and go through the steps. You have to figure out ...

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1.5 (2)

Log in or register to rate

You rated this post out of 5. Change rating

2009-04-29

4,263 reads

External Article

Using the FORFILES Command to Delete SQL Server Backups

by Additional Articles

MSSQLTips.com

Backup and restore

Is there a way I can automate a file deletion process from within SQL Server?

2008-11-11

4,819 reads

External Article

Auto generate restore scripts after each backup completes

by Additional Articles

MSSQLTips.com

Backup and restore

There are a lot of scripts out there that will allow you to use the msdb to auto-generate restore scripts, but what if you cannot access the msdb database. What is the easiest way to make sure I have the proper restore scripts on hand without relying on the msdb database?

2008-11-07

3,562 reads

External Article

Importance of Database Backups and Recovery Plan

by Additional Articles

SQL Server Performance

Backup and restore

As a DBA you need to ensure that the databases you manage are backed up regularly. This article details the importance of database backups and a recovery plan

2008-10-15

2,652 reads

External Article

Selecting the database recovery model to ensure proper backups

by Additional Articles

MSSQLTips.com

Backup and restore

One of the first things that should be done when managing SQL Server is to setup an appropriate backup plan in order to minimize any data loss in the event of a failure. Along with setting up a backup plan there are certain database configurations that need to be setup to ensure you are able to backup databases correctly. In this tip we will look at the different recovery models that SQL Server offers and how to choose a recovery model for your database.

2008-05-30

4,151 reads