0

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:

enter image description here

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:

  1. put a trigger on msdb - table msdb..restorehistory - which is not of my liking particularly, and restore filelistonly and the like should also be taken apart.
  2. 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.

Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320

0 Answers0