SQL Server compatibility error in replication

SQL Server compatibility error in replication

From last replication setup on my sandbox , i found wondering error. Error is like this

Error messages:

Source: Microsoft.SqlServer.Smo
Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[], Boolean)
Message: Script failed for Table ‘Caregiver.SpecialAvailability’.
Stack: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.EnumScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0
Source: Microsoft.SqlServer.Smo
Target Site: Void CheckSupportedType(Microsoft.SqlServer.Management.Smo.ScriptingPreferences)
Message: Column FromDate in object SpecialAvailability contains type Date, which is not supported in the target server version, SQL Server 2005.
Stack: at Microsoft.SqlServer.Management.Smo.Column.CheckSupportedType(ScriptingPreferences options)
at Microsoft.SqlServer.Management.Smo.Column.VersionValidate(ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingPreferences sp, StringBuilder sb, ColumnCollection columns, ICollection indexes)
at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingPreferences sp, StringBuilder sb)
at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(DependencyCollection depList, SqlSmoObject[] objects, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired) (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0

On the first look i am unable to get the meaning of this error, but i read it twice and i get one line which is useful to identify the error.

Line is.

Message: Column FromDate in object SpecialAvailability contains type Date, which is not supported in the target server version, SQL Server 2005.

This message is showing i am configuring replication on incompatible versions of SQL Server.

I have set database compatibility level same as publisher database and i got resolved.

Thanks & Regards,

Nirav Gajjar

Cannot insert explicit value for identity column in table ‘tablename’ when IDENTITY_INSERT is set to OFF

Error messages:
Cannot insert explicit value for identity column in table ‘TableName’ when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)
Get help: http://help/544

Solution : You will get this type of error when you try to insert identity column in replication. You need to update column with not for replication.

This option sets identity off for the records which are coming throw replication.

Use SubscriberDBName
Go
Alter Table TableName
Alter Column ColumnName Not For Replication -- Your Identity column.

Partition Existing Replicated Table

Partition Existing Replicated Table.

To create partition in existing replicated table you must have to remove article from replication. The reason is, to create partiiton in existing table you must have to recreate primary key and you can’t recreate primary key replicated table.

so, remove table from replication is first step.

Note : Before you remove article from replicatin please check file group on both database at publisher and subscrber.

You must have multiple filegroup on replicated database and both database must have same filegroup.

1) You must remove existing table from replication.
http://nirav.extreme-advice.com/2013/07/11/remove-article-from-replication/

2) Create Parition on existing table.
http://nirav.extreme-advice.com/2013/07/09/partition-in-existing-table-in-sql-server/

3) Add partitioned table to replication.

http://nirav.extreme-advice.com/2013/07/10/add-partitioned-table-in-replication/

Remove Article From Replication

Remove Article From Replication

In transnational, replication you have to remove article from Subscription list and drop an Article.

Step1 : Remove article from subscription.

USE [DatabaseName]
EXEC Sp_dropsubscription
  @Publication = N'PublicationName',
  @Subscriber = N'SuscriberServerName',
  @Article = N'ArticleName'
GO

Step2 : Drop article.

EXEC Sp_droparticle
  @Publication = N'PublicationName',
  @Article = N'ArticleName',
  @Force_Invalidate_Snapshot = 1;
GO
--Message "Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot."

Step3 : Refresh Subscription.

Sp_refreshsubscriptions 'MultipleFileGroup_Pub'

Step4 : Start snapshot sgent in replication to apply changes in snapshot.
–> Replication Monitor.
–> Expand Publisher and select Subscription.
–> Goto Agent Tab.
–> Right click on Snaphot and click on Start.

Regards,

Nirav Gajjar

Add Partitioned Table in Replication

Add Partitioned Table in Replication.

Step1 : Create Partition Table.

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION (INT) AS RANGE RIGHT FOR
VALUES(10)

--Create Parition Schema
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION AS PARTITION
FN_PARTITION_TBLPARTITION TO([PRIMARY], [SECONDARY])

CREATE TABLE Tblpartition
  (
     Seq         INT IDENTITY(1, 1) NOT NULL,
     Fisrt_name  VARCHAR(20),
     Last_name   VARCHAR(20),
     Split_value INT NOT NULL
  )
ON SCH_PARTITION_TBLPARTITION(SPLIT_VALUE)

ALTER TABLE Tblpartition
  ADD CONSTRAINT PK_TBLPARTITION PRIMARY KEY (Seq, Split_value)

INSERT INTO Tblpartition
SELECT 'Nirav', 'Gajjar', 1
UNION ALL
SELECT 'Smith', 'Mack', 2
UNION ALL
SELECT 'Jack', 'Samson', 3
UNION ALL
SELECT 'Smith', 'Raka', 10
UNION ALL
SELECT 'Hen', 'Vaith', 11

Step2 : Script to add Partition Table in existing Replication.

--Script to add table in replication.
EXEC Sp_addarticle
  @publication = N'PublicationName',
  @article = N'TBLPartition',
  @source_owner = N'DBO',
  @source_object = N'TBLPartition',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x00000000081B50DB, --Partitioning Options, This will enables Partition Function and Partition Scheme to replicate.
  @identityrangemanagementoption = N'manual',
  @destination_table = N'TBLPartition',
  @destination_owner = N'DBO',
  @status = 24,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_DBOTBLPartition]',
  @del_cmd = N'CALL [sp_MSdel_DBOTBLPartition]',
  @upd_cmd = N'SCALL [sp_MSupd_DBOTBLPartition]',
  @force_invalidate_snapshot = 1
GO

Step3 : Refresh Subscription.

exec sp_refreshsubscriptions @publication = 'PublicationName'

Step4 : Start snapshot sgent in replication to apply changes in snapshot.
–> Replication Monitor.
–> Expand Publisher and select Subscription.
–> Goto Agent Tab.
–> Right click on Snaphot and click on Start.

Regards,

Nirav Gajjar

Replication Issue – Duplication of Primary Key

Replication Issue – Duplication of Primary Key.

Replication synchronizes data and article on subscriber same as it is on publisher.

Changing and inserting data directly at subscriber cause to break replication.

Please look the following error.

Error Message

Command attempted:

 

if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000031000001B1000500000000, Command ID: 1)
Error messages:
Violation of PRIMARY KEY constraint ‘PK__Replicat__3214EC27323EC80A’. Cannot insert duplicate key in object ‘dbo.ReplicationTest’. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627
Violation of PRIMARY KEY constraint ‘PK__Replicat__3214EC27323EC80A’. Cannot insert duplicate key in object ‘dbo.ReplicationTest’. The duplicate key value is (2). (Source: MSSQLServer, Error number: 2627)
Get help: http://help/2627

Replication1

The problem occurs by inserting duplicate key in dbo.ReplicationTest.

Step1 : Track the problem using T-SQL Query.

Copy transaction sequence number and assign to xact_seqno.

The result will show you publisher, publisher db, source article, destination article.

USE [Distribution]
Go

SELECT
  MSA.*
FROM
  dbo.MSarticles AS MSA
JOIN   MSrepl_commands AS MSRC
  ON MSA.Article_id = MSRC.Article_id
WHERE
  MSRC.xact_seqno = 0x00000031000001B1000500000000

Replication2
Step2 : To get the exact row which try to insert data in subscriber.

You have to use SP_BrowseReplCmds procedure of distribution database  and need to pass transaction sequence number to @xact_seqno_start parameter.

EXEC Distribution.DBO.Sp_browsereplcmds
@xact_seqno_start = ‘0x00000031000001B1000500000000’

Replication3

See the column “Command”  it will shows you full query.

Error on procedure SP_MSins_DBOReplicationTest with parameter “2” and “Test” is failing, it means ID “2” is already exist on subscriber table.

You have a two options to solve, you can modify the record at subscriber or delete the record from subscriber table.

Regards,

Nirav Gajjar

Add article in Replication without initializing

Add article in Replication without initializing

By this way you are only creating a snapshot only for newly added article and no need to reinitialize  all subscription.

Step1 : Create New Table on Publisher.

USE Adventureworks2012

CREATE TABLE ReplicationTest
  (
     ID   INT PRIMARY KEY,
     Test VARCHAR(20)
  )

Step2 : Add this table in Article.

First check Article exist or not in a replication.


--Check Exisitng Articles.
SELECT
  sub.srvname    AS SubscriptionServer,
  pub.name       AS Publication,
  art.name       AS ArticleName,
  art.dest_table AS DestinationArticle,
  art.dest_owner AS OwnerName
FROM
  sysextendedarticlesview art
INNER JOIN syspublications pub
        ON ( art.pubid = pub.pubid )
INNER JOIN syssubscriptions sub
        ON ( sub.artid = art.artid )
WHERE
  art.name = 'ReplicationTest'

GO

Run this to add “ReplicationTest” table to your existing article.

--Script to add table in replication.
EXEC Sp_addarticle
  @publication = N'AdventureWorks2012_Pub',
  @article = N'ReplicationTest',
  @source_owner = N'DBO',
  @source_object = N'ReplicationTest',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x00000000080350DF,
  @identityrangemanagementoption = N'manual',
  @destination_table = N'ReplicationTest',
  @destination_owner = N'DBO',
  @status = 24,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_DBOReplicationTest]',
  @del_cmd = N'CALL [sp_MSdel_DBOReplicationTest]',
  @upd_cmd = N'SCALL [sp_MSupd_DBOReplicationTest]',
  @force_invalidate_snapshot = 1

GO

Now check article added successfully.

--Now Check again for newly added article.
SELECT
  sub.srvname    AS SubscriptionServer,
  pub.name       AS Publication,
  art.name       AS ArticleName,
  art.dest_table AS DestinationArticle,
  art.dest_owner AS OwnerName
FROM
  sysextendedarticlesview art
INNER JOIN syspublications pub
        ON ( art.pubid = pub.pubid )
INNER JOIN syssubscriptions sub
        ON ( sub.artid = art.artid )
WHERE
  art.name = 'ReplicationTest'

GO

Step3 : Refresh Subscription.

USE Adventureworks2012
go

Sp_refreshsubscriptions 'Adventureworks2012_pub' -- Supply Publication Name

Step4 : Go to replication monitor screen –> Select Agent Tab
–>Select Snapshot Agent in Agent Type –>Right click on Agent and Start Again.

Replication1

Step5 : Now Go to replication monitor screen –> Select Agent Subscription watch list
–> Double click on your subscription and snapshot applying only for newly added article.

Replication2

Regards,

Nirav Gajjar

Script to get Replication Latency

Script to get Replication Latency.

I have created monitoring script to get latency information for replication and status of replication health.

I have used “SP_REPLMONITORHELPSUBSCRIPTION ” under Distribution database.

It gives you same information which you can get from Replication Monitor –> Subscription Wath List.

In SQL Server 2012 you just need to pass value only for @Publication_type.

If you are using older version of SQL Server then you need to pass @Publication_type and @Publisher parameter, so I have created script for both different versions.

DECLARE @Srvname VARCHAR(100)
DECLARE @Pub_db VARCHAR(100)
DECLARE @Pubname VARCHAR(100)

CREATE TABLE #REPLMONITOR
 (
 Status INT NULL,
 Warning INT NULL,
 Subscriber SYSNAME NULL,
 Subscriber_db SYSNAME NULL,
 Publisher_db SYSNAME NULL,
 Publication SYSNAME NULL,
 Publication_type INT NULL,
 Subtype INT NULL,
 Latency INT NULL,
 Latencythreshold INT NULL,
 Agentnotrunning INT NULL,
 Agentnotrunningthreshold INT NULL,
 Timetoexpiration INT NULL,
 Expirationthreshold INT NULL,
 Last_distsync DATETIME,
 Distribution_agentname SYSNAME NULL,
 Mergeagentname SYSNAME NULL,
 Mergesubscriptionfriendlyname SYSNAME NULL,
 Mergeagentlocation SYSNAME NULL,
 Mergeconnectiontype INT NULL,
 Mergeperformance INT NULL,
 Mergerunspeed FLOAT,
 Mergerunduration INT NULL,
 Monitorranking INT NULL,
 Distributionagentjobid BINARY(16),
 Mergeagentjobid BINARY(16),
 Distributionagentid INT NULL,
 Distributionagentprofileid INT NULL,
 Mergeagentid INT NULL,
 Mergeagentprofileid INT NULL,
 Logreaderagentname VARCHAR(100),
 Publisher VARCHAR(20)
 )

INSERT INTO #REPLMONITOR
SELECT A.*
FROM OPENROWSET('MSDASQL',
 'DRIVER={SQL Server}; SERVER=InstanceName; UID=sa; PWD=Password',
 'SET FMTONLY OFF EXEC DISTRIBUTION.DBO.SP_REPLMONITORHELPSUBSCRIPTION @Publication_type = 0') AS A;

SELECT CASE Status
 WHEN 1 THEN 'Started'
 WHEN 2 THEN 'Succeeded'
 WHEN 3 THEN 'In Profress'
 WHEN 4 THEN 'Idle'
 WHEN 5 THEN 'Retrying'
 WHEN 6 THEN 'Failed'
 END AS Status,
 Publication,
 Publisher_db Subscriber_db,
 Subscriber_db,
 CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, Latency
 ), 6),
 5, 0, ':'), 3, 0,
 ':'), 108) AS Latency,
 CASE Monitorranking
 WHEN 60 THEN 'Error'
 WHEN 56 THEN 'Warning: performance critical'
 WHEN 52 THEN 'Warning: expiring soon or expired'
 WHEN 50 THEN 'Warning: subscription uninitialized'
 WHEN 40 THEN 'Retrying failed command'
 WHEN 30 THEN 'Not running (success)'
 WHEN 20 THEN 'Running (starting, running, or idle)'
 END AS Healthcheck
FROM #REPLMONITOR

DROP TABLE #REPLMONITOR

Query For SQL Server 2005 and 2008.

DECLARE @Srvname VARCHAR(100)
DECLARE @Pub_db VARCHAR(100)
DECLARE @Pubname VARCHAR(100)

CREATE TABLE #REPLMONITOR
  (
     Status                        INT NULL,
     Warning                       INT NULL,
     Subscriber                    SYSNAME NULL,
     Subscriber_db                 SYSNAME NULL,
     Publisher_db                  SYSNAME NULL,
     Publication                   SYSNAME NULL,
     Publication_type              INT NULL,
     Subtype                       INT NULL,
     Latency                       INT NULL,
     Latencythreshold              INT NULL,
     Agentnotrunning               INT NULL,
     Agentnotrunningthreshold      INT NULL,
     Timetoexpiration              INT NULL,
     Expirationthreshold           INT NULL,
     Last_distsync                 DATETIME,
     Distribution_agentname        SYSNAME NULL,
     Mergeagentname                SYSNAME NULL,
     Mergesubscriptionfriendlyname SYSNAME NULL,
     Mergeagentlocation            SYSNAME NULL,
     Mergeconnectiontype           INT NULL,
     Mergeperformance              INT NULL,
     Mergerunspeed                 FLOAT,
     Mergerunduration              INT NULL,
     Monitorranking                INT NULL,
     Distributionagentjobid        BINARY(16),
     Mergeagentjobid               BINARY(16),
     Distributionagentid           INT NULL,
     Distributionagentprofileid    INT NULL,
     Mergeagentid                  INT NULL,
     Mergeagentprofileid           INT NULL,
     Logreaderagentname            VARCHAR(100)
  )

INSERT INTO #REPLMONITOR
SELECT A.*
FROM   OPENROWSET('MSDASQL',
       'DRIVER={SQL Server}; SERVER=InstanceName; UID=sa; PWD=Password',
' set fmtonly off EXEC distribution.dbo.sp_replmonitorhelpsubscription @publication_type = 0, @Publisher = ''ServerName'''
) AS A;

SELECT CASE Status
         WHEN 1 THEN 'Started'
         WHEN 2 THEN 'Succeeded'
         WHEN 3 THEN 'In Profress'
         WHEN 4 THEN 'Idle'
         WHEN 5 THEN 'Retrying'
         WHEN 6 THEN 'Failed'
       END                                                AS Status,
       Publication,
       Publisher_db                                       Subscriber_db,
       Subscriber_db,
       CONVERT(VARCHAR(8), STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR, Latency
                                             ), 6),
                                 5, 0, ':'), 3, 0,
                                               ':'), 108) AS Latency,
       CASE Monitorranking
         WHEN 60 THEN 'Error'
         WHEN 56 THEN 'Warning: performance critical'
         WHEN 52 THEN 'Warning: expiring soon or expired'
         WHEN 50 THEN 'Warning: subscription uninitialized'
         WHEN 40 THEN 'Retrying failed command'
         WHEN 30 THEN 'Not running (success)'
         WHEN 20 THEN 'Running (starting, running, or idle)'
       END                                                AS Healthcheck
FROM   #REPLMONITOR

DROP TABLE #REPLMONITOR

Regards,
Nirav Gajjar

Script to get replication Undistributed Commands

Script to get replication Undistributed Commands

Script to Monitor Replication by checking last refresh time and pending commands

SELECT ( CASE
           WHEN Mdh.Runstatus = '1' THEN 'Start'
           WHEN Mdh.Runstatus = '2' THEN 'Succeed'
           WHEN Mdh.Runstatus = '3' THEN 'InProgress'
           WHEN Mdh.Runstatus = '4' THEN 'Idle'
           WHEN Mdh.Runstatus = '5' THEN 'Retry'
           WHEN Mdh.Runstatus = '6' THEN 'Fail'
         END )                                 [Run status],
       Mda.Subscriber_db                       [Subscriber db],
       Mda.Publication                         [Pub name],
       Mdh.[Time]                              [Lastsynchronized],
       Und.Undelivcmdsindistdb                 [Undistcom],
       Mdh.Comments                            [Comments],
       DATEDIFF(SECOND, Mdh.[Time], GETDATE()) Lastrefresh
FROM   DISTRIBUTION.DBO.Msdistribution_agents Mda
       LEFT JOIN DISTRIBUTION.DBO.Msdistribution_history Mdh
              ON Mdh.Agent_id = Mda.Id
       JOIN (SELECT S.AGENT_ID,
                    Maxagentvalue.[Time],
                    SUM(CASE
                          WHEN Xact_seqno > Maxagentvalue.Maxseq THEN 1
                          ELSE 0
                        END) AS Undelivcmdsindistdb
             FROM   DISTRIBUTION.DBO.Msrepl_commands T (NOLOCK)
                    JOIN DISTRIBUTION.DBO.Mssubscriptions AS S (NOLOCK)
                      ON ( T.ARTICLE_ID = S.ARTICLE_ID
                           AND T.PUBLISHER_DATABASE_ID = S.PUBLISHER_DATABASE_ID
                         )
                    JOIN (SELECT Hist.AGENT_ID,
                                 MAX(Hist.[TIME]) AS [Time],
                                 H.Maxseq
                          FROM   DISTRIBUTION.DBO.Msdistribution_history Hist (
                                 NOLOCK)
                                 JOIN (SELECT Agent_id,
                                              ISNULL(MAX(Xact_seqno), 0x0) AS
                                              Maxseq
                                       FROM
                                 DISTRIBUTION.DBO.Msdistribution_history (
                                 NOLOCK)
                                       GROUP  BY Agent_id) AS H
                                   ON ( Hist.AGENT_ID = H.Agent_id
                                        AND H.Maxseq = Hist.XACT_SEQNO )
                          GROUP  BY Hist.AGENT_ID,
                                    H.Maxseq) AS Maxagentvalue
                      ON Maxagentvalue.Agent_id = S.AGENT_ID
             GROUP  BY S.AGENT_ID,
                       Maxagentvalue.[Time]) Und
         ON Mda.Id = Und.Agent_id
            AND Und.[Time] = Mdh.[Time]
ORDER  BY Mdh.[Time] DESC


Regards,

Nirav Gajjar

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