SSIS Package to Backup Replication Script using SMO

SSIS Package to Backup Replication Script.

Hi,

This package willl use some SMO reference and generates SQL Server replication backup script same as you do it manually like, Right click on Publication –> Generate Scripts….

I have created SSIS package that automatically creates backup folder for current date and save your all replication publication script with name “publication name + .sql”.

Step1 : First of all you have to drag one script task and create variables..

–> ServerName – DataType String.

–> UserName – DataType String.

–> pwd- DataType String.

–> DateTime – DataType String.

–> FolderPath – DataType String.

Step2 : Please proper assign ServerName, UserName Pwd and your local machine’s FolderPath.

For DateTime variable click on Expression button and paste below code.

Note : This will create folder with current date letter in script task.

(DT_STR,4,1252)YEAR(GETDATE()) + RIGHT(“0″ + (DT_STR,2,1252)MONTH(GETDATE()),2) + RIGHT(“0″ + (DT_STR,2,1252)DAY(GETDATE()),2)

Replication Script Backup 2

Step3 :  Now Right Click on Script task and select all user variables to ReadOnlyvariables.

Replication Script Backup 1

Step4 : Click on Edit Script.

here you need to add refrences for SMO Objects.

To Add Reference

–> Click on project menu –> Select Add Refrence.

–> On the Add Refrence popup select .Net Tab.

–>Select Microsoft.SQLServer.ConnectionInfo

Microsoft.SQLServer.Management.Utility

Microsoft.SQLServer.SMO.

Paste below code to your script task code.

#region Namespaces
using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using Microsoft.SqlServer.Management.Smo.Agent;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Sdk.Sfc;

using Microsoft.SqlServer.Replication;

using System.IO;

using System.Collections.Specialized;

#endregion</pre>
namespace ST_69d1b336488942988b45f8cdd86a3824
{
 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 {       
  public void Main()
  {
            string ServerName;
            string UserName;
            string Password;
            string FolderDate;
            string FolderPath;

            ServerName = (string)Dts.Variables["ServerName"].Value;
            UserName = (string)Dts.Variables["UserName"].Value;
            Password = (string)Dts.Variables["pwd"].Value;
            FolderDate = (string)Dts.Variables["DateTime"].Value;
            FolderPath = (string)Dts.Variables["FolderPath"].Value;

            ServerConnection conn = new ServerConnection();
            conn.LoginSecure = false;
            conn.Login = UserName;
            conn.Password = Password;
            conn.ServerInstance = ServerName;
            Server srv = new Server(conn);

            System.IO.Directory.CreateDirectory(FolderPath + "\\" +FolderDate.ToString());
            ReplicationServer RS = new ReplicationServer(conn);
            try
            {
                foreach (ReplicationDatabase RD in RS.ReplicationDatabases)
                {

                    if (RD.HasPublications)
                    {
                        foreach (TransPublication TP in RD.TransPublications)
                        {
                            //TextWriter tw = new StreamWriter("D:\\" + TP.Name.ToString() + ".sql");
                            TextWriter tw = new StreamWriter(FolderPath + "\\" + FolderDate.ToString() + "\\" + TP.Name.ToString() + ".sql");
                            tw.Write(TP.Script(ScriptOptions.Creation | ScriptOptions.IncludeAll ^ ScriptOptions.IncludeReplicationJobs));
                            tw.Close();
                        }
                    }
                }
            }
            catch (Exception eh)
            {
                //MessageBox.Show(eh.ToString());
            }
   Dts.TaskResult = (int)ScriptResults.Success;
  }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

 }
}

Note: This script will support only in SQL Server 2012.

Create a job which calls this package and schedule it daily or weekly.

Regards,

Nirav Gajjar