PowerShell SQL 作业步骤移动项目在 1 台服务器上不起作用

Question

这个相同的代码已在 3 个服务器中使用，只有其中一个服务器无法静默地移动项目(它仍然删除它们，但它们不会出现在共享中)。

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

作业步骤是在所有 3 台服务器上键入“PowerShell”并包含以下相同的代码:

#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

如果我使用 SQL 运行作业步骤，则文件将被删除，但不会出现在存储帐户中。 如果我手动运行此代码块，它们就会被移动。

当我在服务器上启动 PowerShell 时，收到一条错误消息:“尝试对‘文件系统’提供程序执行 InitializeDefaultDrives 操作失败。”但是，这并不会真正影响其余操作(例如，将备份文件复制到 BackupArchive 文件夹)。

我应该提到，copy-item 也无法复制到共享，但成功复制到/BackupArchive 文件夹

Answer 1

请注意，这是否会对您有帮助，但您可以尝试使用 New-PSDrive cmdlet 而不是 net use 来映射您的共享:

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
}