PowerShell SQL Job Step Move-Item not working on 1 server

Question

This identical code has been used in 3 servers, and only one of them does it silently fail to move the items (it still REMOVES them, but they do not appear in the share).

Azure-MapShare.ps1

param (
    [string]$DriveLetter,
    [string]$StorageLocation,
    [string]$StorageKey,
    [string]$StorageUser
)

if (!(Test-Path "${DriveLetter}:"))
{
    cmd.exe /c "net use ${DriveLetter}: ${StorageLocation} /u:${StorageUser} ""${StorageKey}"""
}

Get-Exclusion-Days.ps1

param (
    [datetime]$startDate,
    [int]$daysBack
)

$date = $startDate
$endDate = (Get-Date).AddDays(-$daysBack)

$allDays = 
    do {
        "*"+$date.ToString("yyyyMMdd")+"*"
        $date = $date.AddDays(-1)
    } until ($date -lt $endDate)

return $allDays

Migrate-Files.ps1

param(
    [string]$Source, 
    [string]$Filter, 
    [string]$Destination,
    [switch]$Remove=$False
)

#Test if source path exist
if((Test-Path -Path $Source.trim()) -ne $True) {
    throw 'Source did not exist'
} 

#Test if destination path exist
if ((Test-Path -Path $Destination.trim()) -ne $True) {
    throw 'Destination did not exist'
}

#Test if no files in source
if((Get-ChildItem -Path $Source).Length -eq 0) {
    throw 'No files at source'
}

if($Remove)
{
    #Move-Item removes the source files
    Move-Item -Path $Source -Filter $Filter -Destination $Destination -Force
} else {
    #Copy-Item keeps a local copy
    Copy-Item -Path $Source -Filter $Filter -Destination $Destination -Force
}

return $True

The job step is type "PowerShell" on all 3 servers and contains this identical code:

#Create mapping if missing
D:\Scripts\Azure-MapShare.ps1 -DriveLetter 'M' -StorageKey "[AzureStorageKey]" -StorageLocation "[AzureStorageAccountLocation]\backup" -StorageUser "[AzureStorageUser]"


#Copy files to Archive
D:\Scripts\Migrate-Files.ps1 -Source "D:\Databases\Backup\*.bak" -Destination "D:\Databases\BackupArchive"


#Get date range to exclude
$exclusion = D:\Scripts\Get-Exclusion-Days.ps1 -startDate Get-Date -DaysBack 7

#Remove items that are not included in exclusion range
Remove-Item -Path "D:\Databases\BackupArchive\*.bak" -exclude $exclusion


#Move files to storage account. They will be destroyed
D:\Scripts\Migrate-Files.ps1 -Source "D:\Databases\Backup\*.bak" -Destination "M:\" -Remove


#Remove remote backups that are not from todays backup
Remove-Item -Path "M:\*.bak" -exclude $exclusion

If I run the job step using SQL then the files get removed but do not appear in the storage account. If I run this code block manually, they get moved.

When I start up PowerShell on the server, I get an error message: "Attempting to perform the InitializeDefaultDrives operation on the 'FileSystem' provider failed." However, this does not really impact the rest of the operations (copying the backup files to BackupArchive folder, for instance).

I should mention that copy-item also fails to copy across to the share, but succeeds in copying to the /BackupArchive folder

C Bauer · Accepted Answer · 2016-06-20T02:35:05.787

Apparently I tricked myself on this one. During testing I must have run the net use command in an elevated command prompt. This apparently hid the mapped drive from non-elevated OS features such as the Windows Explorer and attempts to view its existence via non-elevated command prompt sessions. I suppose it also was automatically reconnecting during reboots because that did not fix it.

The solution was as easy as running the net use m: /delete command from an elevated command prompt.

score 0 · Answer 2 · answered Jun 17 '16 at 08:34

Note sure if this will help you but you could try to use the New-PSDrive cmdlet instead of net use to map your shares:

param (
    [string]$DriveLetter,
    [string]$StorageLocation,
    [string]$StorageKey,
    [string]$StorageUser
)

if (!(Test-Path $DriveLetter))
{    
    $securedKey = $StorageKey | ConvertTo-SecureString -AsPlainText -Force
    $credentials = New-Object System.Management.Automation.PSCredential ($StorageUser, $securedKey)

    New-PSDrive -Name $DriveLetter -PSProvider FileSystem -Root $StorageLocation -Credential $credentials -Persist
}

PowerShell SQL Job Step Move-Item not working on 1 server

2 Answers2