使用凭据会中断脚本块

Question

概述：这是一个PowerShell脚本，用于自动执行SQL数据库备份，必须尽可能并行执行。

这很好用。

$ServerInstanceSource = ".INSTANCE01"
$ServerInstanceDestination = ".INSTANCE02"
$UserName = 'user'
$PassWord = 'pass'
$GetDBs = Invoke-Sqlcmd -ServerInstance $ServerInstanceSource -Username $UserName -Password $Password -Database tempdb -Query "SELECT name FROM sys.databases WHERE database_id > 4 AND recovery_model_desc != 'SIMPLE'"
$Date =  (Get-Date -Format 'yyyyMMddhhmmss')
$BackupSourceDirectory = 'F:DB-BACKUPSQLBACKUPManual Logshipping Restoration'
$BackupDestinationDirectory = 'D:BACKUPS'
Get-Job | Remove-Job -Force
Write-Output "Creating FULL backup. . ."
ForEach($db in $GetDBs.name){
$FileName = "FULL-$db-$Date.bak"
$FullBackupPath = Join-Path -Path $BackupSourceDirectory -ChildPath $FileName
$BackupFullScript = "BACKUP DATABASE $db TO DISK = N'$FullBackupPath' WITH NOFORMAT, NOINIT,  NAME = N'$db-FULL Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10"
$SQLBackupFull = "Invoke-Sqlcmd -ServerInstance $ServerInstanceSource -Username $UserName -Password $Password -Database tempdb -Query `"$BackupFullScript`" -QueryTimeout 21600;" # 5 hours
[scriptblock]$cmdblock1 = [ScriptBlock]::Create($SQLBackupFull)
Start-Job $cmdblock1
}
Get-Job | Wait-Job
Get-Job | Receive-Job

然而，当我将代码转换为接受凭据而不是硬编码凭据时，我会出现错误：

$ServerInstanceSource = ".INSTANCE01"
$ServerInstanceDestination = ".INSTANCE02"
$Cred = Get-Credential
$GetDBs = Invoke-Sqlcmd -ServerInstance $ServerInstanceSource -Credential $Cred -Database tempdb -Query "SELECT name FROM sys.databases WHERE database_id > 4 AND recovery_model_desc != 'SIMPLE' -- Dynamic; Input specific databases only, if necessary."
$Date =  (Get-Date -Format 'yyyyMMddhhmmss')
$BackupSourceDirectory = 'C:Usersedgar.bayronMusicEdgarTrash CanDB-BACKUPSQLBACKUPLAX-DBMON' # "F:DB-BACKUPSQLBACKUPManual Logshipping Restoration"
$BackupDestinationDirectory = 'C:Usersedgar.bayronMusicEdgarTrash CanDB-BACKUPSQLBACKUPDAL-INDBS01' # "D:BACKUPS"
Get-Job | Remove-Job -Force
Write-Output "Creating FULL backup. . ."
ForEach($db in $GetDBs.name){
$FileName = "FULL-$db-$Date.bak"
$FullBackupPath = Join-Path -Path $BackupSourceDirectory -ChildPath $FileName
$BackupFullScript = "BACKUP DATABASE $db TO DISK = N'$FullBackupPath' WITH NOFORMAT, NOINIT,  NAME = N'$db-FULL Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10"
$SQLBackupFull = "Invoke-Sqlcmd -ServerInstance $ServerInstanceSource -Credential $Cred -Database tempdb -Query `"$BackupFullScript`" -QueryTimeout 21600;" # 5 hours
[scriptblock]$cmdblock1 = [ScriptBlock]::Create($SQLBackupFull)
Start-Job $cmdblock1
}
Get-Job | Wait-Job
Get-Job | Receive-Job

错误：

Wait-Job : The Wait-Job cmdlet cannot finish working, because one or more jobs are blocked waiting for user interaction.  Process interactive job 
output by using the Receive-Job cmdlet, and then try again.

我试图应用这里推荐的内容，但我不能完全应用它，因为在这个例子中，它只有一个参数，而我有多个参数。如果我尝试应用它，它会搜索其他变量。

我尝试使用{PARAM($ServerInstanceSource, $cred, $BackupFullScript)，但它破坏了$BackupFullScript内部的SQL查询。

有人能帮我吗？

Answer 1

您正试图将凭证对象用作字符串。发生的情况是，$Cred使用它的.ToString()方法转换为字符串，该方法看起来像System.Management.Automation.PSCredential，当作为参数传递给脚本块中的-Credential参数时，它显然不起作用。

您可以返回到使用用户名和密码的原始实现，在那里您可以传递从$Cred对象获取的用户名和密码

$SQLBackupFull = "Invoke-Sqlcmd -ServerInstance $ServerInstanceSource -Username $($Cred.UserName) -Password $($Cred.GetNetworkCredential().Password) -Database tempdb -Query `"$BackupFullScript`" -QueryTimeout 21600;" # 5 hours

或者您可以创建一个带有param()块的脚本块，在其中您可以将$Cred对象作为参数传递

EDIT-$cmd脚本块移到foreach之前，因此只创建一次

$cmd = {
param(
$ServerInstanceSource,
$Cred,
$BackupFullScript
)
Invoke-Sqlcmd -ServerInstance $ServerInstanceSource -Credential $Cred -Database tempdb -Query $BackupFullScript -QueryTimeout 21600;
}
ForEach ($db in $GetDBs.name) {
$FileName = "FULL-$db-$Date.bak"
$FullBackupPath = Join-Path -Path $BackupSourceDirectory -ChildPath $FileName
$BackupFullScript = "BACKUP DATABASE $db TO DISK = N'$FullBackupPath' WITH NOFORMAT, NOINIT,  NAME = N'$db-FULL Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10"
Start-Job $cmd -ArgumentList $ServerInstanceSource, $Cred, $BackupFullScript
}