--desc: scripts out databasemail for migration usage and backup
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 = ' + 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
'
+ '
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
--new block for public profiles and default profiles
IF NOT EXISTS( SELECT * FROM [msdb].[dbo].[sysmail_principalprofile] [pp]
INNER JOIN [msdb].[dbo].[sysmail_profile] p
ON [pp].[profile_id] = [p].[profile_id]
WHERE [p].[name] = ''' + [p].[name] + ''')
BEGIN
-- do stuff with sysmail_add_principalprofile_sp
EXECUTE [msdb].[dbo].[sysmail_add_principalprofile_sp] @profile_name =''' + [p].[name] + ''''
+ CASE
WHEN pp.[principal_sid] = 0x00
THEN ' ,@principal_name = ''public'''
ELSE ' ,@principal_name = ''' + SUSER_NAME(pp.[principal_sid]) + ''''
END
+ CASE
WHEN pp.[is_default] = 1
THEN ',@is_default = 1'
ELSE ',@is_default = 0'
END + '
END
--#################################################################################################
-- 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 [p].[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 [msdb].[dbo].[sysmail_profile] [p]
INNER JOIN [msdb].[dbo].[sysmail_profileaccount] [pa] ON [p].[profile_id] = [pa].[profile_id]
INNER JOIN [msdb].[dbo].[sysmail_account] [a] ON [pa].[account_id] = [a].[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]
LEFT OUTER JOIN [msdb].[dbo].[sysmail_principalprofile] [pp] ON [p].[profile_id] = [pp].[profile_id]
--the lines results might end in a number of different chars:
--clean it Up
SET @TheResults = REPLACE(REPLACE(@TheResults,CHAR(10),CHAR(13) + CHAR(10)),CHAR(13) + CHAR(13), CHAR(13))
;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]
