Migrating SQL Server Database Mail Settings

1152 views 03:59 0 Comments 16 November 2022

Migrating SQL Server Database Mail Settings

Migrating SQL Server Database Mail profiles, accounts and settings can be a complex and time-consuming process. This article will provide an overview of what SQL Server Database Mail is, how to migrate SQL Server Database Mail profiles, and how to migrate SQL Server Database Mail accounts.

What is SQL Server Database Mail?

SQL Server Database Mail is a feature that allows you to send e-mail messages from within SQL Server. It includes several components, such as the Database Mail Configuration Wizard, which helps you set up the various components of Database Mail. There are also two stored procedures, sp_send_dbmail and sp_configure_dbmail, which are used to configure Database Mail and to send e-mail messages, respectively.

Scenario

Last weekend I was working on an on-premises to Azure migration. As per the plan, we migrated all databases and other required database objects from an on-premises server to an Azure server. But I faced difficulties while migrating DB mail profiles, accounts, and configuration. I found a couple of profiles and accounts on-premises servers and if we start manually recreating the same it will be time-consuming and error-prone. So I asked for support from a senior database administrator. He provided me with a script and show me how to do the task.

Solution

All we need to do is run this script in a new query window,

USE msdb
GO

SET NOCOUNT ON
GO

SELECT 'exec  msdb.dbo.sysmail_add_profile_sp  ''' + name + '''
, ''' + isnull(description, name) + ''''
FROM sysmail_profile

SELECT 'EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = ''' + name + '''
,   @description= ''' + description + '''
,   @email_address = ''' + email_address + '''  
,   @display_name = ''' + display_name + '''  
,   @mailserver_name = ''smtp.xxxx.com'' 
,   @port = 587
,   @enable_ssl = 0
,   @username = ''xxxxxx''
,   @password = ''xxxxxx'' '
FROM sysmail_account

SELECT 'exec msdb.dbo.sysmail_add_profileaccount_sp 
	@profile_name  = ''' + p.name + '''
	, @account_name = ''' + a.name + '''
	, @sequence_number  = ''' + convert(VARCHAR(10), pa.sequence_number) + ''''
FROM sysmail_profileaccount pa
INNER JOIN sysmail_account a ON a.account_id = pa.account_id
INNER JOIN sysmail_profile p ON p.profile_id = pa.profile_id

Note: Below information, we have to provide before executing the script.

@mailserver_name = ”smtp.xxxx.com” , [Name of the smtp we have to mention here]

@port = 587 , [Port for smtp we have to mention here]

@enable_ssl = 0 , [SSL need to be use or not we have to mention here zero or one]

@username = ”xxxxxx” , [User name/email we have to mention here]

@password = ”xxxxxx” ‘ [Password for the User name/email we have to mention here]

Steps

To run a script on a Microsoft SQL Server using SSMS.

  • Open Microsoft SQL Server Management Studio
  • Select [New Query] from the toolbar
  • Copy the ‘Example Query’
  • Select the database to run the query against
  • Paste the ‘Example Query’ into the query window
  • Click the [Execute] button

Result

Creating profiles

Creating accounts

Adding profiles with accounts

Leave a Reply

Your email address will not be published. Required fields are marked *