I have a little powershell script that is fed a list of VM names from an external database, then it uses the Veeam PS snapin to query the Veeam database for that particular VM and find out what job it is in and when its last successful good backup is. It takes these values and writes them back to the same external database. The script i have copied below works fine but is VERY VERY slow! Like takes 4-6 hours to complete and chews up a lot of memory. Can anyone take a look at what I have and perhaps suggest some tweaks or a better method?
Code: Select all
#Add Veeam Powershell snapin
if ((Get-PSSnapin -Name VeeamPSSnapIn -ErrorAction SilentlyContinue) -eq $null)
{
Add-PsSnapin VeeamPSSnapIn
}
#this part sets up the connection to the external DB and grabs the list of VMs to check
$SQLServer = “AppServer”
$SQLDBName = “BackupView”
$SqlQuery = "SELECT * FROM SCCM where ServerArchitecture = 'Virtual' and (select count(*) from SCCMServerExceptions where Name0 = SCCM.Name)=0 order by Name"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = “Server=$SQLServer;Database=$SQLDBName;Integrated Security=True”
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
foreach ($row in $DataSet.Tables[0].Rows)
{
#This part loops through the list of VM names and grabs $LastCompletion and $JobName from the Veeam DB
Write-host $row["Name"]
$VM = $row["Name"]
$LastCompletion = (Get-VBRRestorePoint -name "$VM" | where {$_.FindSourceJob().JobType -eq "Backup"} | sort creationtime -Descending | select -first 1).CreationTimeString
$JobName = (Get-VBRRestorePoint -name "$VM" | sort creationtime -Descending | select -first 1).FindSourceJob().name
If (($LastCompletion -ne $Null) -and ($JobName -ne $Null)){
#This part writes the resultant #LastCompletion and #JobName back to the external DB
$sqlQuery = "Delete from dbo.VeeamStatus where Name = '$VM';INSERT INTO dbo.VeeamStatus (Name, JobName, LastCompletionTime) VALUES ('$VM', '$JobName', '$LastCompletion')"
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
#write-host $sqlQUery
}else{
write-host "Both variables must have been null"
}
}