As a DBA we’re always considering disaster recovery, business continuity, backups etc. Replication is no different – we need to get a copy of the whole setup. Manually this can be done from SSMS:
This is OK if you remember to always do it when making changes.
In practice people forget or haven’t got the time.
So, the Powershell script below will create a text file and you can run this script as a job – easy!
########################################################################################################################
# Script created by:
# Date created:
# Changes:
# Purpose: Script creates the replication file for DR purposes
# Then it copies the file to the DR destination
# Example Usage: powershell -Command “C:\DBA\scripts\BackupReplicationScripts.ps1” -SourceReplicationFilename “\\servername\replication\Publications.sql” -DestinationReplicationFilename “\\servername2\replication\Publications.sql” -SQLServerWithPublications “mainserver,port” -$ComputerWithPublications “mainserver”
########################################################################################################################
param(
[Parameter(Position=0,mandatory=$true)]
[string]$SourceReplicationFilename,
[Parameter(Position=1,mandatory=$true)]
[string]$DestinationReplicationFilename,
[Parameter(Position=2,mandatory=$true)]
[string]$SQLServerWithPublications,
[Parameter(Position=3,mandatory=$true)]
[string]$ComputerWithPublications
)
Try
{
write-host “$SourceReplicationFilename”
write-host “$DestinationReplicationFilename”
write-host “$SQLServerWithPublications”
write-host “$ComputerWithPublications”
$ScriptBlockContent = {
param ( $SourceReplicationFilename, $SQLServerWithPublications, $ComputerWithPublications)
#Reference RMO Assembly
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Rmo”)
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Replication”)
function errorhandler([string]$errormsg)
{
writetofile (“Replication Script Generator run at: ” + (date)) $SourceReplicationFilename 1
writetofile (“[Replication Script ERROR] ” + $errormsg) $SourceReplicationFilename 0
write-host(“[Replication Script ERROR] ” + $errormsg) -Foregroundcolor Red
}
function writetofile([string]$text, [string]$myfilename, [int]$cr_prefix)
{
if ($cr_prefix -eq 1) { “” >> $myfilename }
$text >> $myfilename
}
function initializefile([string]$myfilename)
{
“” > $myfilename
}
$repsvr=New-Object “Microsoft.SqlServer.Replication.ReplicationServer” $SQLServerWithPublications
Clear-host
initializefile $SourceReplicationFilename
# if we don’t have any replicated databases then there’s no point in carrying on
if ($repsvr.ReplicationDatabases.Count -eq 0)
{
writetofile (“Replication Script Generator run at: ” + (date)) $SourceReplicationFilename 0
writetofile “ZERO replicated databases on $SQLServerWithPublications!!!” $SourceReplicationFilename 1
EXIT
}
# similarly, if we don’t have any publications then there’s no point in carrying on
[int] $Count_Tran_Pub = 0
foreach($replicateddatabase in $repsvr.ReplicationDatabases)
{
$Count_Tran_Pub = $Count_Tran_Pub + $replicateddatabase.TransPublications.Count
}
if (($Count_Tran_Pubb) -eq 0)
{
writetofile (“Replication Script Generator run at: ” + (date)) $SourceReplicationFilename 0
writetofile “ZERO Publications on $SQLServerWithPublications!!!” $SourceReplicationFilename 1
EXIT
}
# if we got this far we know that there are some publications so we’ll script them out
# the $scriptargs controls exactly what the script contains
# for a full list of the $scriptargs see the end of this script
$scriptargs =
[Microsoft.SqlServer.Replication.scriptoptions]::Creation `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IIncludePublications `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublicationAccesses `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeGo `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateSnapshotAgent `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::EnableReplicationDB `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateLogreaderAgent
writetofile (“– Replication Script Generator run at: ” + (date)) $SourceReplicationFilename 0
writetofile “– PUBLICATIONS ON $SQLServerWithPublications” $SourceReplicationFilename 1
writetofile “– TRANSACTIONAL PUBLICATIONS ($Count_Tran_Pub)” $SourceReplicationFilename 1
foreach($replicateddatabase in $repsvr.ReplicationDatabases)
{
if ($replicateddatabase.TransPublications.Count -gt 0)
{
foreach($tranpub in $replicateddatabase.TransPublications)
{
write-host “–********************************************************************************” -Foregroundcolor Blue
“–***** Writing to file script for publication: ” + $tranpub.Name
write-host “–********************************************************************************” -Foregroundcolor Blue
writetofile “–********************************************************************************” $SourceReplicationFilename 0
writetofile (“–***** Writing to file script for publication: ” + $tranpub.Name) $SourceReplicationFilename 0
writetofile “–********************************************************************************” $SourceReplicationFilename 0
[string] $myscript=$tranpub.script($scriptargs)
writetofile $myscript $SourceReplicationFilename 0
}
}
}
}
Invoke-Command -ComputerName “$ComputerWithPublications” -ScriptBlock $ScriptBlockContent -ArgumentList $SourceReplicationFilename, $SQLServerWithPublications, $ComputerWithPublications
Copy-Item $SourceReplicationFilename $DestinationReplicationFilename
}
Catch [Exception]
{
write-host $_.Exception.Message $true
}
Finally
{
}
