This week, I was working on a migration for a client. The migration was moving databases from a stand-alone instance to a two-node Availability Group. When it came to moving the Database Mail settings, I discovered they had 21 sets of profiles and accounts. Not wanting to manually create 42 Database Mail profiles, I set out to automate the process. A web search yielded this blog post by Iain Elder. This script does what I was looking for, but would only generate settings for a single Database Mail profile. Using Iain’s code as a starting point, I modified it to create Database Mail settings for all profiles on an instance. The script is listed below. I hope this simplifies your SQL Server migrations.
Click through for Frank’s script, and you might also be interested in Iain Elder’s script, linked above.