Post reply

Auto-generate publication/subscription scripts?

  • October 6, 2016 at 4:27 pm

    #318011

    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!

  • October 6, 2016 at 4:29 pm

    #1905288

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

    -- Gianluca Sartori

  • October 6, 2016 at 4:41 pm

    #1905291

    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!

  • October 13, 2016 at 3:42 pm

    #1906474

    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?

  • October 13, 2016 at 4:22 pm

    #1906480

    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