April 28, 2011 at 1:48 pm
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?
April 28, 2011 at 2:15 pm
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
April 28, 2011 at 3:05 pm
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 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
April 29, 2011 at 5:54 am
Excellent! Thank you very much.
May 13, 2011 at 1:41 pm
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'.
May 13, 2011 at 1:49 pm
I have added msdb.dbo to various tables on the actual script that it creates...
--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 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 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 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply