Scripting out DBMail Setup

• Staggerlee

  SSCertifiable

  Points: 5272

  More actions

  March 2, 2012 at 1:52 am

  #257810

  Hi all,

  trying to create a script to run on new machines to get them all set up the same way.

  Part of that is getting db mail on and functioning. The best way to script it out and work was using Jonathan Kehayias's blog script from here:

  http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/08/23/scripting-database-mail-configuration-with-powershell-and-smo.aspx

  I tested a set up then ran the export using the powershell script. But for some reason it doesnt keep the exchange relay details. and puts in the name of the server.

  Anyone know why it would do that or what i can add to fix it.

  sp_CONFIGURE 'show advanced', 1

  GO

  RECONFIGURE

  GO

  sp_CONFIGURE 'Database Mail XPs', 1

  GO

  RECONFIGURE

  GO

  DECLARE @emailAddress varchar(50);

  SET @emailAddress = @@SERVERNAME + '-Notification@domain.co.uk'

  DECLARE @notification varchar(50);

  SET @notification = @@SERVERNAME + ' NOTIFICATION'

  EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryAttempts', @parameter_value=N'1', @description=N'Number of retry attempts for a mail server'

  EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryDelay', @parameter_value=N'60', @description=N'Delay between each retry attempt to mail server'

  EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DatabaseMailExeMinimumLifeTime', @parameter_value=N'600', @description=N'Minimum process lifetime in seconds'

  EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DefaultAttachmentEncoding', @parameter_value=N'MIME', @description=N'Default attachment encoding'

  EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'LoggingLevel', @parameter_value=N'2', @description=N'Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3'

  EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'MaxFileSize', @parameter_value=N'1000000', @description=N'Default maximum file size'

  EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'ProhibitedExtensions', @parameter_value=N'exe,dll,vbs,js', @description=N'Extensions not allowed in outgoing mails'

  EXEC msdb.dbo.sysmail_add_account_sp @account_name=N'Notification',

  @email_address=@emailAddress,

  @display_name=@notification

  EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'Default Public Profile',

  @description=N'Default public profile for all users'

  EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'Default Public Profile', @account_name=N'Notification', @sequence_number=1

  EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest', @profile_name=N'Default Public Profile', @is_default=1

• bruce 1565

  SSCrazy

  Points: 2574

  More actions

  March 2, 2012 at 6:47 am

  #1454132

  The Script method of the Mail object doesn't get the SMTP server information and neither does the Script method for the Accounts object. I guess it's just an SMO thing.

  If you just want DBMail configured the same for new servers have you looked at my article here[/url]? It's what I use for all new installs.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 2, 2012 at 7:16 am

  #1454151

  also, look at my forum post here:

  Reverse Engineer DatabaseMail Settings

  where i answered my own question with a TSQL script;

  note the core issue is the CREDENTIAL for the SMTP user ; i have to substitute a default "NotTheRealPassword" string for that, but otherwise, it scripts out your settings to make it portable from server to server.

  At least for me, i KNOW the SMTP passwords for all the boxes used in SQL Mail, and can modify the script accordingly.

  you get the same issue if you want to script Linked Servers...the remote login is in a credential, that cannot be scripted.

  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!

• bruce 1565

  SSCrazy

  Points: 2574

  More actions

  March 2, 2012 at 7:27 am

  #1454160

  Great script Lowell, it’s definitely going in my traveling SQL bag. Thanks.