PowerShell- copy item cannot bind argument to parameter 'path' because it is null

Question

I need to create a PowerShell script that will copy my backup files from PROD server to restore it on DEV server on a monthly basis. Then I need to create a SQL Agent job with two steps. The first step will be to restore those copied files onto DEV Server. The second step will be to execute a SP that cleans up the redundant/inconsistent data.

So far this is the script I created

begin set vars
$BackupDir = "C:\SQL Installs\Backups\Full Backups\Sample_full.bak" # where backups are stored
$WorkDir = "C:\SQL Installs\Backups\Test Backups" # where you are copying backup to
end set vars

Set-Location $WorkDir
$LatestBackupFileName = (Get-ChildItem $BackupDir*.bak | sort LastWriteTime | select -last 1)
Copy-Item $LatestBackupFileName -Destination $WorkDir\AutomatedDbRefresh.bak -Force

Here is the result output- showing that the path is null.

Set-Location $WorkDir
$LatestBackupFileName = (Get-ChildItem $BackupDir*.bak | sort LastWriteTime | select -last 1)
Copy-Item $LatestBackupFileName -Destination $WorkDir\AutomatedDbRefresh.bak -Force
Copy-Item : Cannot bind argument to parameter 'Path' because it is null.
At line:8 char:11

• Copy-Item $LatestBackupFileName -Destination $WorkDir\AutomatedDbRefr ...
• ~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidData: (:) [Copy-Item], ParameterBindingValidationException
• FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.CopyItemCommand

Can someone please advise on how to avoid this error and what I may need to fix in my script?

Answer 1

Hi,

You see the error message because $LatestBackupFileName is null. If Sample_full.bak is a file, the first line should be

$BackupDir = "C:\SQL Installs\Backups\Full Backups"  

Best Regards，
Ian

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

This looks suspicious:

$BackupDir = "C:\SQL Installs\Backups\Full Backups\Sample_full.bak

Is Sample_full.bak a file? Or it's a subfolder?

What is the content of the variable $LatestBackupFileName if you run the script?

---

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 3

I'm pretty sure the Get-ChildItem is incorrect. I think you're looking for all the .bak files in $BackupDir? If so, try this:

$LatestBackupFileName = (Get-ChildItem $BackupDir -Include *.bak | sort LastWriteTime | select -last 1)