• 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