T-SQL Script to Extract SQL Server Database Mail Settings

To generate a script that replicates SQL Server Database Mail configurations, we can query specific system tables located in the MSDB database. These tables store all necessary metadata for mail profiles, accounts, and their associated settings.

The key MSDB tables used in this script include:

• msdb.dbo.sysmail_profile
• msdb.dbo.sysmail_profileaccount
• msdb.dbo.sysmail_account
• msdb.dbo.sysmail_server

Below is a T-SQL script that dynamically generates a complete setup script for Database Mail, including profiles, accounts, servers, and associations. It ensures that configurations can be easily re-created or migrated to another SQL Server instance.

-- Script to extract and generate SQL Server Database Mail configuration
-- Queries sysmail_* tables and generates CREATE statements

USE msdb;
GO

DECLARE @TheResults VARCHAR(MAX),
        @vbCrLf CHAR(2);

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

-- [Insert the long SELECT @TheResults = @TheResults + ... statement from your script here]

-- Use Tally table to split script lines for output (ensures proper formatting)
-- [Insert the Tally table and ItemSplit CTE as in your original script]

SELECT 
    ROW_NUMBER() OVER (ORDER BY ItemOrder) AS ItemID,
    Item
FROM ItemSplit;