Generate Scripts from existing email configurations

 

Generate SQL Server Database Mail Configuration Scripts Using T-SQL

Setting up Database Mail in SQL Server is an important task, especially for sending alerts, job notifications, or custom messages from your SQL instance. While this can be configured through SQL Server Management Studio (SSMS), generating a repeatable T-SQL script allows you to automate and replicate your mail configuration across environments.

In this post, we'll walk through a T-SQL script that dynamically generates the configuration commands for existing Database Mail profiles, accounts, and server settings. This script pulls metadata from msdb and creates a reusable script block for recreating the configuration elsewhere.

---

Overview of the Script

The script does the following:

1. Enables required configuration settings (Database Mail XPs).

2. Generates script blocks to:

   • Create mail profiles if they don’t exist.

   • Create mail accounts with their respective settings.

   • Associate mail accounts with profiles.

3. Optionally includes commented-out commands to drop these configurations.

This is useful for backup purposes or for migrating Database Mail configurations to another server.

---

T-SQL Script for Mail Configuration Generation

Here’s the simplified logic behind the actual T-SQL that builds the configuration script:

USE msdb;
GO

DECLARE @TheResults VARCHAR(MAX), 
        @vbCrLf CHAR(2);
        
SET @vbCrLf = CHAR(13) + CHAR(10);
SET @TheResults = '
USE master
GO
sp_configure ''show advanced options'', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ''Database Mail XPs'', 1
GO
RECONFIGURE
GO
';

-- Append configuration for each mail profile/account
SELECT @TheResults = @TheResults + '
--------------------------------------------------------------------------------------------------
-- BEGIN Mail Settings: ' + p.name + '
--------------------------------------------------------------------------------------------------

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')
BEGIN
    EXEC msdb.dbo.sysmail_add_profile_sp 
        @profile_name = ''' + p.name + ''',
        @description = ''' + ISNULL(p.description, '') + ''';
END;

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')
BEGIN
    EXEC msdb.dbo.sysmail_add_account_sp 
        @account_name = ''' + a.name + ''',
        @email_address = ''' + a.email_address + ''',
        @display_name = ''' + a.display_name + ''',
        @replyto_address = ''' + a.replyto_address + ''',
        @description = ''' + a.description + ''',
        @mailserver_name = ''' + s.servername + ''',
        @mailserver_type = ''' + s.servertype + ''',
        @port = ''' + CONVERT(VARCHAR, s.port) + ''',
        @username = ''' + ISNULL(c.credential_identity, '') + ''',
        @password = ''NotTheRealPassword'',
        @use_default_credentials = ' + CAST(s.use_default_credentials AS VARCHAR) + ',
        @enable_ssl = ' + CAST(s.enable_ssl AS VARCHAR) + ';
END;

IF NOT EXISTS (
    SELECT 1
    FROM msdb.dbo.sysmail_profileaccount pa
    JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
    JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
    WHERE p.name = ''' + p.name + ''' AND a.name = ''' + a.name + ''')
BEGIN
    EXEC msdb.dbo.sysmail_add_profileaccount_sp 
        @profile_name = ''' + p.name + ''',
        @account_name = ''' + a.name + ''',
        @sequence_number = ' + CONVERT(VARCHAR, pa.sequence_number) + ';
END;

/* -- Uncomment to generate cleanup (drop) statements
IF EXISTS (...) -- Checks and deletes associations, accounts, and profiles
BEGIN
    EXEC msdb.dbo.sysmail_delete_profileaccount_sp ...
    EXEC msdb.dbo.sysmail_delete_account_sp ...
    EXEC msdb.dbo.sysmail_delete_profile_sp ...
END;
*/
'
FROM msdb.dbo.sysmail_profile p
JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id
JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
LEFT JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id
LEFT JOIN sys.credentials c ON s.credential_id = c.credential_id;

-- Split the generated text into rows for easier reading
;WITH Tally(N) AS (
    SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_objects
),
ItemSplit AS (
    SELECT 
        N,
        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf, N + LEN(@vbCrLf),
                  CHARINDEX(@vbCrLf, @vbCrLf + @TheResults + @vbCrLf, N + LEN(@vbCrLf)) - N - LEN(@vbCrLf)) AS Item
    FROM Tally
    WHERE N < LEN(@vbCrLf + @TheResults)
      AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf, N, LEN(@vbCrLf)) = @vbCrLf
)
SELECT ROW_NUMBER() OVER (ORDER BY N) AS LineNumber, Item
FROM ItemSplit;

---

Why Use This Script?

• Documentation: Quickly generate a readable backup of your email configuration.

• Migration: Move Database Mail settings to another SQL Server instance with minimal effort.

• Automation: Integrate this logic into CI/CD pipelines or setup scripts.

🔐 Note: The script replaces real passwords with 'NotTheRealPassword'. Always handle credentials securely when adapting this for production use.