April 4, 2009 at 11:52 am
i thought I had an epiphany when i was restoring a database from another server...done this lots of times, and I have a canned script for fixing orphaned users;
So I thought, if there is a DDL Event for CREATE_DATABASE or RESTORE_DATABASE, i could add users automatically, or fix orphaned users automatically.
so my objective was to log the event, thus proving that the trigger finds the event so i can add other things, like run my script/proc against the db in the event.
so I Look in BOL,(See "DDL Statements with Server Scope") and do not see a specific event for restore. based on the events i thought might work, I did this:
CREATE TABLE [dbo].[DDLEventLog](
[EventDate] [datetime] NULL,
[UserName] [sysname] NULL,
[objectName] [sysname] NULL,
[CommandText] [varchar](max) NULL,
[EventType] [nvarchar](100) NULL,
[WholeEventData] varchar(max) NULL
)
ALTER TRIGGER TR_SERVER_FIXLOGINS
ON ALL SERVER
FOR CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE
AS
BEGIN
declare @eventData XML,
@uname nvarchar(50),
@oname nvarchar(100),
@otext varchar(max),
@etype nvarchar(100),
@edate datetime
--set my local XML variable to the triggers event data
SET @eventData = eventdata()
SELECT
@edate=GETDATE(),
@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
insert master.dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType,WholeEventData) values
(@edate,@uname,@oname,@otext,@etype,convert(varchar(max),@eventData))
END
GO
ENABLE TRIGGER TR_SERVER_FIXLOGINS ON ALL SERVER
It captures creates and drops as well as changes like setting the db to simple or full recovery.
unfortunately a restore is not the same as a CREATE or ALTER , so it did not capture anything when I restored any database.
so i want to find an event that occurs when a database gets restored. can't seem to find it,
I see in the default trace an event named "Audit Backup/Restore Event", so I guess I could create a job that reads the trace to automatically fix logins, but that seems like such a waste...job would never find anything except maybe once or twice a week.
anyone give me a better idea on an event which might get fired on a restore. not an emergency, just a nice to have to add to my snippets collection.
Lowell
April 4, 2009 at 8:59 pm
Ok, based on the situation and problem you provided, I can provide a workaround for this problem.
This workaround is -
1. not recommended to use, unless you are very confident in what you are going to do
2. this is playing around system database and object
after these disclaimers 😉 lets begin
Idea is to capture restore database event, now lets recall "what happens when we restore any database?"- after restore restorehistory table is updated with one record. This is the whole concept, so, we'll create an AFTER INSERT trigger on restorehistory table, although its a system table, believe me you need not to set allow updated before doing it, it will easily create trigger on this table.
CREATE TRIGGER trg_after_restore
ON msdb..restorehistory
AFTER insert
AS
select 'DATABASE '+inserted.destination_database_name + ' restored ON '+cast(inserted.restore_date as varchar(20))
from inserted
This trigger will simply write back what db was restored and when. Change it with your script.
Remember, if your trigger script fails (anyways) there will be no update in restorehistory table, although your restore wont be effect by this. To avoid this, dont make complex scripting in the trigger itself, better create a job and call the job from trigger. Calling a job is not going to fail in normal conditions.
Use this work around only -
1. You have tested it and you are confident what you are doing
2. There is no other way left to automatically apply your post restore scripts
February 2, 2010 at 1:05 pm
sorry to bring up an old thread
I added a trigger on the msdb.dbo.restorehistory to "only print out the restored db name" and it never kicked in after 10 restores, despite I see the actual restore records appearing
Any clues? I don't know if SQL 2005 (64 bit STD) disallows triggers on system objects now?
April 15, 2016 at 8:21 am
You can't use DML triggers on a system table....
https://technet.microsoft.com/en-us/library/ms187834(v=sql.105).aspx
April 15, 2016 at 9:30 am
Mel Lusk (4/15/2016)
You can't use DML triggers on a system table....https://technet.microsoft.com/en-us/library/ms187834(v=sql.105).aspx
old thread from six years ago...
the msdb object mentioned msdb.dbo.restorehistory is not a system object; adding a trigger to the table is easy..
Lowell
April 15, 2016 at 10:37 am
I know it was an old thread....I was just adding in case anyone else runs into this.
I was able to add a trigger to restorehistory, but it never fires.
August 8, 2016 at 3:54 am
The trigger will fire if you insert data just manually!:cool: but with restore operation it will not fire. try it.:hehe:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply