≡ Menu

Quickly Script Out Replication Redux

Dave Levy (Blog|Twitter) posted a script in a blog post in which he uses a bit of SQL PowerShell Extensions (SQLPSX) and some Replication Management Objects (RMO) to script out SQL Server replication.  Overall Dave’s script is a good use of PowerShell and RMO. Scripting out objects is much easier to handle in PowerShell than a T-SQL solution, however some improvements to the script can be made.

EDIT: Dave pointed out I was missing the Append parameter for Out-File. The script below has been corrected.

One of the goals of SQLPSX is simplify SQL Server PowerShell scripting by providing functions over common tasks. I think this important as the PowerShell host which ships with SQL Server, sqlps, does not cover replication. The original script can be refactored to use SQLPSX instead of working with the RMO classes directly as well reduce some of the code as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
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 = $true
    $scriptOptions.IncludeCreateSnapshotAgent = $true
    $scriptOptions.IncludeGo = $true
    $scriptOptions.EnableReplicationDB = $true
    $scriptOptions.IncludePublicationAccesses = $true
    $scriptOptions.IncludeCreateLogreaderAgent = $true
    $scriptOptions.IncludeCreateQueuereaderAgent = $true
    $scriptOptions.IncludeSubscriberSideSubscriptions = $true
 
    $distributor = Get-ReplServer $sqlserver
 
if($distributor.DistributionServer -eq $distributor.SqlServerName)
{
	$distributor.DistributionPublishers | ForEach-Object {
		$distributionPublisher = $_
		if($distributionPublisher.PublisherType -eq "MSSQLSERVER")
		{
			$outPath =  "{0}from_{1}{2}"  -f $path,$distributionPublisher.Name.Replace("","_"),$((Get-Date).toString('yyyy-MMM-dd_HHmmss'))
			New-Item $outPath -ItemType Directory | 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 -Append
			}
		}
	}
}
else
{
    $outPath =  "{0}from_{1}{2}"  -f $path,$distributor.SqlServerName.Replace("","_"),$((Get-Date).toString('yyyy-MMM-dd_HHmmss'))
    New-Item $outpath -ItemType Directory | 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 -Append
	}
}

Save the script as a .ps1 file, scriptPublications.ps1 and invoke with:

1
2
.scriptPublications.ps1 -sqlserver "Z002sql2k8" -path "C:Usersu00repl"
.scriptPublications.ps1 -sqlserver "Z002sql2k8" -path "C:Usersu00repl" -scriptPerPublication

Comments:

  • Notice the use of New-ReplScriptOptions. This is a helper function to handle the replication scripting options.
  • Replace a bool with a switch param. A switch is kind of like a bool, but you don’t specify $true or $false. In PowerShell scripting switch should be used over bool.
  • Removed unneeded code

Comments on this entry are closed.

%d bloggers like this: