Replication scripting

• SQLQuest29

  SSCrazy Eights

  Points: 8264

  More actions

  November 26, 2010 at 1:34 pm

  #149417

  Hi Guys,

  How can I script out replication using TSQL. I dont want to use powershell ?

  Is there any way this can be done using TSQL.

  I know that this can be done using GUI, but I am planning to automate this.

  Cheers !

  ______________________________________________________________________________________________________________________________________________________________________________________
  HTH !
  Kin
  MCTS : 2005, 2008
  Active SQL Server Community Contributor 🙂

• MysteryJimbo

  SSC-Insane

  Points: 24203

  More actions

  November 27, 2010 at 7:10 am

  #1255972

  As far as I know, you can only script out publications using the .Net replication management objects.

  The easiest method is powershell, however you've stated you dont want to use this.

  Dead easy using the SQL Powershell extensions (SQLPSX) and you could do it something like this and schedule it in a SQL Agent job.

  $Path = "C:\output\"

  Get-ReplPublication 'servername'| ForEach-Object { $FilePath = $Path + $_.Name + ".sql"

  Get-ReplScript $_ | out-file -filepath $FilePath -Force; #Scripts Publication

  Get-ReplArticle $_ | Get-ReplScript | out-file -filepath $FilePath -append; #Scripts Articles

  Get-ReplSubscription $_ | Get-ReplScript| out-file -filepath $FilePath -append; #Scripts Subscribers

  }