locked
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.

    [CmdletBinding()]
    Param(
        [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 {
    				[CmdletBinding()]
    				Param(
    				[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
                            GO
                            --Step-1 : Remove from Availability Group
                            BEGIN 
                            TRY 
                            ALTER AVAILABILITY GROUP IMSAEPERFAG04 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 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