functions/Copy-SqlReplication.ps1

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
<#
#$transfer.Options.ContinueScriptingOnError
# replace names
# Load SMO, create server object, test connection, disconnect
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO")
Microsoft.SqlServer.Rmo
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "sqlserver"
try { $server.ConnectionContext.Connect() } catch { throw "Can't connect to SQL Server." }
Write-Host "Connection succeeded." -ForegroundColor Green
 
$sourceSqlConn = $server.ConnectionContext.SqlConnectionObject
$distributor = New-Object Microsoft.SqlServer.Replication.ReplicationServer $sourceSqlConn
 
 
# Trans - creates pub, add articles, starts snapshot
 
  
$scriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation -bor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeAll -bxor [Microsoft.SqlServer.Replication.ScriptOptions]::IncludeReplicationJobs
 
 
$scriptargs = [Microsoft.SqlServer.Replication.scriptoptions]::Creation -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeAgentProfiles `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeChangeDestinationDataTypes -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateDistributionAgent `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateLogreaderAgent -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateMergeAgent `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateQueuereaderAgent -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeInstallDistributor `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeMergeDynamicSnapshotJobs -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeMergeJoinFilters `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeMergePartitions -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeMergePublicationActivation `
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublicationAccesses -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublications`
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePullSubscriptions -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeRegisteredSubscribers`
-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeReplicationJobs -bor [Microsoft.SqlServer.Replication.scriptoptions]::InstallDistributor
 
#-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePartialSubscriptions
#-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateSnapshotAgent
#-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeEnableReplicationDB `
#-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeDistributionPublishers `
#-bor [Microsoft.SqlServer.Replication.scriptoptions]::EnableReplicationDB `
# explore -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateSnapshotAgent `
#-bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions `
 
 $ScriptOptions = [Microsoft.SqlServer.Replication.ScriptOptions]::Creation `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeInstallDistributor `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::EnableReplicationDB `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublisherSideSubscriptions `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateLogreaderAgent `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateDistributionAgent `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateMergeAgent `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateSnapshotAgent `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublicationAccesses `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions `
                   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeGo `
         
#InstallDistributor
$repdbs = $distributor.ReplicationDatabases
foreach ($repdb in $repdbs) {
 if ($repdb.HasPublications) {
  
  foreach ($transpub in $repdb.TransPublications) {
  # TransArticles
  # TransSubscriptions
  $transpub.Script([Microsoft.SqlServer.Replication.scriptoptions]::Creation `
   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateLogreaderAgent `
   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeCreateQueuereaderAgent `
   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludePublicationAccesses `
   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeArticles `
   -bor [Microsoft.SqlServer.Replication.scriptoptions]::IncludeSubscriberSideSubscriptions
   )
   break
  }
   
  foreach ($mergepub in $repdb.MergePublications) {
   $mergepub.Script($scriptargs)
  }
   
 }
  
 if ($repdb.HasPullSubscriptions) {
  foreach ($transsub in $repdb.TransPullSubscriptions) {
   $transsub.Script($scriptargs)
  }
   
  foreach ($mergesub in $repdb.MergePullSubscriptions) {
   $mergesub.Script($scriptargs)
  }
 }
}
#>