Script backup of replication setup of SQL Server by SSIS and SMO
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”, “Automatic script backup of email operator in SQL Server by SSIS and SMO”, “Backup Linked Server script in SQL Server by SSIS” and “Automatic script backup of mail profile in SQL Server by SSIS”. Today I come up with article which generates the script for replication publisher, subscriber, article etc. in SQL Server instance by SSIS and SMO.
Replication is one of the important High Availability and disaster recovery option for DBA which is being used by application load balancing too. When I failover (manually or automatically) the server, I suppose to have same replication 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 which uses SMO script to generate the script of replication along with its article 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 replication every week.
Let us now create new SSIS project and start creating package.
Have one “Script Task” in your package and double click on the “Script Task” so that you can get “Script Task Editor”. Click on “Edit Script” button from the “Script Task Editor” and you will get script windows where you can write down C# script (by default you get C# script editor).
You have to add reference for following name spaces.
Microsoft.SQLServer.Replication .NET Programming Interface
Apart from that, have following extra namespaces in “NameSpaces” region in your script window.
Now here is the code you have to place in your “Main” method of script window.
// TODO: Add your code here
ServerName = "ServerName";
UserName = "Login";
Password = "Password";
FolderDate = DateTime.Now.ToShortDateString().Replace(‘\\’,’_’).Replace(‘/’,’_’);
NetworkPath = @"D:\"+ ServerName.Replace(‘\\’, ‘ ‘).ToString() + @"\Replicaiton\"+FolderDate+"\\";
ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = UserName;
conn.Password = Password;
conn.ServerInstance = ServerName;
Server srv = new Server(conn);
ReplicationServer RS = new ReplicationServer(conn);
foreach (ReplicationDatabase RD in RS.ReplicationDatabases)
foreach (TransPublication TP in RD.TransPublications)
TextWriter tw = new StreamWriter(NetworkPath + "\\" + TP.Name.ToString() + ".sql");
tw.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludeReplicationJobs));
catch (Exception eh)
Dts.TaskResult = (int)ScriptResults.Success;
Once you setup proper path as well as credential of your SQL Server Instance, you are ready to go. Run package manually to check whether it works or not and then schedule it in SQL Server Job or in Windows Task to run weekly or as per your requirement.
Here is the screen capture I have received after I ran it manually.
We have many different server/instance and need script for all email operator in from all server and hence I have had loop for all our database server and script task under the loop so that one package connect to each server, take a script of all operator and save it to two different network location.
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.