March 2, 2011 at 3:41 pm
Hi,
Question:
Is there a way to script out the database mail configuartions?
Please advice on correct route to take.
Thank you in .
March 3, 2011 at 4:41 am
yes,
you can script the mail configuration. you can read the bol for detailed information.
March 3, 2011 at 4:53 am
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.
Declare @TheResults varchar(max)
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 + '
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE name = ''' + m.name + ''')
BEGIN
--CREATE Account [' + m.name + ']
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ' + CASE WHEN m.name IS NULL THEN ' NULL ' ELSE + '''' + m.name + '''' END + ',
@email_address = ' + CASE WHEN m.email_address IS NULL THEN ' NULL ' ELSE + '''' + m.email_address + '''' END + ',
@display_name = ' + CASE WHEN m.display_name IS NULL THEN ' NULL ' ELSE + '''' + m.display_name + '''' END + ',
@replyto_address = ' + CASE WHEN m.replyto_address IS NULL THEN ' NULL ' ELSE + '''' + m.replyto_address + '''' END + ',
@description = ' + CASE WHEN m.description IS NULL THEN ' NULL ' ELSE + '''' + m.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 = ''NotTheRealPassword'',
@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
'
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
SELECT @TheResults = @TheResults + '
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
'
FROM sysmail_profile p
SELECT @TheResults = @TheResults + '
IF NOT EXISTS(SELECT *
FROM sysmail_profileaccount pa
INNER JOIN sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN 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
'
FROM sysmail_profileaccount pa
INNER JOIN sysmail_profile p ON pa.profile_id = p.profile_id
INNER JOIN sysmail_account a ON pa.account_id = a.account_id
SELECT @TheResults
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply