Skip to content

Instantly share code, notes, and snippets.

@schwartzmx
Last active August 12, 2016 22:09
Show Gist options
  • Save schwartzmx/48b46d7aa2ca95d206f5ae20817104b4 to your computer and use it in GitHub Desktop.
Save schwartzmx/48b46d7aa2ca95d206f5ae20817104b4 to your computer and use it in GitHub Desktop.
SQL Server jobs, linked servers, etc.. transfer from a source server to targets using SMO
<#
Note: Before running, make sure you have any relevant Credentials copied over and maintenance plans, or referenced objects could cause failures in the script.
Author: Phil
#>
$Source = "" # ex. "Source-SQL"
$SQLUser = "" # user
$SQLPassword = "" # password
$Targets = "" # ex. "Target-SQL"
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$smosource = New-Object Microsoft.SqlServer.Management.Smo.Server $Source
# mixed-mode authentication
$smosource.ConnectionContext.LoginSecure=$false;
$smosource.ConnectionContext.set_Login($SQLUser);
$smosource.ConnectionContext.set_Password($SQLPassword)
$targetList = New-Object System.Collections.ArrayList
$Targets | % {
$targetList.Add($(New-Object Microsoft.SqlServer.Management.Smo.Server $_)) | Out-Null
}
# Linked Servers, Operators, Proxies
$LinkedServerScript = $smosource.LinkedServers | % { $_.Script() + "GO" }
$OperatorsScript = $smosource.JobServer.Operators | % { $_.Script() + "GO" }
$ProxyScript = $smosource.JobServer.ProxyAccounts | % { $_.Script() + "GO" }
ForEach($smotarget in $targetList) {
$smotarget.Databases['msdb'].ExecuteNonQuery($LinkedServerScript)
Write-Host "[$($smotarget.Name)] Added LinkedServers."
$smotarget.Databases['msdb'].ExecuteNonQuery($OperatorsScript)
Write-Host "[$($smotarget.Name)] Added Operators."
$smotarget.Databases['msdb'].ExecuteNonQuery($ProxyScript)
Write-Host "[$($smotarget.Name)] Added Proxies."
$smotarget.JobServer.Refresh()
$smotarget.JobServer.Alter()
Write-Host "WARNING: For security reasons the linked server remote logins password is changed with ########" -ForegroundColor "Yellow"
}
# SQL Agent Properties
$AgentScript = $smotarget.JobServer.Script()
ForEach($smotarget in $targetList) {
$smotarget.Databases['msdb'].ExecuteNonQuery($AgentScript)
Write-Host "[$($smotarget.Name)] Set SQL Agent properties."
$smotarget.JobServer.Refresh()
$smotarget.JobServer.Alter()
}
# SQL Jobs
ForEach ($Job in $smosource.JobServer.Jobs) {
$JobScript = $Job.Script()
ForEach($smotarget in $targetList) {
$JobScript = $JobScript -replace $($smosource.Name), $($smotarget.Name) # Replaces any references to the host instance with the target instance
If($smotarget.JobServer.Jobs.Name -notcontains $JobName) {
$smotarget.Databases['msdb'].ExecuteNonQuery($JobScript)
$smotarget.JobServer.Jobs.Refresh()
$smotarget.JobServer.Jobs[$($Job.Name)].IsEnabled = $false
$smotarget.JobServer.Jobs[$($Job.Name)].Alter()
Write-Host "[$($smotarget.Name)] Added job $($Job.Name)."
}
}
}
# SQL Alerts
ForEach ($a in $smosource.JobServer.Alerts) {
$AlertScript = $a.Script()
ForEach($smotarget in $targetList) {
$smotarget.Databases['msdb'].ExecuteNonQuery($AlertScript)
Write-Host "[$($smotarget.Name)] Added Alert - $($a.Name)."
}
}
# SQL Mail
ForEach ($Pro in $smosource.Mail.Profiles) {
$MailScript = $Pro.Script()
ForEach($smotarget in $targetList) {
If($smotarget.Mail.Profiles.Name -notcontains $Pro) {
$smotarget.Databases['master'].ExecuteNonQuery($MailScript)
Write-Host "[$($smotarget.Name)] Added mail profile $Pro."
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment