November 5, 2003 at 4:21 pm
I'm trying to build an alert that fires when ever a database backup occurs to a particular database. When I have it set to a single database it does not fire, but if I have it set for all databases it fires. But of course it fires for every database backup, not just the one database.
Here is the single database alert that does not fire:
EXECUTE msdb.dbo.sp_add_alert
@name = N'Copy MYDB Database Backups',
@message_id = 18265, @severity = 0,
@enabled = 1, @delay_between_responses = 1,
@include_event_description_in = 0,
@database_name = N'MYDB',
@job_name = N'COPY MYDB BACKUPS TO STANDBY',
@category_name = N'[Uncategorized]'
Here is the database alert that does fire for every database.
EXECUTE msdb.dbo.sp_add_alert
@name = N'Copy MYDB Database Backups',
@message_id = 18265,
@severity = 0,
@enabled = 1,
@delay_between_responses = 1,
@include_event_description_in = 0,
@job_name = N'COPY MYDB BACKUPS TO STANDBY',
@category_name = N'[Uncategorized]'
Anyone know why the first alert does not work, but the second one does?
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 10, 2003 at 5:45 pm
Interesting problem. No ideas yet. Closest link I could find:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_1ypf.asp
Andy
November 17, 2003 at 12:43 pm
Thanks for the thought and link Andy. I did resolve this problem. I got the answer at PASS at the PSS/MVP session. Actuall none of the PSS or MVP panelist solved my problem, but Kimberly Tripp did. Here is a recap of how to set up an alert that fires when a specific database backup is taken.
Set up the alert to fire on error number "18265", on database "master" and have the error message text reference the specific database for which you want the backup to fire. So basically you alert should look like this:
EXECUTE msdb.dbo.sp_add_alert
@name = N'Copy WERD Database Backups',
@message_id = 18264,
@severity = 0,
@enabled = 1,
@delay_between_responses = 0,
@notification_message = N'Testing',
@include_event_description_in = 4,
@database_name = N'master',
@event_description_keyword = N'Database backed up: Database: WERDS',
@job_name = N'COPY WERD BACKUPS TO STANDBY',
@category_name = N'[Uncategorized]'
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 18, 2003 at 6:32 am
Well, that made it worth the trip then! Not intuitive at all.
Andy
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply