Ah but in my utility instance I created the following trigger:

CREATE TRIGGER [DDLTriggerCreateDatabaseBackupSchedule]

ON ALL SERVER

FOR CREATE_DATABASE

AS

declare @data xml

declare @DBName varchar(256)

set @data = eventdata()

set @DBName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)')

INSERT INTO master.dbo.backupschedule(backupdate, Backupweekday, database_name, BackupType)

SELECT DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))) AS backupdate,

DATENAME(DW,DATEADD(DD,N,dateadd(dd, 0, DATEDIFF(dd, 0, getdate())))) AS Backupweekday

,b.name

,CASE WHEN N % 14 = 0 THEN 'F' ELSE 'D' END AS backuptype

FROM dbo.Tally AS a

CROSS JOIN sys.databases as b

WHERE N < 780

and name = @DBName

GO

It's not that great to be honest. I'm sure with your SQL skills you can do better but here goes anyway:

CREATE PROCEDURE [dbo].[sp_QbaseBackup] @BackupFolderPath varchar(400)

AS

SET NOCOUNT ON

DECLARE DatabaseList CURSOR

FOR SELECT database_name, BackupType

FROM backupschedule

WHERE backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))

and backupstartdate is null and backupenddate is null

ORDER BY database_name ASC

DECLARE @DBName VARCHAR(255)

DECLARE @BackupTtype CHAR(1)

DECLARE @SQL VARCHAR(max)

OPEN DatabaseList

FETCH NEXT FROM DatabaseList INTO @DBName, @BackupTtype

WHILE @@FETCH_STATUS = 0

BEGIN

-- log the start time of the backup

update dbo.backupschedule

set backupstartdate = GETDATE()

where database_name = @DBName

and backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))

IF @BackupTtype = 'F'

BEGIN

SET @SQL = 'BACKUP DATABASE [' + @DBName + '] TO DISK = N' + CHAR(39) + @BackupFolderPath + @DBName +

'.complete'' WITH NAME = N' + char(39) + @DBName + ' Complete Backup' + char(39) + ', CHECKSUM'

EXEC(@SQL)

END

ELSE IF @BackupTtype = 'D'

BEGIN

SET @SQL = 'BACKUP DATABASE [' + @DBName + '] TO DISK = N' + CHAR(39) + @BackupFolderPath + @DBName +

'.Differential'' WITH DIFFERENTIAL, NAME = N' + char(39) + @DBName + ' differential backup' + char(39) + ', CHECKSUM'

EXEC (@SQL)

END

-- log the end time of the backup

update dbo.backupschedule

set backupenddate = GETDATE()

where database_name = @DBName

and backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))

-- Let's also get the backup size

update a

set backupSizeMB = b.backup_size / 1048576

from master.dbo.backupschedule as a

inner join msdb.dbo.backupset as b

on a.database_name = b.database_name

where a.database_name = @DBName

and backupdate = dateadd(dd, 0, DATEDIFF(dd, 0, getdate()))

and b.backup_set_id = (select MAX(backup_set_id)

from msdb.dbo.backupset as c

where b.database_name = c.database_name)

FETCH NEXT FROM DatabaseList INTO @DBName, @BackupTtype

END

CLOSE DatabaseList

DEALLOCATE DatabaseList

RETURN 0

Feel free to have a go at me for such bad coding practice lol!

I'm testing this but I'm not really sure how well it's going to work.