ms sql server database backup trigger

Last Post 25 Jan 2013 12:14 PM by gunneyk. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
borowik22
New Member
New Member

--
24 Jan 2013 04:42 AM
Hi Guys,
I have a task to do.
I have to create a table which holds information about any database bacp-up done on server (dbid from system_databases table, date of backup, kind of backup, file location).
Problem is that I haven't found event which could be used in a trigger.
How can I make a row inserted into this table with all the required information any time back-up is performed? (doesn't matter if back-up is scheduled or performed manually).
Can I use some of system tables, procedures?


thanks!
rm
New Member
New Member

--
24 Jan 2013 05:52 AM
Did you check msdb.dbo.backupmediafamily?
borowik22
New Member
New Member

--
24 Jan 2013 06:34 AM
Thanks I will definitely use this system table.
Problem is how to use it to get data for ma table.
Is far as I know I can't create trigger triggered for event on system table (after update or insert on msdb.dbo.backupmediafamily).
Do you think creating a schedule run every hour with a job which executes my procedure is fine?
Procedure would check any backups from msdb.dbo.backupmediafamily table created not more than an hour ago.
rm
New Member
New Member

--
24 Jan 2013 09:04 AM
Maybe with extend event 'sql_statement_starting' and set 'backup ...' as statement to trace? Trigger your process if the event happens.
gunneyk
New Member
New Member

--
25 Jan 2013 12:14 PM
Extended events may be the answer here or you can simply create a SQL Agent job that periodically polls the msdb tables and updates yours with any new data. You can use the ID's which are sequential to see what is new since the last time you polled it.


Acceptable Use Policy
---