How to script Replication objects using RMO

  • I need to script to file all the statements necessary to re-create existing Publications, and to delete existing publications. I had this working in SQL 2000 using DMO, I am struggling a bit with the new RMO. My problem is with the ScriptOptions. I need one file for each publication that has the sp_addpublication, the snapshot, the add article and add subscription. The exact way is it produced with the GUI.

    here is what I have so far. I siumply get the ap_addpublication line with nothing else. I have tried the IncludeAll option and I get nothing.

    ' Create connection to local server

    Dim ServerConnection = New ServerConnection("MyServer")

    ''Dim File As File

    Try

    ' connect to publisher

    ServerConnection.LoginSecure = True

    ServerConnection.Connect()

    Dim ReplicationServer = New ReplicationServer(ServerConnection)

    Dim ReplicationDatabases = ReplicationServer.ReplicationDatabases

    Dim ReplicationDatabase As ReplicationDatabase

    Dim TransactionalPublication As TransPublication

    For Each ReplicationDatabase In ReplicationDatabases

    If ReplicationDatabase.HasPublications Then

    For Each TransactionalPublication In ReplicationDatabase.TransPublications

    FileName = "c:\temp\" & TransactionalPublication.Name & ".sql"

    File.WriteAllText(FileName, TransactionalPublication.Script(ScriptOptions.Creation))

    Next

    End If

    Next

    Catch ex As Exception

    Finally

    ServerConnection.Disconnect()

    End Try

  • I finally found the solution by myself.

    ScriptOptions.Creation Or ScriptOptions.IncludeAll Xor ScriptOptions.IncludeReplicationJobs

    does the trick.

    TransactionalPublication.Script(ScriptOptions.Creation Or ScriptOptions.IncludeAll Xor ScriptOptions.IncludeReplicationJobs)

    generates the script. I was missing bitwise operators (Or /Xor).

    Thanks everyone for your time.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply