script to migrate or create sql server email accounts and profiles from one server to another

References – Reference 1 Reference 2

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 = ‘ + 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
—————————————————————————————————
— 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 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

;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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s