Invoke-Sqlcmd with querytime out not working RRS feed

  • Question

  • I have the following script to automate the DB back up from database from a remote machine which trigger the script.

        [Parameter(Mandatory = $True)] [string] $IPAddress,
        [Parameter(Mandatory = $True)] [string] $Username,
        [Parameter(Mandatory = $True)] [string] $Password,
        [Parameter(Mandatory = $True)] [string[]][AllowNull()][AllowEmptyCollection()] $DatabaseList=@('Test1','Test2','Test3'),
        [Parameter(Mandatory = $True)] [string] $source_folder
    	Import-Module .\ConnectionModule\VMFunctions.psm1 -DisableNameChecking
    	$isConnected = Wait-ForWinRM -IPAddress $IPAddress -Username $Username -Password $Password
    	if ($isConnected) {
    		$Creds = Create-Credentials -Username $Username -Password $Password
    			function Start-Prerequisites {
    				[Parameter(Mandatory = $True)][string]$source_folder,
    				[Parameter(Mandatory = $True)] [string[]][AllowNull()][AllowEmptyCollection()] $DatabaseList=@('Test1','Test2','Test3')
               $SQLServer = Invoke-Command -ComputerName $IPAddress -Credential $Creds -ScriptBlock {
                     for ($i=0; $i -le $args[1].Count -1 ; $i++){
                            $DatabaseName= $args[1][$i]
                            $Query = "USE master
                            --Step-1 : Remove from Availability Group
                            IF @@ERROR <>0
                            PRINT 'Error occured while removing Databases from AVAILABILITY GROUP'
                            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
                            PRINT 'Error occured while taking backups'
                            PRINT 'Backups taken Successfull!!'
                            --Step-3 : Add the database back to Availability Group
                            ALTER AVAILABILITY GROUP IMSAEPERFAG04 ADD DATABASE $DatabaseName
                            END TRY
                            BEGIN CATCH
                            ALTER AVAILABILITY GROUP IMSAEPERFAG04 ADD DATABASE $DatabaseName
                            PRINT 'Errors occured!'
                            END CATCH"
    					$ServerInstance = "AEPERF01SQL03.AWSDEV.EZESOFTCLOUD.COM"
    					$BackupDatabase= Invoke-Sqlcmd -ServerInstance $ServerInstance -Database master -Query $Query -QueryTimeout 1200
             } -ArgumentList $source_folder,$DatabaseList
    Start-Prerequisites -source_folder $source_folder  -DatabaseList $DatabaseList
    else {
        Write-Warning "Connection to the remote machine unsucessfull"

    At first step I am connecting to the SQL server and in the second command (invoke-sqlcmd) I am triggering the database back up. 

    Actual result: The first database back up is successful but it is not taking the second one (partially the back up is taken).

    What I am doing wrong here, here below some questions in my mind,

    1. Is the connection (invoke-sqlcmd) is breaking ?
    2. Is the connection to the remote machine (invoke-command) is breaking ?
    3. Is the querytimeout is not effective ?

    How to resolve this issue.

    Tuesday, April 30, 2019 7:00 AM

All replies

  • There ae many syntax errors in the code you have posted.  The code is also impossible to understand due to a mixture of things that do not match.  I suspect you have copied this and tried to change it.

    I recommend taking the time to learn how to write code and how to debug your code.

    Start by fixing the syntax errors.  They will be visible if you edit the code with VSCode or PowerShell ISE.

    Here is where to start learning coding with PowerShell.

          Microsoft Virtual     Academy - Getting Started with Microsoft PowerShell

          PowerShell Documentation

          PowerShell Style   Guidelines


    Tuesday, April 30, 2019 7:33 AM
  • @jrv, yeah I only paste the snippet of the code which is actually doing the work. I figure out the issue now it is working fine.
    Tuesday, April 30, 2019 12:20 PM