In my organization they create the database through the application and I want to create the trigger to fire the backup whenever the new database are created. I have created the following script. It's creating the backup when I manually create the database, but the trigger is not firing when the database is created by application. Do I need to change the script or anything?
CREATE TRIGGER TRG_BackupNewDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @database sysname, @event_data XML = EVENTDATA(), @folder nvarchar(4000), @file nvarchar(4000)
SET @database = @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname')
set @folder = 'X:\Backups\' + @database
set @file = @folder + '\' + @database + '.bak'
if exists (select * from sys.databases where name = @database and source_database_id is null)
BEGIN
EXEC master.dbo.xp_create_subdir @folder
COMMIT
BACKUP DATABASE @database to disk=@file
raiserror( 'You can ignore the error message which says that the transaction ended within the trigger.', 16,1)
END
GO