Automatic script backup of mail profile in SQL Server by SSIS
DBA should ever ready for Disaster recovery and provide high availability. It is not enough for DBA to take backup of database, set mirroring/replication of database. There are lot outside the database too, which is needed for failover or for preparing another server instead of regular server.
I have already provided way to “script all SQL Server Job automatically by SSIS and SMO” and “Automatic script backup of email operator in SQL Server by SSIS and SMO”, today I come up with article which generates the script for all mail profile in SQL Server instance by SSIS.
Mail Profile is one of the important configuration for DBA because of this mail profile I get to know the status of many different jobs and other important things. When I failover (manually or automatically) the server, I suppose to have same mail profile(s) and hence I used to script it with me so that I can use the latest script in an emergency situation.
Let us now create one SSIS package to generate the script of all mail profile of SQL Server. I used to keep weekly schedule for this SSIS package in SQL Server Job Agent so that I have latest script of email operator every week.
Let us now create new SSIS project and start creating package.
Have one “Data Flow Task” in your package and double click on that.
Once you double click on “Data Flow Task” you will be moved to “Data Flow” tab, right beside “Control Flow” tab above the “Data Flow Task”. Have one “OLE DB Source” task in “Data Flow” tab. Double click on “OLE DB Source” task, click on “New” button to create connection with your database and have following “SQL Command”.
SELECT '--Create Account' AS CMD UNION ALL SELECT 'EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = ''' + m.name + ''', @description = ''' + m.description + ''', @email_address = ''' + m.email_address + ''', @replyto_address = ''' + m.replyto_address + ''', @display_name = ''' + m.display_name + ''', @mailserver_name = ''' + s.servername + ''', @mailserver_type = ''' + s.servertype + ''', @port = ''' + cast(s.port as nvarchar) + ''', @username = ''' + isnull(c.credential_identity,0) + ''', @password = ''x'', @use_default_credentials = 0, @enable_ssl = 0' AS CMD FROM msdb.dbo.sysmail_account m LEFT OUTER JOIN msdb.dbo.sysmail_server s ON m.account_id = s.account_id LEFT OUTER JOIN master.sys.credentials c ON s.credential_id = c.credential_id UNION ALL SELECT '--Create Profile' AS CMD UNION ALL SELECT ' EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ''' + name + ''', @description = ''' + isnull(description,'NULL')+ ''' ' AS CMD FROM msdb.dbo.sysmail_profile UNION ALL SELECT '--Create Link For Profile to Account' AS CMD UNION ALL SELECT ' EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = '''+ a.name +''', @account_name = ''' + b.name + ''', @sequence_number = 1 ' as CMD FROM msdb.dbo.sysmail_profile as a JOIN msdb.dbo.sysmail_account as b on a.name = b.name GO
Here is the screen capture of “OLE DB Source” property.
Once you are done with “OLE DB Source” task, take one “Flat File Destination” task and connect it with “OLE DB Source” task. Double click on “Flat File Destination” to set its property.
Click on “New” button to create “Flat File Connection”, I am going to give path of blank file “MailProfile.sql” which I already had. You can keep blank SQL file in your destination.
Click on “Ok” button from “Flat File Connection Manager Editor” and go to “Mappings” tab in “Flat File Destination Editor” to confirm whether our “CMD” column from “OLE DB Source” is mapped with “Flat File” or not then click on “OK” button again and execute package. It should run successfully if credentials and path given are right.
We have many different server/instance and need script for all mail profiles from all server and hence I have had loop for all our database server and “Data Flow task” under the loop so that one package connect to each server, take a script of all mail profile and save it to location I have specified.
One of my team member and enthusiast SQL geek Mr. Nirav Gajjar (F) has helped me to develop this package and test it thoroughly.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.