September 19, 2013 at 1:05 pm
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
September 19, 2013 at 3:58 pm
I would suspect that when you create a database manually, you are logged with sysadmin permissions, which you hopefully you are not when you are logged in from the application. In this case the permissions to xp_create_subdir is lacking.
A remedy would be to sign the trigger with a certificate, but I think a better solution is to take the backup asynchronously. I would suggest that you set up an event notification, and then you have an activation procedure on a Service Broker queue that creates the backup.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply