2

I'm setting up a maintenance plan and on error I added a "Notify operator task" with some text. But I want to be able to add something like:

Backup failed on MyDatabase\Instance_name...blablabla

I guess I should use @@SERVERNAME and DB_NAME(db.database_id) someway but so far can't find how to do it, maybe is trivial but no luck find in it.

Improve this question

1 Answer 1

Reset to default
3

Notify Operator Tasks are independent of a database context.

Furthermore, the text that's entered into the Properties dialog box gets passed into a statement resembling the following (obtained by clicking the View T-SQL button):

EXECUTE msdb.dbo.sp_notify_operator @name=N'My Operator',
    @subject=N'Subject',@body=N'Message Body'

So what do you do?

Getting the server context is easily solved by switching to an Execute T-SQL Statement Task where you can put whatever SQL you want in there.

That doesn't solve the database name issue, though, because you'll get back either master or msdb and not any particular user database. I don't believe it's possible using Maintenance Plans to get the database name for what you're trying to do. The Back Up Database Task is entirely self-contained.

What you would need to do is use SSIS proper and put a Back Up Database Task and Notify Operator Task within a loop container, where the flow would be executed for each database individually.

Or, you could take a completely different approach and use a script-based backup solution and customize it to suit your exact needs.

Improve this answer
6
  • Thanks Jon, nice answer. And what you would suggest as "different approach"? Maybe using Ola Hallengren maintenance scripts? Does it allows to have this kind of tweaks?
    – Yaroslav
    Commented Jan 30, 2014 at 19:39
  • 1
    @Yaroslav: Yes, exactly. There are a bunch of good ones out there, all free and open source. They're just stored procedures, so you can modify them any way you want.
    – Jon Seigel
    Commented Jan 30, 2014 at 19:42
  • Ok, will check it out as soon as possible, thanks again.
    – Yaroslav
    Commented Jan 30, 2014 at 19:43
  • @Yaroslav: You're welcome.
    – Jon Seigel
    Commented Jan 30, 2014 at 19:44
  • 2
    For the server name portion, I recommend changing the DISPLAY NAME in the mail account configuration to include the instance name. For example, all the e-mails from my servers show "[InstanceName] SQL Server Alert" in the FROM column in my mail client. That way there is no confusion regarding what server sent the alert.
    – SomeGuy
    Commented Jan 30, 2014 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.