How to Script Replication objects using Replication Management Objects (RMO)

Question

How to Script Replication objects using Replication Management Objects (RMO)

me_balaji

SSC Eights!

Points: 923
More actions
August 25, 2008 at 12:53 pm

#192798

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

Viewing 10 posts - 1 through 9 (of 9 total)

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

steve smith-401573 SSChampion Points: 10889 More actions · Answer 1

How many do you have in total? Have you tried to use the GUI for just one, to see what scripts you can extract? It could be that there's a naming convention that's in error, which prevents the other scripts from being extracted? Or you may need to explicitly pull each of the different stored procedures with a separate line of code?

me_balaji SSC Eights! Points: 923 More actions · Answer 2

I have multiple publications some are Transactional and some are Merge replication. I could manually script from SQL Management Studio. But I want to automate the process because there are multiple replication setup around 50+ in production environment. As part of a Disaster plan I want to script it out every day and store the script in a network share to deployed in DR site.

Thanks

noeld SSC Guru Points: 96590 More actions · Answer 3

me_balaji (8/25/2008)
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

Try calling "TransactionalPublication.LoadProperties" BEFORE scripting!

* Noel

me_balaji SSC Eights! Points: 923 More actions · Answer 4

Thanks for a quick response.

I tried both TransactionalPublication.Load() and TransactionalPublication.LoadProperties. No luck.

I still couldn't figure out a easy way to do it.

Thanks

steve smith-401573 SSChampion Points: 10889 More actions · Answer 5

How is scriptoptions defined? That may give you a key, if you can do something 'other' than all - for instance, list each component separately in the central part of your code? Not as 'simple' or as 'elegant' as you might like, but certainly explicit! (I haven't used VB in 5 years, so I'm rusty)

me_balaji SSC Eights! Points: 923 More actions · Answer 6

I treid all the options available in scriptoptions. Only EnableReplicationDB or Creation works. All others just return me a blank space. No where I could find a reference or example. Please help.

me_balaji SSC Eights! Points: 923 More actions · Answer 7

I finally found the solution by myself.

ScriptOptions.Creation Or ScriptOptions.IncludeAll Xor ScriptOptions.IncludeReplicationJobs

do 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.

noeld SSC Guru Points: 96590 More actions · Answer 8

noeld

SSC Guru

Points: 96590

August 28, 2008 at 11:43 am

#864605

Thanks for posting the solution back.

* Noel

gmathur Valued Member Points: 51 More actions · Answer 9

That was for Publications and Subscriptions..do you know the way to do it for Distributor and Publishers