In case you need to migrate a numerous Database Mail Profiles to another MSSQL server doing this manually is exhausting and error-prone. The script, originally written by Iain Elder and enhanced by Frank Gill generates dynamic SQL statements to recreate database mail accounts and profiles on another SQL server. All you need to do is run this script in a new query window, copy the dynamic SQL statement in a new window and execute it. The script is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
USE msdb; GO /* Author - Frank Gill, Concurrency Date - 2017-08-18 Description - Script queries sys tables to generate dynamic SQL statements to recreate database mail accounts and profiles Code modified to generate multiple mail profiles from the script found here: https://basetable.wordpress.com/2012/04/03/script-out-database-mail-settings/ Note - Execute query in SQL Server Management Studio and output Results to Text (Ctrl + T) Copy the results to a separate query window For mail acounts with passwords, the password will appear as 'NotTheRealPassword' You will need to change this to the real password Each result set will contain a header row of Text Do a group replace (Ctrl + H) of Text to --Text to comment the headers out */ SET NOCOUNT ON; /* Drop and create temp table Declare local variables */ IF OBJECT_ID('tempdb.dbo.#sysmail_info') IS NOT NULL BEGIN DROP TABLE #sysmail_info; END CREATE TABLE #sysmail_info (RowId INT IDENTITY(1,1) ,account_name SYSNAME ,email_address NVARCHAR(120) ,display_name NVARCHAR(128) ,replyto_address NVARCHAR(128) ,[description] NVARCHAR(256) ,servername SYSNAME ,servertype SYSNAME ,[port] INT ,credential_identity NVARCHAR(4000) ,use_default_credentials BIT ,enable_ssl BIT ,profile_name SYSNAME ,sequence_number INT ,database_principal_name SYSNAME NULL ,is_default BIT); DECLARE @loopcount INT = 1, @looplimit INT, @SQLText VARCHAR(MAX), @ProfileText VARCHAR(MAX), @CrLf CHAR(2) = CHAR(13) + CHAR(10); /* Insert the required database mail info into the temp table */ INSERT INTO #sysmail_info (account_name ,email_address ,display_name ,replyto_address ,[description] ,servername ,servertype ,[port] ,credential_identity ,use_default_credentials ,enable_ssl ,profile_name ,sequence_number ,database_principal_name ,is_default) SELECT a.name AS account_name, a.email_address, a.display_name, a.replyto_address, a.[description], servername, servertype, [port], c.credential_identity, s.use_default_credentials, s.enable_ssl, p.name AS profile_name, pa.sequence_number, dp.name AS database_principal_name, pp.is_default FROM msdb.dbo.sysmail_profile AS p INNER JOIN msdb.dbo.sysmail_profileaccount AS pa ON p.profile_id = pa.profile_id INNER JOIN msdb.dbo.sysmail_account AS a ON pa.account_id = a.account_id LEFT OUTER JOIN msdb.dbo.sysmail_principalprofile AS pp ON p.profile_id = pp.profile_id LEFT OUTER JOIN msdb.sys.database_principals AS dp ON pp.principal_sid = dp.sid LEFT OUTER JOIN msdb.dbo.sysmail_server AS s ON a.account_id = s.account_id LEFT OUTER JOIN sys.credentials AS c ON s.credential_id = c.credential_id; /* Set loop limit to max RowId value */ SELECT @looplimit = MAX(RowId) FROM #sysmail_info; /* Generate commands to enable database mail */ SELECT @SQLText = ' EXEC msdb.dbo.sp_configure @configname = ''show advanced options'', @configvalue = 1; RECONFIGURE; EXEC msdb.dbo.sp_configure @configname = ''Database Mail XPs'', @configvalue = 1; RECONFIGURE;'; SELECT @SQLText AS [Text]; /* Loop through each row in the temp table build commands to recreate database mail accounts and profiles */ WHILE @loopcount <= @looplimit BEGIN SELECT @SQLText = ' EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ''' + profile_name + ''', @description = ''' + ISNULL([description],'') + '''; EXEC msdb.dbo.sysmail_add_account_sp @account_name = ' + CASE WHEN account_name IS NULL THEN 'NULL' ELSE + '''' + account_name + '''' END + ', @email_address = ' + CASE WHEN email_address IS NULL THEN 'NULL' ELSE + '''' + email_address + '''' END + ', @display_name = ' + CASE WHEN display_name IS NULL THEN 'NULL' ELSE + '''' + display_name + '''' END + ', @replyto_address = ' + CASE WHEN replyto_address IS NULL THEN 'NULL' ELSE + '''' + replyto_address + '''' END + ', @description = ' + CASE WHEN [description] IS NULL THEN 'NULL' ELSE + '''' + [description] + '''' END + ', @mailserver_name = ' + CASE WHEN servername IS NULL THEN 'NULL' ELSE + '''' + servername + '''' END + ', @mailserver_type = ' + CASE WHEN servertype IS NULL THEN 'NULL' ELSE + '''' + servertype + '''' END + ', @port = ' + CASE WHEN [port] IS NULL THEN 'NULL' ELSE + '''' + CONVERT(VARCHAR,[port]) + '''' END + ', @username = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''' + credential_identity + '''' END + ', @password = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''NotTheRealPassword''' END + ', @use_default_credentials = ' + CASE WHEN use_default_credentials = 1 THEN '1' ELSE '0' END + ', @enable_ssl = ' + CASE WHEN enable_ssl = 1 THEN '1' ELSE '0' END + '; EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = ''' + profile_name + ''', @account_name = ''' + account_name + ''', @sequence_number = ' + CAST(sequence_number AS NVARCHAR(3)) + '; ' + COALESCE(' EXEC msdb.dbo.sysmail_add_principalprofile_sp @profile_name = ''' + profile_name + ''', @principal_name = ''' + database_principal_name + ''', @is_default = ' + CAST(is_default AS NVARCHAR(1)) + '; ', '') FROM #sysmail_info WHERE RowId = @loopcount; WITH R2(N) AS (SELECT 1 UNION ALL SELECT 1), R4(N) AS (SELECT 1 FROM R2 AS a CROSS JOIN R2 AS b), R8(N) AS (SELECT 1 FROM R4 AS a CROSS JOIN R4 AS b), R16(N) AS (SELECT 1 FROM R8 AS a CROSS JOIN R8 AS b), R32(N) AS (SELECT 1 FROM R16 AS a CROSS JOIN R16 AS b), R64(N) AS (SELECT 1 FROM R32 AS a CROSS JOIN R32 AS b), R128(N) AS (SELECT 1 FROM R64 AS a CROSS JOIN R64 AS b), Tally(N) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R128 ), SplitText(SplitIndex, [Text]) AS ( SELECT N, SUBSTRING( @CrLf + @SQLText + @CrLf, N + DATALENGTH(@CrLf), CHARINDEX( @CrLf, @CrLf + @SQLText + @CrLf, N + DATALENGTH(@CrLf) ) - N - DATALENGTH(@CrLf) ) FROM Tally WHERE N < DATALENGTH(@CrLf + @SQLText) AND SUBSTRING(@CrLf + @SQLText + @CrLf, N, DATALENGTH(@CrLf)) = @CrLf ) SELECT [Text] FROM SplitText ORDER BY SplitIndex; SELECT @loopcount += 1; END |
