I modified the code to backup the database based on size using groups.
I will use a basketball analogy in explaining what I tried to accomplish.
Let’s say there are 32 basketball players (number of tables) all with different skill level (database size) and I want to divide them into five evenly skilled teams.
To divide them as evenly as possible among the teams, I did the following:
If (32 % 5) > 1, then (32/5)+1=7, else #players/5
If I have 30 players, it will simply be 30/5 = 6. Simple.
The teams are grouped as follows:
Best player (largest db) - group 1
Second best (second largest db) - group 2
Third best (third largest db) – group 3
Fourth best (fourth largest db) – group 4
Fifth best (fifth largest db) – group 5
Sixth best (sixth largest db) group 6
Seventh best (seventh largest db) group 1
Etc, etc.
Two of the groups will end up with seven players (seven databases). The seventh players will be low ranking players (small databases) and may not have a huge impact on the team.
The code can use some improvement.
So, if I have a server with 46 databases (46 players) and I want 12 databases to be backed up at a time (12 players per team), I will end up with four group (four teams) with two groups/team having 12 databases/players and the other two with 11 databases/players.
/*
====================================================================================
Name:usp_MyDB_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_MyDB_Maintenance_CreateBackupJob as follows to create the jobs:
usp_MyDB_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_MyDB_Maintenance_BackupData
History:
Added option to group the databases by size and stagger the start time of the backup.
Example, if there are three groups, the
first largest database will be assigned to group one,
second largest db will be assigned to group two
third largest will be assigned to group 3
fourth largest database will be assigned to group one,
the fifth to group two,
etc, etc.
This way, all the large databases are not in one group
====================================================================================
*/
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 @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,
@group_interval_mi int, -- start interval of jobs between groups in minutes
@job_interval_sec int, -- start interval of jobs in the same group in seconds
@waittimesec datetime-- time to wait in seconds based on start interval of jobs (@job_interval_sec) in the same group in seconds
declare @database [sysname]
declare @totaldb int -- total databases to backup
declare @numgroup int -- total number of groups
declare @jobgroup int -- Total Number of jobs to run at the same time
set @backuplocation = 'C:\mssql\SQLBackup\'
set @jobgroup = 2
set @job_interval_sec = 1
set @group_interval_mi = 1
set @waittimesec = CONVERT(VARCHAR(8), DATEADD(ss,@job_interval_sec,'00:00:00'), 108)
--select @waittimesec
;with fs
as
(
select database_id, type, size * 8.0 / 1024 size
from sys.master_files
)
select
database_id,name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
into #databases
from sys.databases db
-- Exclude system databases
where database_id > 4
order by DataFileSizeMB desc
--select * from #databases order by DataFileSizeMB desc
--drop table #databases
set @totaldb = @@ROWCOUNT -- Total # of databases to be backed up
-- Set total number of groups or backup set that will be created
-- The number of groups is based on the number of databases to be backed up
-- divided by total number of jobs to be run at almost the same time.
-- If there is remender in the number, add one to the total number of group
set @numgroup = case
when (@totaldb % @jobgroup > = 1) then (@totaldb/@jobgroup) + 1
else @totaldb/@jobgroup
end
--select @totaldb as TotalDB,@jobgroup as NumJobs,@numgroup as NumGroup
/*
Assign databases to a group
Example, if there are three groups, the
first largest database will be assigned to group one,
second largest db will be assigned to group two
third largest will be assigned to group 3
fourth largest database will be assigned to group one,
etc, etc.
This way, all the large databases are not in one group
*/
declare @databases table(rn int identity(1,1),database_id int, dbname sysname,DataFileSizeMB float, LogFileSizeMB float)
insert into @databases
select * from #databases
order by DataFileSizeMB desc
--- Cleanup
drop table #databases
--declare final table that will hold databases to be backed up and include the backup command
declare @BackupDatabases table(dbnum int identity(1,1),dbname sysname,DataFileSizeMB float, LogFileSizeMB float,groupnum int,dbbackupcmd nvarchar(1000))
insert into @BackupDatabases
select dbname,DataFileSizeMB,LogFileSizeMB,groupnum = case
when (rn % @numgroup = 0) then @numgroup
else rn % @numgroup
end,
'backup database [' + [dbname] + '] to disk=''' + @backuplocation + @@SERVERNAME + '\'
+ [dbname] + '_Full_' + cast(datepart(dw, getdate()) as varchar(2)) + '.bak'' with init, compression'
from @databases
order by groupnum
select * from @BackupDatabases
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'
-- 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
--Get start date time of job
select @currentdatetime = dateadd(mi,case
when (@dbcounter/@jobgroup = 0) then 1
else (@dbcounter/@jobgroup) * @group_interval_mi
end,GETDATE())
set @starttime = cast(replace(cast(CONVERT(VARCHAR(8), @currentdatetime, 108) as nvarchar),':','') as int)
set @startdate = cast(CONVERT(VARCHAR(10), @currentdatetime, 112) as int)
set @delaycount = 0
--Execute stored procedure to create the job
exec MyDB..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: MyDB..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 specified time in second to continue. This spaces the jobs out every two seconds
waitfor delay @waittimesec
-- 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