Include log text in notification emails

ev89pimpev89pimp Posts: 10
edited October 10, 2006 9:38AM in SQL Backup Previous Versions
Is there a way to get the output from Sql Backup Restores to be outputted in the notifications that are sent out by SQL 2005? I'm using the extended stored procedure and it would be nice if the actions performed could be included in the email.

Has anyone done this?

thanks,
E

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Elvis,

    It looks like in order to use SQL Server 2005 notification services, you need to create a CLR assembly to do the subscriptions, etc. But SQL Server 2005 does have a 'notifications' category on the job settings.

    SQL Server 2000 required you to have SQLMail and Outlook client installed for the notifications to work; sorry I don't know if this is still true for SQL 2005's job notifications.

    Unfortunately email notification only works for backups in SQL backup, but not restore operations.
  • peteypetey Posts: 2,358 New member
    EXEC master..sqlbackup '-sql "RESTORE DATABASE pubs FROM DISK = [e:\backups\pubs.sqb] WITH MAILTO = [elvis@graceland.com] " '

    ought to send you the details of the restore process.

    Or are you referring to the use of SQL Server 2005s' notification services to send contents of the SQL Backup process log?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    MAILTO does work in RESTORE commands. Sorry for the misunderstanding.
  • I'm willing to use MAILTO as a workaround, but what I was really looking for was a way for SQB to pass the results of the stored procedure to SQL's own reporting system. I'm not using notification services yet, just database mail with SMTP.
    Basically, when I run the command used in a stored procedure in a query window, the results I see in the lower pane is what I would like to see in an email.

    thanks
  • peteypetey Posts: 2,358 New member
    Let's look at the why of your needs, instead of the how, to understand your requirements.

    What is it that is achievable using the 'SQL reporting system', that is not obtainable using the MAILTO option?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Hi,
    The reason I would like to be able to get results via SQL emails instead of SQB is simply for consolidation reasons. I'd rather not get job emails from SQL, then job emails from SQB as well.. Also, when notifications and email addresses are added/changed, I would have to change the mailto address in each and every job... definitely not scalable.

    Like I said, if I had to use SQB mailto I will, but I see it as a short term solution as we still have only 12 DBs we are log shipping, but that could change in the near future.

    Thanks,
    E
Sign In or Register to comment.