I modified the script to schedule the jobs for two seconds apart. Scheduling and starting all the backup at the same time was causing a deadlock. Also added the option to send an email out when there is an error. This is assumng that a mail profile has already been setup on the server. Mine is called PersonalEmail.
My next attempt will be to check the size of the database and decide if to run multiple database backup at a time or in a sequential mode, based on size.
/*
====================================================================================
Name:usp_DBA_Maintenance_CreateBackupJob
Description: This procedure creates SQL Maintenance Backup job for one database at a time.
The job is deleted after execution because of @delete_level=3.
Setting @delete_level=0 will not delete the job after execution.
Input Parameters:
@jobname-Job Name
@jobdescription-Job Description
@sql-SQL script to be executed
@startdate-Start Date
@starttime-Start Time
Execution:MyDB..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime
History:
====================================================================================
*/
USE [MyDB]
GO
CREATE procedure [dbo].[usp_DBA_Maintenance_CreateBackupJob](@jobname [sysname],@jobdescription nvarchar(500),@sql nvarchar(4000),@startdate int,@starttime int)
as
/****** Object: Job [Backup Database] ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
if exists (SELECT [name] FROM msdb.dbo.sysjobs_view WHERE name = @jobname)
Begin
EXEC msdb.dbo.sp_delete_job @job_name=@jobname,@delete_unused_schedule=1
End
/****** Object: JobCategory [Database Maintenance] ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@jobname,
@enabled=1,
@notify_level_eventlog=3,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=3,
--@notify_email_operator_name=N'PersonalEmail',
@description=@jobname,
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Backup Database Step] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup Database Step',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=@sql,
@database_name=N'DBA',
@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
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=@jobname, @name=N'Daily Backup Schedule',
@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=@startdate,
@active_end_date=99991231,
@active_start_time=@starttime,
@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
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
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
/*
====================================================================================
Name:usp_DBA_Maintenance_BackupData
Description: This procedure creates one backup job for each database on the server,
schedules them to run as follows:
First 6 databases: 2 seconds apart from the current time
Next 6 databases: 10 minutes from current time and 2 seconds apart
Next 6 databases: 20 minutes from current time and 2 seconds apart
and so on, depending on the amount of the databases to be backed up.
This is to reduce I/O load and prevent deadlocks.
It calls MyDB..usp_DBA_Maintenance_CreateBackupJob as follows to create the jobs:
MyDB..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime
Parameter description are as follows:
@jobname-Job name
@jobname-Job Description
@sqlblock-SQL Script executed to backup to database
@startdate-Start Date
@starttime-Start Time
Execution:exec MyDB..usp_DBA_Maintenance_BackupData
History:
====================================================================================
*/
USE [MyDB]
GO
Alter procedure [dbo].[usp_DBA_Maintenance_BackupData]
as
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
declare @sqlblock nvarchar(4000)
declare @BackupDatabases table(dbnum int,dbname [sysname], dbbackupcmd nvarchar(1000))
declare @sql nvarchar(1000),@cmdstring nvarchar(1000),
@dbname nvarchar(50),
@backuplocation nvarchar(200),
@numdb int, @dbcounter int,
@jobname nvarchar(200),
@currentdatetime datetime,
@startdate int,
@starttime int,
@delaycount int
set @backuplocation = 'C:\MSSQL\SQLBackup\'
declare @database [sysname]
----------------------------------------------
-- Exclude databases with custom backup needs
----------------------------------------------
insert into @BackupDatabases
select Rn =Row_Number() Over(Order by (Select 1)),[name],'backup database [' + [name] + '] to disk=''' + @backuplocation + @@SERVERNAME + '\'
+ [name] + '_Full_' + cast(datepart(dw, getdate()) as varchar(2)) + '.bak'' with init, compression'
from sys.databases
where name not in (select databasename from DBA..DBA_Maintenance_CustomDatabases)
set @numdb= @@ROWCOUNT
set @dbcounter = 1
--select * from @BackupDatabases
--Only run Database backup if count of database is > 1
if @numdb > 0
Begin
begin try
while @dbcounter <= @numdb
Begin
select @sql=dbbackupcmd,@dbname=dbname from @BackupDatabases where dbnum = @dbcounter
set @jobname = 'Database backup for ' + @dbname
--set @sql = 'select 1/0' -- Used to test failure in the job
-- Format the backup command string so that it can be included in the error message
set @cmdstring = ''''+ REPLACE(@sql,'''','''''')+''''
-- Query that will be included in backup job
-- an email will be sent out to the DBA group if the job fails
set @sqlblock = 'DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT'
+ CHAR(13)+CHAR(10) + 'Begin try' + CHAR(13)+CHAR(10)
+ '' + @sql + CHAR(13)+CHAR(10)
+ 'End try' + CHAR(13)+CHAR(10)
set @sqlblock = @sqlblock +
'begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
set @ErrorMessage = ''The Database backup failed.''
+ char(13) + @ErrorMessage + CHAR(13)
+ ''Severity: '' + convert(nvarchar,@ErrorSeverity) + CHAR(13)
+ ''State: '' + convert(nvarchar,@ErrorState)+ CHAR(13)
+ ''Command: '' + ' + @cmdstring + '' + CHAR(13)+CHAR(10)+ '
declare @subjectline nvarchar(100)
select @subjectline = ''Database Backup Failed on '' + @@SERVERNAME + '' / ' + @dbname + '''' + CHAR(13)+CHAR(10)+
'exec msdb.dbo.sp_send_dbmail
@profile_name = ''PersonalEmail'',
@recipients = ''YOUREMAIL@YOURDOMAIN.COM'',
@body = @ErrorMessage,
@subject = @subjectline
-- Raise an error
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
end catch'
-- End query that will be included in backup job
--Get start date time of job
select @currentdatetime = dateadd(mi,case
when (@dbcounter/7 = 0) then 1
else (@dbcounter/7) * 2-- Used 2 minutes for testing. 10 for prod.
end,GETDATE())
set @starttime = cast(replace(cast(CONVERT(VARCHAR(8), @currentdatetime, 108) as nvarchar),':','') as int) -- HHMMSS format
set @startdate = cast(CONVERT(VARCHAR(10), @currentdatetime, 112) as int)-- YYYYMMDD format
set @delaycount = 0
--Execute stored procedure to create the job
exec dba..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime
-- Make sure the job is created before continuing
while not exists (SELECT [name] FROM msdb.dbo.sysjobs_view WHERE name = @jobname)
Begin
print 'In delay for ' + @jobname
waitfor delay '00:00:05'
if @delaycount > 6
Begin-- been waiting for 30 seconds, so do someting. Hopefully this will never happen
set @sql = 'Command: dba..usp_DBA_Maintenance_CreateBackupJob @jobname,@jobname,@sqlblock,@startdate,@starttime'
RAISERROR ('It is taking too long to create SQL job while executing the following command', -- Message text.
16, -- Severity.
1 )-- State.
End
set @delaycount = @delaycount + 1
End
--Wait two second to continue. This spaces the jobs out every two seconds
waitfor delay '00:00:02'
-- Start the backup job. This will start the job immediately
--EXEC msdb..sp_start_job @jobname
--Start the backup for the database. This will backup one database at a time
--exec sp_executesql @sql
set @dbcounter = @dbcounter + 1
End
end try
begin catch
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
set @ErrorMessage = 'The Database backup failed.'
+ char(13) + @ErrorMessage + CHAR(13)
+ 'Severity: ' + convert(nvarchar,@ErrorSeverity) + ' , '
+ 'State: ' + convert(nvarchar,@ErrorState)+ CHAR(13)
+ @sql
declare @subjectline nvarchar(100)
select @subjectline = 'Database Backup Failed on ' + @@SERVERNAME + ' / ' + @dbname
exec msdb.dbo.sp_send_dbmail
@profile_name = 'PersonalEmail',
@recipients = 'YOUREMAIL@YOURDOMAIN.COM',
@body = @ErrorMessage,
@subject = @subjectline
-- Raise an error
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
)
end catch
End