Search code examples
sql-serverpowershellstart-job

Using Credential breaks the script block

Overview: This is a PowerShell script created to automate SQL database backup which must be done in parallel as much as possible.

This works fine.

$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-BACKUP\SQLBACKUP\Manual 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

However, when I converted the code to accept credentials instead of hard coded ones, I'm getting errors:

$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:\Users\edgar.bayron\Music\Edgar\Trash Can\DB-BACKUP\SQLBACKUP\LAX-DBMON\' # "F:\DB-BACKUP\SQLBACKUP\Manual Logshipping Restoration\"
$BackupDestinationDirectory = 'C:\Users\edgar.bayron\Music\Edgar\Trash Can\DB-BACKUP\SQLBACKUP\DAL-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

Error:

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.

I'm trying to apply what was recommended here, but I cannot fully apply it since in the example, it only has one parameter, while I have multiple. If I try to apply it, it searches for other variables.

I tried to use {PARAM($ServerInstanceSource, $cred, $BackupFullScript) but it breaks the SQL query inside $BackupFullScript.

Can anyone help me on this?

Solution

  • You are trying to use a credentials object as a string. What happens is $Cred gets converted to a string using it's .ToString() method which will look like this System.Management.Automation.PSCredential which obviously will not work when passed as the argument to the -Credential parameter in your scriptblock.

    You can either return to your original implementation of using Username and Password where you can pass the username and password taken from the $Cred object

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

    or you can create a scriptblock with a param() block where you can pass in the $Cred object as an argument

    EDIT - the $cmd scriptblock moved out to before the foreach so it is only created once.

    $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
}