May 4, 2005 at 1:41 pm
Hello,
MSSQL 2000/SP3
Any suggestions on how to create an alert that will trigger when an ad-hoc backup (successful or unsuccessful) is taken/completed for a particular database?
Many thanks. Jeff
May 4, 2005 at 1:52 pm
You could try creating a custom alert which is based on a performance condition. Specifically on the object SQL Server: Databases and on the counter Backup/Restore throughput/sec. Then set it to alert if the counter rises above a certain number. You would have to test this a little to see what the actual readings for this counter are like during a restore and then set the threshold accordingly.
May 4, 2005 at 4:01 pm
Hello,
Did you try MSDB database Backupfile table? It contains a record for each file that is backed up. I backed up my test database 2 times and there are 4 records in this table 2 for each try one for the database file and one for log file. There is also table Backupset that contains more info including dates and times, in this case it contains 2 records one for each backup try.
Maybe you can query this tables?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1lcx.asp
Says that Microsoft does not support triggers defined on the system tables; they may alter the operation of the system. So I would just query these tables with the VBscript that will send you an email or email to your pager right away. Run this script every 5 min as a job
Yelena
Regards,Yelena Varsha
May 5, 2005 at 7:13 am
Hi, great ideas. Yelena - Do you think you could post the script or send email, i would greatly appreaciate it? Is it vb or tsql?
Many thanks. Jeff
May 5, 2005 at 8:56 am
Jeff,
use whatever language you are more comfortable with. It also depends if you have Exchange on the network and can send SQL emails. If not and if you decede to use SQL then you may want to install XPSMTP.dll from http://www.sqldev.net/xp/xpsmtp.htm by the advice of Phill Carter in the earlier discussion at
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=168947#bm169085
This approach with SQL Mail or XPSMTP.DLL is easier then writing custom SMTP email script for running which you also need SMTP server on the network. After installing XPSMTP.DLL you should be able to use xp_smtp_sendmail from T-SQL.
As for the querying the backupfile and backupset tables I don't have a script that does it. You may want to see what fields those tables give you (see System Tables topic in Books Online) and decide what criteria you want to use to send an alert. When you decide, you may send me a message and I will try to help you to script. For example you may want to store previous backup_finish_date somewhere in the custom table and then the script will check if there are newer backups in the backupset table. You may want to return the user_name for example or you may want to exclude all backups where Name is your automated backup name.
Yelena
Regards,Yelena Varsha
May 5, 2005 at 11:34 am
Can't just put a backup script in a 1st step in a Job and notification ( success/fail) as 2nd/3rd step?
May 5, 2005 at 11:43 am
Hi barsuk,
I want to capture ad-hoc db backups being performed by other users ie: have an alert emailed to me when this is done re: to monitor disk capacity and bkup frequency.
Unfortunately, due to the business functionality requirements of a couple of applications, removing backup permissions is not an option ie: the app user account requires "dbo".
Many thanks. Jeff
May 5, 2005 at 2:03 pm
USE MESSAGE 18264 to create the alert!
ex:
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Some body run a BACKUP successfuly'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'Some body run a BACKUP successfuly'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'Some body run a BACKUP successfuly', @message_id = 18264, @severity = 0, @enabled = 1, @delay_between_responses = 60, @include_event_description_in = 5, @category_name = N'[Uncategorized]'
END
Don't forget to set the operator and stuff
hth
* Noel
May 5, 2005 at 4:13 pm
Noel,
This is so cool! I never think of this possibility because we have Lotus Notes and I have to use SMTP mail anyway and can get only NEt Send from the alert.
Yelena
Regards,Yelena Varsha
May 6, 2005 at 2:18 pm
Noel/Yelena,
Great! Works like a charm. Had some challenges with refreshing the alert in EM re:"all databases" vs a single db.
Any idea what the number would be for a successful RESTORE?
Also, why I'm asking, it would be nice to track/send alert when a DDL action (CREATE/ALTER/DROP) has been performed. Any alert hints for this?
Many thanks. Jeff
May 6, 2005 at 4:07 pm
Jeff,
Hint for restore: select * from sysmessages where... There are a couple of IDs for Database and log restore: 18267, 18268 and also for file restore 18269
For tracking DDL I would use Profiler Trace with T-SQL Statement Completed Event and TextData filter on a particular statement and DatabaseID for a particular database. Do not overdo traces, files get big.
Noel, any suggestions for Alert on statements?
Yelena
Regards,Yelena Varsha
May 6, 2005 at 11:46 pm
Tracing is only really an option if you can deal with a fairly substantial overhead.
You should take a look at Imceda's new product called SQL Watch. This product is now available on their website. It does exactly what you're looking for (sends e-mail notification via SMTP mail) whenever a DDL statement is altered.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply