SQL – Scripting out DB Mail Config with PowerShell and SMO

Fellow MVP Jonathan Kehayias has a post on scripting out the Database Mail configuration with PowerShell and SMO. While this solution looks perfect, there is a bug in the Mail.Script() output, not mentioned in Jonathan’s post except for one reader’s comment, that does not script out the SMTP server name and port number. (Note, that is not a bug in Jonathan’s code; it is a bug in how Microsoft coded the API.)

This post is to give an example of how I worked around that problem so that I could script out a SQL instance’s mail config and apply it on a new instance as needed. Quite simply, I added a ForEach loop over each account in the Mail object and printed its configured mail server name and port. I just plugged those in as parameters to msdb.dbo.sysmail_update_account_sp. I also added a few more enhancements to Jonathan’s script – resizing the width of the output and adding appropriate sp_configure statements to get things rolling on the new instance. Below is the code for SQL 2005+:

#Original code from Jonathan Kehayias, http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/08/23/scripting-database-mail-configuration-with-powershell-and-smo.aspx

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 25);

"sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'Database Mail XPs',1
RECONFIGURE 
GO
"

#Set the server to script from 
$ServerName = "SQLServerMachine";

#Get a server object which corresponds to the default instance 
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

#Script Database Mail configuration from the server 
$srv.Mail.Script();

ForEach ($account in $srv.Mail.Accounts) {
	$AccountName = $account.Name
	$MailServerName = $account.MailServers[0].Name;
	$MailServerPort = $account.MailServers[0].Port;
	"EXEC msdb.dbo.sysmail_update_account_sp @account_name = N'$AccountName',";
		"	@mailserver_name = N'$MailServerName',";
		"	@port = N'$MailServerPort'";
}

This will give you output such as the following:

sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'Database Mail XPs',1
RECONFIGURE 
GO

EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryAttempts', @parameter_value=N'1', @description=N'Number of retry attempts for a mail server'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryDelay', @parameter_value=N'60', @description=N'Delay between each retry attempt to mail server'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DatabaseMailExeMinimumLifeTime', @parameter_value=N'600', @description=N'Minimum process lifetime in seconds'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DefaultAttachmentEncoding', @parameter_value=N'MIME', @description=N'Default attachment encoding'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'LoggingLevel', @parameter_value=N'2', @description=N'Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'MaxFileSize', @parameter_value=N'1000000', @description=N'Default maximum file size'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'ProhibitedExtensions', @parameter_value=N'exe,dll,vbs,js', @description=N'Extensions not allowed in outgoing mails'
EXEC msdb.dbo.sysmail_add_account_sp @account_name=N'DBMailAccount', 
		@email_address=N'sqlops@ssistalk.com', 
		@display_name=N'SQL Operations'
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'DataManagement'
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'DataManagement', @account_name=N'DBMailAccount', @sequence_number=1
EXEC msdb.dbo.sysmail_update_account_sp @account_name = N'DBMailAccount',
	@mailserver_name = N'mail.ssistalk.com',
	@port = N'25'