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,
- Is the connection (invoke-sqlcmd) is breaking ?
- Is the connection to the remote machine (invoke-command) is breaking ?
- Is the querytimeout is not effective ?
How to resolve this issue.
