トランザクションレプリケーションのバックアップ

トランザクションレプリケーションをバックアップするPowerShell Scriptを作成します。

Windows Server 2012R2、SQLServer 2012でテストしました。

 

• 1.SQLPSX.msi　のダウンロード

こちらのサイトからSQLPSX.msiをダウンロードします。

http://sqlpsx.codeplex.com/

作業時のバージョンはSQLPSX 2.3.2.1でした。

---

• 2.SQLPSX.msiの展開

ローカルPCに展開します。

今回、必要なのは「Modules\Repl」のみなので、その他は削除、アンインストールしてもかまいません。

---

• 3.Repl.psm1の修正

下の3～6行目の部分を

### </Script>
# ---------------------------------------------------------------------------
try {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"}
try {add-type -AssemblyName "Microsoft.SqlServer.Rmo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" -EA Stop}
catch {add-type -AssemblyName "Microsoft.SqlServer.Rmo"}

$scriptRoot = Split-Path (Resolve-Path $myInvocation.MyCommand.Path)

#######################

3～23行目のように変更します。

### </Script>
# ---------------------------------------------------------------------------
$assemblies = 'Microsoft.SqlServer.ConnectionInfo', 'Microsoft.SqlServer.Rmo'
$sqlversions=12..9
$loadedAssemblies=@{}
foreach($assembly in $assemblies){

    foreach($sqlversion in $sqlversions){
        try {
            $AssemblyName="$assembly, Version=$sqlversion.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
            $loadedassemblies[$assembly]=add-type -AssemblyName $assemblyName -EA Stop -PassThru
            write-verbose "Loaded $assembly version $sqlversion ($assemblyName)"
            break;
        }
        catch{
            write-warning "Unable to Load $assembly version $sqlversion ($assemblyName)"
        }
    }
    if (-not $loadedAssemblies[$assembly]){
        write-warning "$assembly not available"
    }

}

$scriptRoot = Split-Path (Resolve-Path $myInvocation.MyCommand.Path)

#######################

---

• 4.ReplフォルダのDBサーバ配置

修正したファイルの入ったReplフォルダをDBサーバの下記のフォルダに配置します。

C:\Windows\System32\WindowsPowerShell\v1.0\Modules\Repl

---

• 5.Backup_Rep.ps1の作成

下記のPowerShell Scriptを作成し、適当なフォルダへ保存します。

今回は、C:\WORK\Backup_Rep.ps1としました。

#ファイル1個に出力
#./Backup_Rep.ps1 -sqlServer [サーバ名/サーバ名\インスタンス名]  -outputDirectory [出力先フォルダ]

#ファイル分割して出力
#./Backup_Rep.ps1 -sqlServer [サーバ名/サーバ名\インスタンス名]  -outputDirectory [出力先フォルダ] -scriptPerPublication $true

param ([string]$sqlServer, [string]$outputDirectory, [bool]$scriptPerPublication)

Import-Module Repl

if ($sqlServer -eq "")
{
    Write-Output "Please provide a value for -sqlServer"
    exit 1
}

if ($outputDirectory -eq "")
{
    Write-Output "Please provide a value for -outputDirectory"
    exit 1
}

function ScriptPublications
{
    param ([string]$sqlServer, [string] $outputDirectory, [bool] $scriptPerPublication)


    [string] $path =  "$outputDirectory\"
    [string] $ymd  =  $((get-date).toString('yyyy-MMM-dd_HHmmss'))
   
    New-Item $path -ItemType Directory -force | Out-Null


    $a = @(Get-ChildItem "$path\" -include *.sql -recurse)
    foreach($x in $a)  
        {  
            $y = ((Get-Date) - $x.LastWriteTime).Days  

            if ($y -gt 2 -and $x.PsISContainer -ne $True){
                Write-Output $x.Name " " $x.LastWriteTime " " $y
                $x.Delete()
            }  
        } 


    foreach($publication in Get-ReplPublication $sqlServer)
    {
        [string] $fileName = "{0}\{1}_{2}.sql" -f $path, $publication.DatabaseName.Replace(" ", ""), $ymd
        if($scriptPerPublication)
        {
            $fileName = "{0}\{1}_{2}_{3}.sql" -f $path, $publication.DatabaseName.Replace(" ", ""), $publication.Name.Replace(" ", ""), $ymd
        }
        [string] $progressText = "Scripting {0} to {1}" -f $publication.Name.Replace(" ", ""),$fileName
        Write-Output $progressText
        $publication.Script([Microsoft.SqlServer.Replication.scriptoptions]::Creation `
            -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `
            -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions `
            -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateSnapshotAgent `
            -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeGo `
            -bor  [Microsoft.SqlServer.Replication.scriptoptions]::EnableReplicationDB `
            -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublicationAccesses `
            -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateLogreaderAgent `
            -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateQueuereaderAgent `
            -bor  [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions) | Out-File $fileName -Append
    }
}

[Microsoft.SqlServer.Management.Common.ServerConnection] $serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlServer)
[Microsoft.SqlServer.Replication.ReplicationServer] $distributor = new-Object Microsoft.SqlServer.Replication.ReplicationServer($serverConnection);

foreach($distributionPublisher in $distributor.DistributionPublishers)
{
    if($distributionPublisher.PublisherType -eq "MSSQLSERVER")
    {
        [string] $path = $outputDirectory + "\" + $distributionPublisher.Name.Replace("\", "_")

        ScriptPublications -sqlServer $distributionPublisher.Name -outputDirectory $path -scriptPerPublication $scriptPerPublication
    }
}

---

• 6.スクリプトの実行

PowerShellからコマンドを実行します。

 

トランザクションレプリケーション設定を1ファイルに出力する場合

./Backup_Rep.ps1 -sqlServer [サーバ名/サーバ名\インスタンス名]  -outputDirectory [出力先フォルダ]

PS C:\Users> C:\WORK\Backup_Rep.ps1  -sqlServer TestDB1 -outputDirectory C:\WORK

 

C:\WORK配下に「サーバ名」フォルダが作成され、トランザクションレプリケーションのスクリプトが作成されました。

 

パブリケーション毎に分割して出力する場合は、「-scriptPerPublication $true」を追加します。

./Backup_Rep.ps1 -sqlServer [サーバ名/サーバ名\インスタンス名]  -outputDirectory [出力先フォルダ] -scriptPerPublication $true

PS C:\Users> C:\WORK\Backup_Rep.ps1  -sqlServer TestDB1 -outputDirectory C:\WORK -scriptPerPublication $true

 

---

 

SQLジョブに追加し、定期的にバックアップすると便利ではないでしょうか。