Basic Copy-Item cmdlet
If you have used some PowerShell, you are probably familiar with Copy-Item command (cmdlet) to copy files, among other things. Here is a simple example to copy a file on local machine from one folder to another:
Copy-Item "DownloadsKerberosX64MSI.msi" -Destination "Documents" -Verbose
Besides the files and folders, you can also use to copy other types of objects, depending on whether the object provider supports the Copy-Item. Here is an example of copying registry keys.
New-Item Registry::HKEY_CURRENT_USERSOFTWAREAdobeTest -ItemType Registry Copy-Item Registry::HKEY_CURRENT_USERSOFTWAREAdobeTest Registry::HKEY_CURRENT_USERSOFTWAREAdobeTest2
Similarly, you can also use the cmdlet to copy certificates. However, PowerShell provider for SQL Server or SQLPS don'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
There are occasions when a DBA needs to copy file/s from their local computer to a SQL Server, or between two SQL Servers. Reasons aside, there are few ways you can copy the files, including PowerShell. For example:
Copy-Item "\Server01sqlShare*" -Destination "\Server02sqlShare" -Recurse
That's looks simple enough. However, the destination parameter only accepts one value so you cannot use it to copy files to multiple destinations. Plus you are using your local computer as a conduit so its not the most efficient either.
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
But, Invoke-Command doesn't persist the connection. In order to work with remote computers using persistent connections, PowerShell provides cmdlets whose names end with PSSession:
PS C:UsersDummy> (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
But those are the not only commands that supports the remoting feature. But there are several other PowerShell commands that have remoting built-in. For example Get-Process, Get-Service etc. and I consider Copy-Item to be one of them. One way you can find out what commands allows running it 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 the Copy-Item in that list. So what the hell I am talking about? How can I use it to copy files remotely?
Among the parameters for Copy-Item command, there are two parameters FromSession and ToSession, which combined with the official PowerShell remoting commands *PSSession, can be used to copy files from/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:TempTest.txt -FromSession $s1 ` -Destination C:UsersDummyDownloads -Verbose
Step 3: Finally, copy the file from local computer to the target computer:
Copy-Item -Path C:UsersDummyDownloadsTest.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 "ContosoUser01" Copy-Item -Path C:TempTest.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:
$script_block = { param($mycredential) $s1 = New-PSSession -ComputerName "SQLVM01 `
-Credential $mycredential Copy-Item -Path C:TempTest.txt ` -Destination C:Temp -FromSession $s1 -Verbose } Invoke-Command -ComputerName SQLVM02, SQLVM03 `
-ScriptBlock $script_block `
-ArgumentList $credential
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: As soon as published this blog, I found the following articles on second hop issue.