配置database mail的TSQL

First, execute below SQLs

 

Rememberto change display name marked with RED inside the script

 

l  Enable SQL Server mail

USE master

GO

sp_configure 'show advanced options',1

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure 'Database Mail XPs',1

GO

RECONFIGURE

GO

 

l  Add profile

USE msdb

GO

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DBA',

@description = 'Profile for sendingAutomated DBA Notifications'

GO

 

l  Add account

/*Need to change display name as SQL Alerts from “SERVERNAME.domain.  For example, SQL Alertsfrom [***]*/

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'DBA@***.com',

@description = 'Account for Automated DBANotifications',

@email_address ='notify@***.com',

@display_name = 'SQL Alerts from []',

@replyto_address ='.notify@***.com',

@mailserver_name ='smtprelay.***.local',

@port = 25

GO

 

l  Connect profile andaccount

EXECUTEmsdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'DBA',

@account_name = 'DBA@***.com',

@sequence_number = 1

GO

 

l  Change SQL Server Agentproperty and this change needs restart SQL Server Agent (below snapshot justillustrates the next SQL will do).

Use msdb

GO

EXEC msdb.dbo.sp_set_sqlagent_properties@email_save_in_sent_folder=1

GO

EXEC master.dbo.xp_instance_regwriteN'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',N'UseDatabaseMail', N'REG_DWORD', 1

GO

EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',N'REG_SZ', N'DBA'

GO

 

l  Then restart SQL ServerAgent

l  Execute below SQLs to senda test email

/*Need to change body as This is a test message from “SERVER NAME.domain".  For example, This is a test message from[***]*/

EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'DBA',

@recipients = '***@***com',

@Subject = 'Test Message generated from SQLServer Database Mail',

@Body = 'This is a test message from SQL Server []'

GO

 

l  Add operator

EXEC msdb.dbo.sp_add_operator @name=N'DBA',

@enabled=1,

@weekday_pager_start_time=0,

@weekday_pager_end_time=235959,

@saturday_pager_start_time=0,

@saturday_pager_end_time=235959,

@sunday_pager_start_time=0,

@sunday_pager_end_time=235959,

@pager_days=127,

@email_address=N'***',

@category_name=N'[Uncategorized]'

GO

 

l  Define notification for ajob

/*Fill in job name*/

USE msdb

GO

EXEC msdb.dbo.sp_update_job @job_name='',

@notify_level_email=2,

@notify_level_netsend=2,

@notify_level_page=2,

@notify_email_operator_name=N'DBA'

GO