Remotely Copy Files To Multiple Destinations Using PowerShell

Often I need to copy large or sometimes very large files to multiple remote computers, especially if the files I need to copy are also on a remote computer.  

For sake of an example, lets say the files I need to copy are over several TBs in size and are located on a computer A and I need copy those files to computers X, Y, & Z.

If I use the interactive method or even command line tools to do so, it will still take several days.

Basic Copy-Item cmdlet

If you have used PowerShell, you are probably familiar with the Copy-Item cmdlet, which is used to copy files, among other tasks. 

Here is a simple example of how to copy a file on your local machine from one folder to another:

Copy-Item "Downloads\KerberosX64MSI.msi" -Destination "Documents" -Verbose

Besides files and folders, you can also use Copy-Item to copy other types of objects, depending on whether the object provider supports this cmdlet. Here's an example of copying registry keys:

New-Item Registry::HKEY_CURRENT_USER\SOFTWARE\Adobe\Test -ItemType Registry

Copy-Item Registry::HKEY_CURRENT_USER\SOFTWARE\Adobe\Test Registry::HKEY_CURRENT_USER\SOFTWARE\Adobe\Test2

Similarly, you can also use the Copy-Item cmdlet to copy certificates. However, the PowerShell provider for SQL Server (SQLPS) doesn't support Copy-Item, even though it may support other *-Item cmdlets.

You can find out more about the Copy-Item straight from the horse's mouth at Copy-Item.

Copying files between remote computers

As mentioned in the beginning, there are occasions when a DBA needs to copy file/s from their local computer to a SQL Server, or between two or more SQL Servers.  Reasons aside, there are several ways you can copy the files, including PowerShell. For example:

Copy-Item "\\Server01\sqlShare\*" -Destination "\\Server02\sqlShare\" -Recurse
 

That looks simple enough. However, the -Destination parameter only accepts one value, so you cannot use it to copy files to multiple destinations. Plus, you're using your local computer as a conduit, so it's not the most efficient method either.

Meet PowerShell Remoting

Remoting is a feature of PowerShell that allows you to run commands on remote computer/s.  If you are on a Windows 2012 or higher, the PowerShell remoting is enabled by default. Otherwise, you may need to enable it first:

Start the PowerShell session with elevated privileges (Run As Administrator).

Enable-PSRemoting –Verbose

You may be familiar with the Invoke-Command, which maybe the most typical command used to run commands on remote computer/s. You can store results returned by it into a local variable for further processing.

Invoke-Command -ComputerName SQLVM01 -ScriptBlock {"Hello World"}

# Or store the results into a local variable
$local_variable = Invoke-Command -ComputerName SQLVM01 -ScriptBlock {"Hello World"}
Write-Host $local_variable -ForegroundColor White -BackgroundColor Blue

However, Invoke-Command doesn't maintain a persistent connection. To work with remote computers using persistent connections, PowerShell provides cmdlets whose names end with "PSSession":

PS C:\Users\Dummy> (Get-Command -Name *PSSession).Name

Connect-PSSession
Disconnect-PSSession
Enter-PSSession
Exit-PSSession
Export-PSSession
Get-PSSession
Import-PSSession
New-PSSession
Receive-PSSession
Remove-PSSession

However, these are not the only commands that support the remoting feature. There are several other PowerShell commands that have remoting built-in. For example, Get-Process, Get-Service, and others, including Copy-Item, which I consider to be one of them. One way to find out which commands allow running on a remote computer (or a list of computers in one go) is to check if they accept -ComputerName as an input parameter.

Get-Command -ParameterName ComputerName

Alas, you won't find Copy-Item in that list. So what am I talking about? How can you use it to copy files remotely?

Among the parameters for the Copy-Item cmdlet, there are two parameters: -FromSession and -ToSession. These, when combined with the official PowerShell remoting commands ending with "PSSession", can be used to copy files from or to remote computers.

Get-Help -Name Copy-Item -Parameter *Session

But here is the twist, based on parameters name, you would think you can use them together to copy files between two computers in one go like this:

Copy-Item -Path file1.sql  -Destination C:\SQLScripts\ `

          -FromSession $s1  -ToSession $s2 

That is what I thought too at first. But then I tried it and immediate got this error:

Copy-Item : '-FromSession' and '-ToSession' are mutually exclusive and cannot be specified at the same time.

At line:2 char:1 

They are "mutually exclusive".  So how do you then copy file between the two servers? 

Combining Copy-Item and PS Remoting

Let me first show you the usual 3 step procedure.

Step 1:, Create PS sessions to the source and target servers:

$s1 = New-PSSession -ComputerName SQLVM01
$s2 = New-PSSession -ComputerName SQLVM02

Step 2: Copy the file from the source computer to the local machine:

Copy-Item -Path C:\Temp\Test.txt -FromSession $s1 `
    -Destination C:\Users\Dummy\Downloads -Verbose

Step 3: Finally,  copy the file from local computer to the target computer:

Copy-Item -Path C:\Users\Dummy\Downloads\Test.txt `
    -Destination C:\Temp -ToSession $s2 -Verbose

 

You are probably thinking, well this isn't the most efficient method, whichever way you look at it. Plus most laptop or desktop computers don't have kind of disk space to hold large SQL database files so they cannot be used to first copy large files to local computer and then copy it to another computer. Besides, the ToSession parameter only accepts a  single value, so can't be used to copy files to multiple computers in one go.

In this second method. You can send the Copy-Item command, as a script block, to one or more destination computers to copy file/s from the source computer. Advantage of this is that you are not using your local computer as a conduit so it's more efficient and you can copy the file/s to multiple destinations simultaneously. 

Here I am sending the command to the target servers to get the file from the source server:

$script_block =

 {
    $s1 = New-PSSession -ComputerName SQLVM01 ` 

                        -Credential "Contoso\User01"
     
    Copy-Item -Path C:\Temp\Test.txt `
       -Destination C:\Temp -FromSession $s1 -Verbose
 }
 Invoke-Command -ComputerName SQLVM02, SQLVM03 `

                -ScriptBlock $script_block

However, there is still one more tweak needed. Because we are sending the command to another server to run, which then opens a remote session to another computer, that involves a situation called double-hop.  In SQL Server and some other applications like IIS, you can configure it to seamlessly impersonate user through Kerberos and SPNs i.e. without needing to prompt user to re-enter their credential. So far I am not aware PowerShell remoting supports it, yet. 

So, you can either manually enter user credential into the script block, which can get really annoying and inconvenient because it will prompt for password for every destination server. Or, you can store your credentials into an encrypted credential object then pass it to the script block:

$credential = Get-Credential

Updated final script:

<# *** BEWARE: It will overwrite the file at destination if one already exists ***

Purpose: Copies file/s or contents of a folder from one remote computer 
         to one or more other remote computers

It will create the destination folder structure if it does not exist

#>

# Configuration
$config = @{
    SourceComputer = 'SourceVM01'
    SourceFiles = 'D:\Software\Downloads'
    DestinationFiles = 'D:\Software\Downloads'
    DestinationComputers = @('SQLVM02', 'SQLVM03', 'SQLVM03', 'SQLVM04')

}

# Get credentials
$credential = Get-Credential -Message "Enter credentials for remote access"

# Define the script block for remote execution
$scriptBlock = {
    param(
        [PSCredential]$Credential,
        [string]$SourceComputer,
        [string]$SourceFiles,
        [string]$DestinationFiles
    )

        $DestinationFolder = Split-Path -Path $DestinationFiles -Parent
        if (-not (Test-Path -Path $DestinationFolder)) {
            New-Item -Path $DestinationFolder -ItemType Directory -Force | Out-Null
        }
        $session = New-PSSession -ComputerName $SourceComputer -Credential $Credential -ErrorAction Stop
        Copy-Item -Path $SourceFiles -Destination $DestinationFiles -FromSession $session -Recurse -Verbose
}
   
Invoke-Command -ComputerName $config.DestinationComputers `
                   -ScriptBlock $scriptBlock `
                   -ArgumentList $credential, $config.SourceComputer, $config.SourceFiles, $config.DestinationFiles

Here, I am passing the $credential object as a positional parameter to the script block, which gets passed to $my$credential inside the script block.

PS: Here are links to couple articles about how to overcome the challenges of second hop. It's still bit tricky though, for obvious security reasons.  

Making the second hop in PowerShell Remoting

Enable PowerShell "Second-Hop" Functionality with CredSSP