database mail export

Question

database mail export

Sailor

SSCertifiable

Points: 5246
More actions
April 28, 2011 at 1:48 pm

#249483

I'm doing an upgrade from SQL 2005 to SQL 2008. Is there any way to export or generate a script of all my database mail profiles and accounts so I can run it on the new SQL 2008 box?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

yes, except for the passwords used for the mailserver's AUTH command; those are in a CREDENTIAL that I've never figured out how to script out yet;

however the rest of it's scriptable:

see this post i did a while back when i wanted to do the same thing:

Script Out Database Mail Settings?:

http://www.sqlservercentral.com/Forums/Topic982618-391-1.aspx

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: 323518 More actions · Answer 2

i think this is my "final" version of the script; hope this helps you:

--Reverse_Engineer_database_Mail_Settings.sql

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 = ''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

'

+ '

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

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

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

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

/*

IF 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

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 sysmail_profile p

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

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

LEFT OUTER JOIN 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), -- 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!

Sailor SSCertifiable Points: 5246 More actions · Answer 3

Sailor

SSCertifiable

Points: 5246

April 29, 2011 at 5:54 am

#1317745

Excellent! Thank you very much.

Sailor SSCertifiable Points: 5246 More actions · Answer 4

I'm getting an error

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

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

-- BEGIN Mail Settings JoeJohns

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

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

BEGIN

--CREATE Profile [JoeJohns]

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'JoeJohns',

@description = '';

END --IF EXISTS profile

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

BEGIN

--CREATE Account [Joe Johns]

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Joe Johns',

@email_address = 'joejohns@mycompany.com',

@display_name = 'Joe Johns',

@replyto_address = 'joejohns@mycompany.com',

@description = '',

@mailserver_name = '10.15.1.10',

@mailserver_type = 'SMTP',

@port = '25',

@username = NULL ,

@password = NULL,

@use_default_credentials = 0 ,

@enable_ssl = 0 ;

END --IF EXISTS account

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 = 'JoeJohns'

AND a.name = 'Joe Johns')

We don't require auth on smtp, so I changed to null for password.

Msg 208, Level 16, State 1, Line 31

Invalid object name 'sysmail_profileaccount'.

Sailor SSCertifiable Points: 5246 More actions · Answer 5

I have added msdb.dbo to various tables on the actual script that it creates...