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.
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.
All we need to do is run this script in a new query window,
SET NOCOUNT ON
SELECT 'exec msdb.dbo.sysmail_add_profile_sp ''' + name + '''
, ''' + isnull(description, name) + ''''
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'' '
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]
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
Adding profiles with accounts