Auto-generate publication/subscription scripts?

  • Y'know how you can right-click on a publication or subscription and choose "generate scripts" to either "create" or "delete"? Does anyone know if this can be automated so I can export these scripts daily?

    Thanks!

  • Here is a script that does exactly that: http://www.replicationanswers.com/powershell.asp

    -- Gianluca Sartori

  • Wow...super fast response!. I learned a ton about replication from this website a few years ago. I will give this a whirl. If it works out, I'll make sure to mark as the answer.

    Thanks!

  • Alright. So this works great from the command line, but I can't get it to run from SQL Server Agent. My syntax is this:

    cd "C:\Windows\System32\WindowsPowerShell\v1.0"

    powershell.exe "cd 'Z:\MaintenanceScripts\Powershell'; .\ReplicationTopologyBackup.ps1 -sqlserver 'DBPxxx' -filename 'C:\ReplTopologyDBPxxx.txt'"

    The .ps1 file is (obviously) the script from the link you posted, and the .txt file is the output.

    I used this as a reference (step #2): http://www.sqlhammer.com/running-powershell-in-a-sql-agent-job/

    Can anyone tell me what I'm doing wrong? Should I start a new thread?

  • Clint-525719 (10/13/2016)


    Alright. So this works great from the command line, but I can't get it to run from SQL Server Agent. My syntax is this:

    cd "C:\Windows\System32\WindowsPowerShell\v1.0"

    powershell.exe "cd 'Z:\MaintenanceScripts\Powershell'; .\ReplicationTopologyBackup.ps1 -sqlserver 'DBPxxx' -filename 'C:\ReplTopologyDBPxxx.txt'"

    The .ps1 file is (obviously) the script from the link you posted, and the .txt file is the output.

    I used this as a reference (step #2): http://www.sqlhammer.com/running-powershell-in-a-sql-agent-job/

    Can anyone tell me what I'm doing wrong? Should I start a new thread?

    The article you referenced is messing around with Powershell versions. SQL Powershell is like a mini-shell, a scaled down version of Powershell and different versions of SQL use different versions of Powershell. If you don't need to force different versions of powershell and that isn't an issue, just ignore some of that post for now.

    Just try to run your script from a Job step that is a Powershell type of step (the selection under step name).

    Sue

Viewing 5 posts - 1 through 4 (of 4 total)

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