Repository navigation
Expand file tree
/
Copy path02_Linked_Servers.ps1
More file actions
127 lines (91 loc) · 2.8 KB
/
Copy path02_Linked_Servers.ps1
File metadata and controls
127 lines (91 loc) · 2.8 KB
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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
<#
.SYNOPSIS
Gets the Linked Servers on the target server
.DESCRIPTION
Writes the Linked Servers out to the "02 - Linked Servers" folder
One file for all servers
Once recreated, you will have to input the server credentials, as passwords are NOT scripted out
.EXAMPLE
02_Linked_Servers.ps1 localhost
.EXAMPLE
02_Linked_Servers.ps1 server01 sa password
.Inputs
ServerName, [SQLUser], [SQLPassword]
.Outputs
.NOTES
.LINK
https://github.com/gwalkey
#>
[CmdletBinding()]
Param(
[string]$SQLInstance='localhost',
[string]$myuser,
[string]$mypass
)
# Load Common Modules and .NET Assemblies
try
{
Import-Module ".\SQLTranscriptase.psm1" -ErrorAction Stop
}
catch
{
Throw('SQLTranscriptase.psm1 not found')
}
LoadSQLSMO
# Init
Set-StrictMode -Version latest;
[string]$BaseFolder = (Get-Item -Path ".\" -Verbose).FullName
Write-Host -f Yellow -b Black "02 - Linked Servers"
Write-Output("Server: [{0}]" -f $SQLInstance)
function CopyObjectsToFiles($objects, $outDir) {
if (-not (Test-Path $outDir)) {
[System.IO.Directory]::CreateDirectory($outDir) | out-null
}
foreach ($o in $objects) {
if ($o -ne $null) {
$schemaPrefix = ""
if ($o.Schema -ne $null -and $o.Schema -ne "") {
$schemaPrefix = $o.Schema + "."
}
$fixedOName = $o.name.replace('\','_')
$scripter.Options.FileName = $outDir + $schemaPrefix + $fixedOName + ".sql"
$scripter.EnumScript($o)
}
}
}
# Create Output Folder
$fullfolderPath = "$BaseFolder\$sqlinstance\02 - Linked Servers"
if(!(test-path -path $fullfolderPath))
{
mkdir $fullfolderPath | Out-Null
}
# Delete pre-existing negative file
if(test-path -path "$BaseFolder\$SQLInstance\02 - No Linked Servers Found.txt")
{
Remove-Item "$BaseFolder\$SQLInstance\02 - No Linked Servers Found.txt"
}
$server = $SQLInstance
$LinkedServers_path = $fullfolderPath+"\Linked_Servers.sql"
# New UP SMO Object
if ($mypass.Length -ge 1 -and $myuser.Length -ge 1)
{
Write-Output "Using Sql Auth"
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$srv.ConnectionContext.LoginSecure=$false
$srv.ConnectionContext.set_Login($myuser)
$srv.ConnectionContext.set_Password($mypass)
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($srv)
# Script out
$LinkedServers = $srv.LinkedServers
$srv.LinkedServers | foreach {$_.Script()+ "GO"} | Out-File $LinkedServers_path
}
else
{
Write-Output "Using Windows Auth"
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)
# Script Out
$LinkedServers = $srv.LinkedServers
$srv.LinkedServers | foreach {$_.Script()+ "GO"} | Out-File $LinkedServers_path
}
set-location $BaseFolder