SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script Out Database Mail Settings?


Script Out Database Mail Settings?

Author
Message
rsnyderdba
rsnyderdba
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 242
Very helpful, script allows me to uniformly add SMTP mail to various SQL servers from one original source - thank you
mrrobsewell
mrrobsewell
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 673
I love Powershell

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$Server = "ServerName";
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $Server
$srv.Mail.Script();

http://sqldbawithabeard.com Its where I bloghttp://sqlps.io PASS PowerShell Virtual Chapter http://sqlps.io/slack SQL Community Slack Channelhttp://sqlps.io/vote Help Microsoft prioritise improvements to sqlserver PowerShell modulehttp://dbatools.io The Best PowerShell Module for the Modern SQL DBAhttp://dbareports.io PowerShell, SQL, SSRS, PowerBi and Cortana to report on your estate http://sqlps.io/video PASS PowerShell Virtual Chapter YouTube Channel - Subscribe to get notified of new videosSQL South West UK User Grouphttp://sqlsouthwest.co.uk/
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27982 Visits: 39922
mrrobsewell (6/24/2013)
I love Powershell

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
$Server = "ServerName";
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $Server
$srv.Mail.Script();


That's interesting; i bet with some enhancements, that could do the same thing my script was;
i see quite a few differences, so with a 1:1 comparison, my little script is still better, but i'd have to wrestle with powershell and see if I can coax all the same settings out that my script does;

i bet the If Not Exists() is just a setting in SMO.

The scripting of an account in powershell needs a lot of work;

EXEC msdb.dbo.sysmail_add_account_sp
@account_name=N'sqlnotifications@gmail.com',
@email_address=N'sqlnotifications@gmail.com',
@display_name=N'SQL Notifications',
@replyto_address=N'sqlnotifications@gmail.com',
@description=N'Google gMail account'


but my script gets quite a few more settings:


IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = 'sqlnotifications@gmail.com')
BEGIN
--CREATE Account [sqlnotifications@gmail.com]
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'sqlnotifications@gmail.com',
@email_address = 'sqlnotifications@gmail.com',
@display_name = 'SQL Notifications',
@replyto_address = 'sqlnotifications@gmail.com',
@description = 'Google gMail account',
@mailserver_name = 'smtp.googlemail.com',
@mailserver_type = 'SMTP',
@port = '587',
@username = 'sqlnotifications@gmail.com',
@password = 'NotTheRealPassword',
@use_default_credentials = 0 ,
@enable_ssl = 0 ;
END --IF EXISTS account



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!

PeterX2
PeterX2
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
This is brilliant, thanks Lowell.
Gary Gwyn
Gary Gwyn
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 572
Very nice, Lowell!! Thank you!

Blog: http://sqlexchange.wordpress.com
Charles Wannall
Charles Wannall
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 31
Thank you for this extremely useful script. I was just setting up our new server and had disabled some notices in procedures pending learning (again) how to set up email and which accounts we had used when we set up the other server three years ago.

I found this thread and your script. Copied it. Ran it. Ran the result. Done.

That's a nice thing you did. Many thanks.
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27982 Visits: 39922
Thanks for the feedback Charles! It still gives me that warm feeling of a job well done when someone says i helped them.

I think this one has been a script a lot of folks have added to their toolbelt;
So glad it worked out well for you; I guess the only thing you had to do was find and change the password for your SMTP 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!

Peter Phipps
Peter Phipps
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 359
A good script that I have used a few times. Just found out today that because it is based on profiles (inner join ...) it does not script accounts that don't have a profile. I added the following ...

-- Accounts without profiles (added 4-Sep-2014)
SELECT @TheResults = @TheResults + '
--#################################################################################################
-- BEGIN Mail Settings ' + a.name + ' (no 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
--#################################################################################################
-- Drop Settings For ' + a.name + '
--#################################################################################################
/*
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
*/
'
FROM msdb.dbo.sysmail_account a
LEFT JOIN msdb.dbo.sysmail_profileaccount pa ON a.account_id = pa.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
WHERE pa.profile_id IS NULL
DavidKBennett
DavidKBennett
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 412
This is an awesome script. I am adding it to my box of scripts and putting a reference to this thread int he comment block so that I can't forget where it came from.
Dave
isd83
isd83
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 574
Lowell,

Excellent script. You have saved me few hours of work. Thanks!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search