Sql-server – Use @@servername and db_name() on notify operator task

maintenance-planssql serversql-server-2008-r2

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.

Best Answer

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.

Related Question