setup db mail

Enable the Database Mail XPs:
USE master
GO
sp_configure ‘show advanced options’,1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘Database Mail XPs’,1
GO
RECONFIGURE
GO

Create a new mail profile:
USE msdb
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘admin’,
@description = ‘Profile for sending Automated DBA Notifications’
GO

Create an account for the notifications (changing the email address, mail server, port as appropriate to your environment):
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQLAlerts’,
@description = ‘Account for Automated DBA Notifications’,
@email_address = ‘sqlalerts@example.com’,
@display_name = ‘SQL Alerts’,
@mailserver_name = ‘smtp.example.com’,
@port = 25
GO

Add the account to the profile:
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘admin’,
@account_name = ‘SQLAlerts’,
@sequence_number = 1
GO

sending an email to test the configuration is working as expected:
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = ‘admin’,
@recipients = ‘mail@example.com’,
@Subject = ‘Test Message generated from SQL Server Database Mail’,
@Body = ‘This is a test message from SQL Server Database Mail’
GO

http://www.snapdba.com/2013/04/enabling-and-configuring-database-mail-in-sql-server-using-t-sql/

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.