Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Please Please help on this Trigger Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 1:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 8:31 AM
Points: 236, Visits: 934
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
Post #1496566
Posted Thursday, September 19, 2013 3:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:04 PM
Points: 801, Visits: 715
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.



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1496631
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse