Often, I need to copy large, or sometimes very large, files to multiple remote computers, especially when the files I need to copy are already located on another remote computer.
For example, let us say the files I need to copy are several TB in size and are located on Computer A, and I need to copy them to Computers X, Y, and Z.
If I use an interactive method, or even command-line tools, to do that, it could still take several days.
About basic Copy-Item cmdlet
If you have used PowerShell before, you are probably already familiar with the Copy-Item cmdlet. It is commonly used to copy files, along with a few other related 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 the cmdlet. Here is 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.
Copying files between remote computers
As mentioned at the beginning, there are occasions when a DBA needs to copy one or more files from a local computer to a SQL Server, or between two or more SQL Servers. Regardless of the reason, there are several ways to copy the files, including PowerShell. For example:
Copy-Item "\\Server01\sqlShare\*" -Destination "\\Server02\sqlShare\" -Recurse
That looks simple enough. However, the -Destination parameter accepts only one value, so you cannot use it to copy files to multiple destinations. In addition, your local computer becomes the transfer path, so this is not the most efficient method either.
About PowerShell Remoting
Remoting is a feature of PowerShell that allows you to run commands on one or more remote computers. If you are using Windows Server 2012 or later, PowerShell remoting is enabled by default in many environments. Otherwise, you may need to enable it first:
Start the PowerShell session with elevated privileges, or run it as Administrator. Run command:
Enable-PSRemoting -Verbose
You may be familiar with Invoke-Command, which is perhaps the most commonly used cmdlet for running commands on one or more remote computers. You can store the results it returns in 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 does not 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 remoting. Several other PowerShell cmdlets have remoting built in as well. For example, Get-Process, Get-Service, and others can run on remote computers, and I would also include Copy-Item in that group. One simple way to tell whether a cmdlet supports running against a remote computer, or even a list of remote computers, is to check whether it accepts the -ComputerName 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 = { $sourceSession = New-PSSession -ComputerName SQLVM01 -Credential (Get-Credential)Copy-Item-Path 'C:\Temp\Test.txt' ` -Destination 'C:\Temp' ` -FromSession $sourceSession ` -Verbose Remove-PSSession -Session $sourceSession }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:
<# Purpose: Copies files or folder contents from one remote computer to one or more remote computers. Important: This script overwrites files at the destination if they already exist.
Important: This is not a true direct remote-to-remote copy. The script uses a local staging location to avoid the
double-hop authentication problem.
Behavior: - Creates the destination folder structure if it does not already exist. - Uses a local staging folder to avoid double-hop authentication issues. - Copies from the source computer to the staging path first, then to each destination computer. Requirements: - PowerShell remoting must be enabled on the source and destination computers. - Valid credentials are required for remote access. #> # Configuration $config = @{ SourceComputer = 'SourceVM01' SourcePath = 'D:\Software\Downloads' StagingPath = 'C:\Temp\PSCopyStaging\Downloads' DestinationPath = 'D:\Software\Downloads' DestinationComputers = @('SQLVM02', 'SQLVM03', 'SQLVM03', 'SQLVM04') } # Get credentials for remote access $credential = Get-Credential -Message 'Enter credentials for remote access' # Ensure the local staging folder exists $stagingParent = Split-Path -Path $config.StagingPath -Parent if (-not (Test-Path -Path $stagingParent)) { New-Item -Path $stagingParent -ItemType Directory -Force | Out-Null } # Create a session to the source computer $sourceSession = New-PSSession -ComputerName $config.SourceComputer -Credential $credential -ErrorAction Stop try { # Copy the source folder or files from the source server to the local staging pathCopy-Item-Path $config.SourcePath ` -Destination $config.StagingPath ` -FromSession $sourceSession ` -Recurse ` -Force ` -Verbose # Copy the staged content to each destination computer foreach ($destinationComputer in $config.DestinationComputers) { $destinationSession = New-PSSession -ComputerName $destinationComputer -Credential $credential -ErrorAction Stop try { # Ensure destination folder existsInvoke-Command-Session $destinationSession -ScriptBlock { param($DestinationPath) if (-not (Test-Path -Path $DestinationPath)) { New-Item -Path $DestinationPath -ItemType Directory -Force | Out-Null } } -ArgumentList $config.DestinationPath # Copy staged content to destinationCopy-Item-Path (Join-Path $config.StagingPath '*') ` -Destination $config.DestinationPath ` -ToSession $destinationSession ` -Recurse ` -Force ` -Verbose } finally { Remove-PSSession -Session $destinationSession } } } finally { Remove-PSSession -Session $sourceSession }
See also
- Making the second hop in PowerShell Remoting
- Enable PowerShell "Second-Hop" Functionality with CredSSP
- Powershell one liner to export data directly from SQL Server to Excel
- PowerShell Remoting for SQL DBAs: WinRM + SSH Guide