How to Create SQL Server Database Mail on Multiple Servers

SQL Server’s Database mail can never be skipped as it is very important and has to be enabled to send notifications to end users. Setting database mail on a single server is not a tough task as it can be easily completed using the SQL Server management studio. However, it will be a hectic task if you are setting SQL Server Database mail on hundreds of server. Use this script to automate it.

Create Table

The very first step in this automation is to create a table to hold server names.

Create table srvr (srvrname varchar(100))

After creating the table, you can insert all your server names using Insert statement. You can insert multiple values as show in the below statement.

Insert into srvr values('<SERVER 1>'),('<SERVER 1>')

The easy way is to import an Excel file containing server names through management studio or you can use bcp command to import data from text file .

bcp <YOUR DATABASE NAME>.<SCHEMA NAME>.srvr in C:\Files\srvrnames.csv -c -t, -S <YOUR CENTRAL SERVER NAME> -T –E

Create .SQL file

After updating place holders in the below script, save the script as a .sql file.

USE master
GO

sp_configure 'show advanced options'
    ,1
GO

RECONFIGURE
WITH OVERRIDE
GO

sp_configure 'Database Mail XPs'
    ,1
GO

RECONFIGURE
GO

USE msdb
GO

DECLARE @cmd VARCHAR(1000)
DECLARE @vprofilename VARCHAR(1000)
DECLARE @vprofiledescription VARCHAR(1000)

SET @vprofilename = 'ADD A PROFILE NAME HERE'
SET @vprofiledescription = 'ADD A PROFILE DESCRIPTION HERE'
SET @cmd = 'EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ''' + @vprofilename + ''', @description = ''' + @vprofiledescription + ''''

EXEC (@cmd)

DECLARE @vaccountname VARCHAR(1000)
DECLARE @vaccountdescription VARCHAR(1000)
DECLARE @vemailaddress VARCHAR(1000)
DECLARE @vdisplayname VARCHAR(1000)
DECLARE @vmailservername VARCHAR(1000)
DECLARE @vport INT

SET @vaccountname = 'GIVE A SUITABLE ACCOUNT NAME'
SET @vaccountdescription = 'ADD DESCRIPTION HERE'
SET @vemailaddress = 'ADD AN EMAIL ADDRESS HERE. USUALLY IT WILL BE NOREPLY@COMPANYNAME.COM'
SET @vdisplayname = 'GIVE A SUITABLE DISPLAY NAME FOR THIS ACCOUNT'
SET @vmailservername = 'MAIL SERVER NAME'
SET @vport = 25
SET @cmd = 'EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = ''' + @vaccountname + ''', @description = ''' + @vaccountdescription + ''','
SET @cmd = @cmd + '@email_address = ''' + @vemailaddress + ''','
SET @cmd = @cmd + '@display_name = ''' + @vdisplayname + ''','
SET @cmd = @cmd + '@mailserver_name = ''' + @vmailservername + ''','
SET @cmd = @cmd + '@port = 25'

EXEC (@cmd)

SET @cmd = 'EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = ''' + @vprofilename + ''','
SET @cmd = @cmd + '@account_name = ''' + @vaccountname + ''','
SET @cmd = @cmd + '@sequence_number = 1'

EXEC (@cmd)

SET @cmd = 'EXEC master.dbo.xp_instance_regwrite'
SET @cmd = @cmd + 'N''HKEY_LOCAL_MACHINE'','
SET @cmd = @cmd + 'N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'','
SET @cmd = @cmd + 'N''UseDatabaseMail'','
SET @cmd = @cmd + 'N''REG_DWORD'', 1'

EXEC (@cmd)

SET @cmd = 'EXEC master.dbo.xp_instance_regwrite'
SET @cmd = @cmd + 'N''HKEY_LOCAL_MACHINE'','
SET @cmd = @cmd + 'N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'','
SET @cmd = @cmd + 'N''DatabaseMailProfile'','
SET @cmd = @cmd + 'N''REG_SZ'','
SET @cmd = @cmd + 'N''' + @vprofilename + ''''

PRINT @cmd

EXEC (@cmd)

Deploy the script:

In the below script, update the path of the .sql file that we created in the previous step. Run this script through a query window and the database mail will be created and enabled on all servers from the Server table.

DECLARE @pth VARCHAR(1000)

SET @pth = '<PATH OF THE .SQL FILE>'

DECLARE @cmd VARCHAR(1000)
DECLARE @srvr VARCHAR(1000)

DECLARE C1 CURSOR
FOR
SELECT srvrname
FROM srvr

OPEN C1

FETCH NEXT
FROM C1
INTO @srvr

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'SQLCMD -S ' + @srvr + ' -E -i "' + @pth + ''''

    EXEC xp_cmdshell @cmd

    FETCH NEXT
    FROM C1
    INTO @srvr
END

CLOSE C1

DEALLOCATE C1

Modify the script:

As you can see, this script uses same Account name and Profile name for all servers in your environment. You can easily customize this script to use different Account name and Profile name for different servers. You can use sqlcmd to send a test email using the account and profile that we just created. Now you can easily set and receive notification for events occurring on your servers. Please do note that MSDB is the key database in this process. However if you have damaged mdf database you will not be able to use this script.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook pst file problem and excel recovery software products. For more information visit www.datanumen.com