there is a brilliant script to create a server side trigger on an event of a create database.
here is the script:
IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database')
BEGIN
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
END
GO
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE
@DatabaseName NVARCHAR(128)
, @CreatedBy NVARCHAR(128)
, @CreatedDate NVARCHAR(23)
, @SQL NVARCHAR(4000);
SELECT
@DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')
, @CreatedBy = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]','NVARCHAR(128)')
, @CreatedDate = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]','NVARCHAR(23)');
IF @DatabaseName = 'MyTestDatabase'
BEGIN
SET @SQL = '
USE ' + @DatabaseName + ';
EXEC sys.sp_addextendedproperty @name = N''Owner'', @value = N''' + @CreatedBy + ''';
EXEC sys.sp_addextendedproperty @name = N''StartDate'', @value = N''' + @CreatedDate + ''';';
-- PRINT @SQL;
EXEC (@SQL);
END
GO
CREATE DATABASE MyTestDatabase;
GO
SELECT name, value
FROM MyTestDatabase.sys.fn_listextendedproperty(default, default, default, default, default, default, default)
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
DROP DATABASE MyTestDatabase;
GO
here is the result:
Now one thing I would like to achieve is having that same, but triggered when I restore a database on the server.
I have been looking around and I found a couple of options:
- put a trigger on msdb - table msdb..restorehistory - which is not of my liking particularly, and
restore filelistonly and the likeshould also be taken apart. - I could use event notification, but It seems lots of work involved
question: is there a easier and cleaner way - to trigger some actions just after a database restore finishes?
I am mostly using sql 2019. what I really wanted is a server side trigger that fires when a database is restored.
