I am getting

Invoke-Command: A positional parameter cannot be found that accepts an argument

while running an SQL command inside Invoke-Command.

Tried with stored the value as an array, didn't help me.

[CmdletBinding()]
Param(
    [Parameter(Mandatory = $true)] [string] $IPAddress,
    [Parameter(Mandatory = $true)] [string] $Username,
    [Parameter(Mandatory = $true)] [string] $Password,
    [Parameter(Mandatory = $true)] [string[]][AllowNull()][AllowEmptyCollection()] $DatabaseList = @(),
    [Parameter(Mandatory = $true)] [string] $source_folder
)

$Creds = Create-Credentials -Username $Username -Password $Password

function Start-Prerequisites {
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory = $true)] [string] $source_folder,
        [Parameter(Mandatory = $true)] [string[]][AllowNull()][AllowEmptyCollection()] $DatabaseList = @()
    )
    for ($i=0; $i -le $DatabaseList.Count -1 ; $i++) {
        $j = $i+1
        Write-Host "Database $j is " $DatabaseList[$i]
        $DatabaseName = $DatabaseList[$i]
        $BackUpQuery =
        "USE master
         GO
        --Step-1 : Remove from Availability Group
            BEGIN 
            TRY 
             ALTER AVAILABILITY GROUP AETEST01 REMOVE DATABASE $DatabaseName
              IF @@ERROR <>0
              BEGIN
               PRINT 'Error occured while removing Databases from AVAILABILITY GROUP'
              RETURN
              END
            PRINT 'Databases are removing from AVAILABILITY GROUP Successfull!!' 
        --Step-2 : Take Backup of the database
            Backup Database $DatabaseName To Disk = 'G:\MediumProfile\$DatabaseName.bak' With Format;
            IF @@ERROR <>0
             BEGIN
             PRINT 'Error occured while taking backups'
             RETURN
            END
            PRINT 'Backups taken Successfull!!'
        --Step-3 : Add the database back to Availability Group
            ALTER AVAILABILITY GROUP AETEST01 ADD DATABASE $DatabaseName
            END TRY
            BEGIN CATCH
            PRINT 'Errors occurred!'
            END CATCH"
    }
    Write-Host "Backup Script executing"
    $SQLServer = Invoke-Command -ComputerName $IPAddress -Credential $Creds -ScriptBlock {
        if (Test-Path $args[0]) {
            Write-Host $args[0]" folder is exist"
            $BackFolderContentsValidator = Get-ChildItem -Path $args[0] | Measure-Object
            if (-not ($BackFolderContentsValidator.Count -eq 0)) {
                Write-Host "The Folder is --> "$args[0]
                $ServerInstance = "jmallick\SQLSERVER2012"
                $Query = "$args[1]"
                $BackupDatabase = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database master -Query $Query
                if ($?) {
                    Write-Host "Back process complete"
                } else {
                    Write-Warning "Something wrong in the execution"
                }
            } else {
                Write-Warning "Source folder is exist, but there are no back up files found inside it !!!"
            }
        } else {
            Write-Warning "Source folder is exist, but there are no back up files found inside!!!"
        }
    } -ArgumentList $source_folder $BackUpQuery
Start-Prerequisites -source_folder $source_folder -DatabaseList $DatabaseList

I am trying to take a database back up before that I am validation few things like the folder where I want to keep my backup file is exist or not, if exist whether there is any other file or not.

So inside Invoke-Command, I am validation above conditions and after it, I am trying to take back up by Invoke-Sqlcmd which need a query string and I am passing this query as a parameter to script block.

0 Answers