SQLServerCentral.com / Backups / SQL Server 2005 / Multiple backup at the same time / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 12:53:13 GMT20RE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspx[quote][b]obtllc (3/15/2014)[/b]The teams are grouped as follows:Best player (largest db) - group 1Second best (second largest db) - group 2Third best (third largest db) – group 3Fourth best (fourth largest db) – group 4Fifth best (fifth largest db) – group 5Sixth best (sixth largest db) group 6Seventh best (seventh largest db) group 1Etc, 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.[/quote]Did you think of a scenario when these Groups overlap with one another ? In such case you will have more than the required number of parallel backups. How do you overcome this ?--SQLBuddyTue, 18 Mar 2014 11:34:52 GMTsqlbuddy123RE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspx[quote][b]obtllc (3/15/2014)[/b][hr]If I have 30 players, it will simply be 30/5 = 6. Simple.The teams are grouped as follows:Best player (largest db) - group 1Second best (second largest db) - group 2Third best (third largest db) – group 3Fourth best (fourth largest db) – group 4Fifth best (fifth largest db) – group 5Sixth best (sixth largest db) group 6Seventh best (seventh largest db) group 1Etc, etc.[/quote]That will mean that Group 1 will always take the longest and you lose out on the benefit of having multiple backups running at the same time because Groups 2 thru 6 will finish before Group 1, Groups 3 thru 6 will finish before Group 2, etc, etc.As a simple example, lets say that you had just 6 databases and they all varied in size by just 1. According to your algorithm and with 3 simultaneous backups running, you would have the following...Size Group 6 1 5 2 4 3 3 1 2 2 1 3If we aggregate the sizes for each group, we get...Size Group6+3 = 9 15+2 = 7 24+1 = 5 3If we said that each unit of value were 15 minutes worth of backup time, that means that Group 1 would finish in 2:15 hours. Group 2 would finish in 1:45 and sit idle for 0:30 compared to Group 1. Group 3 would finish in 1:15 and sit idle for 1:00 compared to group 1.What you want to happen is the following where all of the groups have a balance load and finish as quickly as possible (1:45 for all groups instead of 2:15 for the longest group):Size Group6+1 = 7 15+2 = 7 24+3 = 7 3 That's also known as a "Load Balancing" or "Bin Stacking" problem and it requires a loop of some sort to do it's job.Here's a simple bit of code for the example above.[code="sql"]DROP TABLE #MyHead,#AccumulatorDECLARE @DBCount INT ,@Backups INT; SELECT @Backups = 3 --Number of simultaneous backups;--===== Get the database names and sizes and remember them in descending size order. -- Obviously, this is just test data. You'd have to change this to look at -- something like sys.master_files. SELECT N = IDENTITY(INT,1,1), GB = Number, DBName = 'Database'+RIGHT(Number+1000,3) INTO #MyHead FROM master.dbo.spt_Values t WHERE t.Number BETWEEN 1 AND 6 AND t.Type = 'P' ORDER BY GB DESC --Sorting by descending size is critical for this to work.;--===== Remember the number of databases to backup SELECT @DBCount = @@ROWCOUNT;--===== Create our backup control bins SELECT TOP (@Backups) Bin = IDENTITY(INT,1,1) ,GB = 0 ,DBNames = CAST(NULL AS VARCHAR(MAX)) INTO #Accumulator FROM master.sys.all_columns ac1;--===== Presets for the loopDECLARE @Counter INT; SELECT @Counter = 1;--===== Stack the bins so "weigh" as the same as closely as possible by -- always assigning the current database, which has been sorted by -- size in descending order, to the bin with the least amount of -- work to do. WHILE @Counter &lt;= @DBCount BEGIN --===== This takes whatever the current database size is, finds the bin -- with the least amount of work to do (bin number breaks ties), -- and assigns the database name to that bin in a CSV. UPDATE a SET a.GB = a.GB + mh.GB ,a.DBNames = ISNULL(a.DBNames +',','') + mh.DBName FROM #MyHead mh CROSS JOIN #Accumulator a WHERE mh.N = @Counter AND a.Bin IN (SELECT TOP 1 a1.Bin FROM #Accumulator a1 ORDER BY a1.GB ASC, a1.Bin ASC) ; --===== I left this here just so you can see the bins fill in order. -- Take this out for production SELECT * FROM #Accumulator ORDER BY Bin ; --===== Bump the counter SET @Counter = @Counter+1 ; END;--===== This displays the worklist by bin and process order (ItemNumber) and could be converted to -- dynamic SQL backup commands to power just the number of jobs that you want to have running. SELECT a.Bin, TotalBinSize = a.GB, ca.ItemNumber, ca.Item FROM #Accumulator a CROSS APPLY dbo.DelimitedSplit8K(a.DBNames,',')ca; [/code]Here's the output for above. I manually added a space between backup jobs just for clarity sake.[code="plain"]Bin TotalBinSize ItemNumber Item--- ------------ ---------- -----------1 7 1 Database0061 7 2 Database0012 7 1 Database0052 7 2 Database0023 7 1 Database0043 7 2 Database003(6 row(s) affected)[/code]This system works well for when you have a database or two that might be quite a bit larger than the rest. For example, if Database001 had a size of 10, we'd get the following balancing act because the "system" wouldn't give it any more to do because of it's size and would try to balance the load as evenly as possible.[code="plain"]Bin TotalBinSize ItemNumber Item--- ------------ ---------- -----------1 10 1 Database0012 11 1 Database0062 11 2 Database0032 11 3 Database0023 9 1 Database0053 9 2 Database004(6 row(s) affected)[/code]In this case, it does make a bit of a bad guess because the combination of 6+4 and 5+3+2 would be better combinations to make them all come out to 10 and would require another "pass" to resolve that, but it's a whole lot better than 10+4, 6+3, 5+2 or 14, 9, 7.Sun, 16 Mar 2014 13:40:46 GMTJeff ModenRE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspxI 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) &gt; 1, then (32/5)+1=7, else #players/5If I have 30 players, it will simply be 30/5 = 6. Simple.The teams are grouped as follows:Best player (largest db) - group 1Second best (second largest db) - group 2Third best (third largest db) – group 3Fourth best (fourth largest db) – group 4Fifth best (fifth largest db) – group 5Sixth best (sixth largest db) group 6Seventh best (seventh largest db) group 1Etc, 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.[code="sql"]/* ==================================================================================== Name: usp_MyDB_Maintenance_BackupDataDescription: 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]GOALTER procedure [dbo].[usp_DBA_Maintenance_BackupData]asdeclare @ErrorMessage NVARCHAR(4000)declare @ErrorSeverity INTdeclare @ErrorState INTdeclare @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 secondsdeclare @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 timeset @backuplocation = 'C:\mssql\SQLBackup\'set @jobgroup = 2set @job_interval_sec = 1set @group_interval_mi = 1set @waittimesec = CONVERT(VARCHAR(8), DATEADD(ss,@job_interval_sec,'00:00:00'), 108)--select @waittimesec;with fsas( 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) LogFileSizeMBinto #databasesfrom sys.databases db-- Exclude system databaseswhere database_id &gt; 4order by DataFileSizeMB desc--select * from #databases order by DataFileSizeMB desc--drop table #databasesset @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 groupset @numgroup = case when (@totaldb % @jobgroup &gt; = 1) then (@totaldb/@jobgroup) + 1 else @totaldb/@jobgroup end --select @totaldb as TotalDB,@jobgroup as NumJobs,@numgroup as NumGroup/*Assign databases to a groupExample, 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 @databasesselect * from #databasesorder by DataFileSizeMB desc--- Cleanupdrop table #databases--declare final table that will hold databases to be backed up and include the backup commanddeclare @BackupDatabases table(dbnum int identity(1,1),dbname sysname,DataFileSizeMB float, LogFileSizeMB float,groupnum int,dbbackupcmd nvarchar(1000))insert into @BackupDatabasesselect 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 @databasesorder by groupnumselect * from @BackupDatabasesset @numdb = @@ROWCOUNTset @dbcounter = 1--select * from @BackupDatabases--Only run Database backup if count of database is &gt; 1if @numdb &gt; 0Begin begin try while @dbcounter &lt;= @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 &gt; 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 catchEnd[/code]Sat, 15 Mar 2014 03:00:39 GMTobtllcRE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspx[quote][b]obtllc (3/13/2014)[/b][hr]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.[/quote]Looks Good. How about picking up databases based on their previous backup execution times instead of first N, Next N etc .. ?--SQLBuddyThu, 13 Mar 2014 15:22:52 GMTsqlbuddy123RE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspxI 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.[code="sql"]/* ==================================================================================== Name: usp_DBA_Maintenance_CreateBackupJobDescription: 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]GOCREATE procedure [dbo].[usp_DBA_Maintenance_CreateBackupJob](@jobname [sysname],@jobdescription nvarchar(500),@sql nvarchar(4000),@startdate int,@starttime int)as/****** Object: Job [Backup Database] ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0if exists (SELECT [name] FROM msdb.dbo.sysjobs_view WHERE name = @jobname)Begin EXEC msdb.dbo.sp_delete_job @job_name=@jobname,@delete_unused_schedule=1End/****** Object: JobCategory [Database Maintenance] ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollbackENDDECLARE @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 OUTPUTIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 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=0IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollbackDECLARE @schedule_id intEXEC 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 OUTPUTIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTIONEndSave:[/code][code="sql"]/* ==================================================================================== Name: usp_DBA_Maintenance_BackupDataDescription: 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]GOAlter procedure [dbo].[usp_DBA_Maintenance_BackupData]asDECLARE @ErrorMessage NVARCHAR(4000)DECLARE @ErrorSeverity INTDECLARE @ErrorState INTdeclare @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 intset @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 = @@ROWCOUNTset @dbcounter = 1--select * from @BackupDatabases--Only run Database backup if count of database is &gt; 1if @numdb &gt; 0Begin begin try while @dbcounter &lt;= @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 &gt; 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 catchEnd[/code]Thu, 13 Mar 2014 12:17:01 GMTobtllcRE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspxI agree with all the comments, mostly the high I/O that it is going to generate. One thing I am trying to do is reduce the amount of time the backups take, mostly on a server with many databases. A test so far cuts the amount of time it takes to backup three user’s databases and the system databases from about 30 minutes to 10 minutes. This is still in testing. I will implement SQLbuddy’s idea of scheduling the jobs instead of manually kicking them off. I will also add the option of staggering the backups if there are more than five databases on the server or even check the sizes of the databases first and decide if they should run in parallel or sequential.Thanks,OBTMon, 10 Mar 2014 07:11:06 GMTobtllcRE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspx[quote][b] Jack Corbett (3/7/2014)[/b][hr]I would question why you'd want to run them in parallel if you are writing them all to the same drive, especially if all the databases are located on the same drive. You are adding extra IO Load to the whole system. I'd be more inclined to let them run sequentially to spread the IO load out over time. I might consider running them in parallel if I was writing each backup to separate locations.[/quote]+1000. Under the conditions you mention, it very well could be that the parallel backups might actually run slower than the sequential backups. At the very least, it's going to drive the R/W heads on the disks nuts.Fri, 07 Mar 2014 16:13:12 GMTJeff ModenRE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspxI would question why you'd want to run them in parallel if you are writing them all to the same drive, especially if all the databases are located on the same drive. You are adding extra IO Load to the whole system. I'd be more inclined to let them run sequentially to spread the IO load out over time. I might consider running them in parallel if I was writing each backup to separate locations.Fri, 07 Mar 2014 11:47:06 GMT Jack CorbettRE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspxLooks good and nice implementation. But still we are starting the jobs in a sequential order not parallelly. This approach works as there is very minute lag between the start time of those individual jobs.However to kick them off simultaneously, you can define the schedules of these jobs to let them kick-off at the same time. That way instead of manually kicking off (sp_startjob) they would start at the same time by the scheduler.--SQLBuddyFri, 07 Mar 2014 09:19:08 GMTsqlbuddy123RE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspxHi SQLBuddy,Your suggestion gave me an idea and I did the following:1. First I created a simple SQL job and scripted it out, the changed it into a stored procedure.2. Next I created a script to generate the SQL command to backup.3. From the script in step 2, I called the stored procedure in step 1 a new job to backup each database4. I then used sp_start_job to start the job.This way, I was able to run the backups in parallel. See scripts below. In my final output, I will be deleting the jobs once the execution completes.This may not be the most elegant solution, but it works for now. Any suggestions to enhance this will be appreciated.----- Stored procedure to create jobuse msdbgoalter procedure usp_CreateBackupJob(@jobname [sysname],@jobdescription nvarchar(500),@sql nvarchar(500))as/****** Object: Job [Backup Database] Script Date: 03/06/2014 17:39:57 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Object: JobCategory [Database Maintenance] Script Date: 03/06/2014 17:39:57 ******/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=1EndIF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@jobname, @enabled=0, @notify_level_eventlog=2, @notify_level_email=0, @notify_level_netsend=2, @notify_level_page=2, @delete_level=0, @description=@jobdescription, @category_name=N'Database Maintenance', @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback/****** Object: Step [Backup Database Step] Script Date: 03/06/2014 17:39:57 ******/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'master', @flags=0IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTIONEndSave:GO----- Script to call stored procedure and execute jobDECLARE @ErrorMessage NVARCHAR(4000)DECLARE @ErrorSeverity INTDECLARE @ErrorState INTdeclare @BackupDatabases table(dbnum int,dbname [sysname], dbbackupcmd nvarchar(1000))declare @sql nvarchar(1000), @dbname nvarchar(50), @backuplocation nvarchar(200), @numdb int, @dbcounter int, @jobname nvarchar(200)set @backuplocation = 'C:\Backup'declare @database [sysname]------------------------------------------------ Exclude system databases---------------------------------------------- insert into @BackupDatabases select Rn =Row_Number() Over(Order by (Select 1)),[name],'backup database [' + [name] + '] to disk=''' + @backuplocation +'\' + [name] + '_Full_' + cast(datepart(dw, getdate()) as varchar(2)) + '.bak'' with init, compression' from sys.databases where database_id &gt; 4set @numdb = @@ROWCOUNTset @dbcounter = 1--select * from @BackupDatabases--Only run Database backup if count of database is &gt; 1if @numdb &gt; 0Begin begin try while @dbcounter &lt;= @numdb Begin select @sql=dbbackupcmd,@dbname=dbname from @BackupDatabases where dbnum = @dbcounter set @jobname = 'Database backup for ' + @dbname print @sql exec msdb..usp_CreateBackupJob @jobname,@jobname,@sql EXEC msdb..sp_start_job @jobname --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 log backup failed.' + char(13) + @ErrorMessage + CHAR(13) + 'Severity: ' + convert(nvarchar,@ErrorSeverity) + ' , ' + 'State: ' + convert(nvarchar,@ErrorState) + CHAR(13) + @sql -- Raise an error RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ) end catchEndThu, 06 Mar 2014 20:25:17 GMTobtllcRE: Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspx[quote][b]obtllc (3/6/2014)[/b][hr]backup database ab to disk = 'c:\ab.bak'backup database bc to disk = 'c:\bc.bak'backup database cd to disk = 'c:\cd.bak'Thanks,OBT[/quote]This will be sequential. However you can start them parallely using separate jobs.--SQLBuddyThu, 06 Mar 2014 15:04:29 GMTsqlbuddy123Multiple backup at the same timehttp://www.sqlservercentral.com/Forums/Topic1548491-357-1.aspxCan I start multiple database backup at the same time using a script? Example, I have the following stored in a table.backup database ab to disk = 'c:\ab.bak'backup database bc to disk = 'c:\bc.bak'backup database cd to disk = 'c:\cd.bak'I then want to use a sql scxript to read the information from the table and start all three backups at the same time instead of using some type of loop to run each one at a time. I would like to apply this concept to other types of scripts as well.Thanks,OBTThu, 06 Mar 2014 14:23:04 GMTobtllc