ScriptPublications

ScriptPublications.psq

param ($sqlServer,$path,[switch]$scriptPerPublication)
Import-Module Repl
 
if ($sqlServer -eq "")
{
    $sqlserver = Read-Host -Prompt "Please provide a value for -sqlServer"
}
 
if ($path -eq "")
{
    $path = Read-Host -Prompt "Please provide a value for output directory path"
}
 
    $scriptOptions = New-ReplScriptOptions
    $scriptOptions.IncludeArticles = $true
    $scriptOptions.IncludePublisherSideSubscriptions = $false
    $scriptOptions.IncludeCreateSnapshotAgent = $false
    $scriptOptions.IncludeGo = $true
    $scriptOptions.EnableReplicationDB = $true
    $scriptOptions.IncludePublicationAccesses = $false
    $scriptOptions.IncludeCreateLogreaderAgent = $true
    $scriptOptions.IncludeCreateQueuereaderAgent = $false
    $scriptOptions.IncludeSubscriberSideSubscriptions = $false
 
    $distributor = Get-ReplServer $sqlserver
 
if($distributor.DistributionServer -eq $distributor.SqlServerName)
{
	$distributor.DistributionPublishers | ForEach-Object {
		$distributionPublisher = $_
		if($distributionPublisher.PublisherType -eq "MSSQLSERVER")
		{
			$outPath =  "{0}\{1}\"  -f $path,$distributionPublisher.Name.Replace("\","_")
			if (Test-Path $outPath) {Remove-Item $outPath -Recurse -Force}
            New-Item $outPath -ItemType Directory -force | Out-Null
			Get-ReplPublication $distributionPublisher.Name | ForEach-Object {
				$publication = $_
				$fileName = "{0}{1}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", "")
				if($scriptPerPublication)
				{
					$fileName = "{0}{1}_{2}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", ""),$publication.Name.Replace(" ", "")
				}
				Write-Debug $("Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName)
				Get-ReplScript -rmo $publication -scriptOpts $($scriptOptions.ScriptOptions) | Out-File $fileName
			}
		}
	}
}
else
{
    ##$replaceInput = '@job_login = N''PRD\s_prd_ReplLog'', @job_password = null,'
    ##$replaceOutput = '@job_login = N''PRD\s_stg_repllog'', @job_password = ''password1'''
    $replaceInput = "@job_login = N'PRD\s_prd_ReplLog'"
    $replaceOutput = "@job_login = N'PRD\s_stg_repllog'"
    
    $outPath =  "{0}\{1}\"   -f $path,$distributor.SqlServerName.Replace("\","_")
    if (Test-Path $outPath) {Remove-Item $outPath -Recurse -Force}
    New-Item $outpath -ItemType Directory -force | Out-Null
    Get-ReplPublication $distributor.SqlServerName | ForEach-Object {
		$publication = $_
		$fileName = "{0}{1}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", "")
		if($scriptPerPublication)
		{
			$fileName = "{0}{1}_{2}.sql" -f $outPath,$publication.DatabaseName.Replace(" ", ""),$publication.Name.Replace(" ", "")
		}
		Write-Debug $("Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName)
		Get-ReplScript -rmo $publication -scriptOpts $($scriptOptions.ScriptOptions) | Out-File $fileName
        $replaceInput
        $replaceOutput
        $fileName2 = $fileName + '.txt'
        ##(Get-Content $fileName) -replace $replaceInput, $replaceOutput | Set-Content $fileName2 
        ##(Get-Content $fileName) | ForEach-Object { $_ -replace @"@job_login = N'PRD\s_prd_ReplLog', @job_password = null,"@, @"@job_login = N''PRD\s_stg_repllog'', @job_password = ''2QJmrtXaUNvPECiqbq3g''"@ } | Set-Content $fileName
        ##(Get-Content $fileName) | ForEach-Object { $_ -replace "@job_login", "@job_login333" } | Set-Content $fileName
        (Get-Content $fileName) | ForEach-Object {$_ -replace $replaceInput, $replaceOutput } | Set-Content $fileName2
	}
}