Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Chad Miller

Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows Powershell since 2007. Chad is the Project Coordinator/Developer of the Powershell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa Powershell User Group and is a frequent speaker at users groups, SQL Saturdays and Code Camps.

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.

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:

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
			}
		}
	}
}
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
	}
}

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

.\scriptPublications.ps1 -sqlserver "Z002\sql2k8" -path "C:\Users\u00\repl"
.\scriptPublications.ps1 -sqlserver "Z002\sql2k8" -path "C:\Users\u00\repl" -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

No comments.

Leave a Comment

Please register or log in to leave a comment.