Script Out Database Mail Settings?

  • Lowell

    SSC Guru

    Points: 323428

    Anyone bother to have created a script to Reverse Engineer / Script out your EXISTING database mail settings?

    I set up a profile to use gMail, and it seems logical for me to export out the settings to a script, then run the script on my laptop, other servers, etc.

    There's no built in option, so I figured i'd ping the forum before i do it myself.

    by the way, my google-fu is strong, and there are example scripts where you fill in the blanks, examples how to set up dbmail, but i did not find anything that scripts out existing settings.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Derrick Smith

    SSChampion

    Points: 10833

    Use one of the creation templates, fill in your info, and then use that on your other few computers? There's not a whole lot of info that needs to be entered...it's probably quicker just to do that than searching for another way.

  • Lowell

    SSC Guru

    Points: 323428

    yeah, digging for stuff is turning into a pain...

    just going for the account info, for example, the password associated to the username used for SMTP AUTH command appears to use something a bit beyond my knowledge;

    i can find the username in select * from sys.credentials, but the password for reverse engineering...hrmmm. sp_helptexting each proc is sending me deeper and deeper into the rabbit hole....

    SELECT '

    --CREATE Account [' + m.name + ']

    EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = ''' + m.name + ''',

    @description = ''' + m.description + ''',

    @email_address = ''' + m.email_address + ''',

    @replyto_address = ''' + m.email_address + ''',

    @display_name = ''' + m.display_name + ''',

    @mailserver_name = ''' + s.servername + ''',

    @mailserver_type = ''' + s.servertype + ''',

    @port = ''' + s.port + ''',

    @username = ''' + c.credential_identity + ''',

    @password = ''x'',

    @mailserver_name = ''mail server address''

    @use_default_credentials = ''x'',

    @enable_ssl = ''x'',

    ' AS CMD

    from sysmail_account m

    LEFT OUTER JOIN sysmail_server s

    ON m.account_id = s.account_id

    LEFT OUTER JOIN sys.credentials c

    ON s.credential_id = c.credential_id

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell

    SSC Guru

    Points: 323428

    As a solution to my own question, one of the real problems is that a CREDENTIAL is created to hold the password for the SMTP user; i do not know of any way to script out the password from the credentials.

    dayum.

    anyway, with the exception of the password, which i would have to change in the generated script, this script below does what i was looking for: this may help others in the future, especially if you are like me and like to script everything for disaster recovery and rebuilding and stuff.

    The code below Reverse Engineers Database Mail Settings.

    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

    --sp_configure ''SQL Mail XPs'',0

    go

    reconfigure

    go

    '

    SELECT @TheResults = @TheResults + '

    --#################################################################################################

    -- BEGIN Mail Settings ' + p.name + '

    --#################################################################################################

    IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')

    BEGIN

    --CREATE Profile [' + p.name + ']

    EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = ''' + p.name + ''',

    @description = ''' + ISNULL(p.description,'') + ''';

    END --IF EXISTS profile

    '

    +

    '

    IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')

    BEGIN

    --CREATE Account [' + a.name + ']

    EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = ' + CASE WHEN a.name IS NULL THEN ' NULL ' ELSE + '''' + a.name + '''' END + ',

    @email_address = ' + CASE WHEN a.email_address IS NULL THEN ' NULL ' ELSE + '''' + a.email_address + '''' END + ',

    @display_name = ' + CASE WHEN a.display_name IS NULL THEN ' NULL ' ELSE + '''' + a.display_name + '''' END + ',

    @replyto_address = ' + CASE WHEN a.replyto_address IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address + '''' END + ',

    @description = ' + CASE WHEN a.description IS NULL THEN ' NULL ' ELSE + '''' + a.description + '''' END + ',

    @mailserver_name = ' + CASE WHEN s.servername IS NULL THEN ' NULL ' ELSE + '''' + s.servername + '''' END + ',

    @mailserver_type = ' + CASE WHEN s.servertype IS NULL THEN ' NULL ' ELSE + '''' + s.servertype + '''' END + ',

    @port = ' + CASE WHEN s.port IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',

    @username = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity + '''' END + ',

    @password = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ',

    @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',

    @enable_ssl = ' + CASE WHEN s.enable_ssl = 1 THEN ' 1 ' ELSE ' 0 ' END + ';

    END --IF EXISTS account

    '

    + '

    IF NOT EXISTS(SELECT *

    FROM msdb.dbo.sysmail_profileaccount pa

    INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id

    INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

    WHERE p.name = ''' + p.name + '''

    AND a.name = ''' + a.name + ''')

    BEGIN

    -- Associate Account [' + a.name + '] to Profile [' + p.name + ']

    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = ''' + p.name + ''',

    @account_name = ''' + a.name + ''',

    @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;

    END --IF EXISTS associate accounts to profiles

    --#################################################################################################

    -- Drop Settings For ' + p.name + '

    --#################################################################################################

    /*

    IF EXISTS(SELECT *

    FROM msdb.dbo.sysmail_profileaccount pa

    INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id

    INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

    WHERE p.name = ''' + p.name + '''

    AND a.name = ''' + a.name + ''')

    BEGIN

    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''

    END

    IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + a.name + ''')

    BEGIN

    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''

    END

    IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE name = ''' + p.name + ''')

    BEGIN

    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''

    END

    */

    '

    FROM msdb.dbo.sysmail_profile p

    INNER JOIN msdb.dbo.sysmail_profileaccount pa ON p.profile_id = pa.profile_id

    INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

    LEFT OUTER JOIN msdb.dbo.sysmail_server s ON a.account_id = s.account_id

    LEFT OUTER JOIN sys.credentials c ON s.credential_id = c.credential_id

    ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows

    E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows

    E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows

    E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows

    --E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,

    Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),

    ItemSplit(

    ItemOrder,

    Item

    ) as (

    SELECT N,

    SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))

    FROM Tally

    WHERE N < DATALENGTH(@vbCrLf + @TheResults)

    --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf

    AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter

    )

    select

    row_number() over (order by ItemOrder) as ItemID,

    Item

    from ItemSplit

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell

    SSC Guru

    Points: 323428

    Thanks to a PM from a fellow member here, i found that the script i posted 10 plus months ago had a few minor flaws in it: I've edited the post above to be what i think is the better solution.

    1. if you ran the script and you were not in msdb, you'd get an errror,

    2. two of the parameters had an extra @ symbol where they were not supposed to be, which was what my friend Mark pointed out in a PM,

    3. and finally, i don't like dumping it into a varchar(max) anymore, so i added a splitting on CrLf to get multi row results.

    I fell in love with varchar(max) for a while, and was getting all my results like that, but that affair is over now.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Nice script > briefcasing!.

    Had you considered the profiler method to see what was going on? Might have saved you some time.

  • Lowell

    SSC Guru

    Points: 323428

    Ninja's_RGR'us (8/2/2011)


    Nice script > briefcasing!.

    Had you considered the profiler method to see what was going on? Might have saved you some time.

    no, i fell into the stubborn learn without help mode...that woudl be a good idea though...thanks!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Mark Gimelfarb

    SSC Enthusiast

    Points: 115

    Thank you, Lowell! The script is very helpful--made a major difference for a critical server migration process and I've learned a lot from it!

    The only thing I added is spiced it up with QUOTENAME() around descriptions and some other fields, as some of our folks like to use single quotes in describing the accounts.

  • sarmistead

    SSCommitted

    Points: 1671

    Thanks for doing the work and letting us benefit from your efforts. Your script is helpful.

    Steve

  • McJoe the Carthy

    SSC Enthusiast

    Points: 130

    wanted to echo my thanks for that really helpful script!

  • Mandeep Dulai

    Old Hand

    Points: 361

    Excellent script! Currently migrating to a new SQL Server and this has saved me a ton of time. Thank you!

  • Lowell

    SSC Guru

    Points: 323428

    Mandeep Dulai (3/16/2012)


    Excellent script! Currently migrating to a new SQL Server and this has saved me a ton of time. Thank you!

    glad it helped you out!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • mot256

    Grasshopper

    Points: 13

    Thank you very much for this script!

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    mot256 (4/2/2012)


    Thank you very much for this script!

    +1 Lowell! I knew I would need this some day after reading the post. Today was that day!

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • Lowell

    SSC Guru

    Points: 323428

    SQLKnowItAll (4/10/2012)


    mot256 (4/2/2012)


    Thank you very much for this script!

    +1 Lowell! I knew I would need this some day after reading the post. Today was that day!

    lol thanks everyone; every thank you i get here gives me the warm fuzzies;

    That's the whole reason i donate some time here!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 15 posts - 1 through 15 (of 48 total)

You must be logged in to reply to this topic. Login to reply