Hi 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 database

4. 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 job

use msdb

go

alter 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 TRANSACTION

DECLARE @ReturnCode INT

SELECT @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=1

End

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=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 OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 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=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_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:

GO

----- Script to call stored procedure and execute job

DECLARE @ErrorMessage NVARCHAR(4000)

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT

declare @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 > 4

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

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 catch

End