ms sql server database backup trigger

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!

rmNew Member

24 Jan 2013 04:52 AM

Did you check msdb.dbo.backupmediafamily?

borowik22New Member

24 Jan 2013 05: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.

rmNew Member

24 Jan 2013 08:04 AM

Maybe with extend event 'sql_statement_starting' and set 'backup ...' as statement to trace? Trigger your process if the event happens.

gunneykNew Member

25 Jan 2013 11:14 AM

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.