Get backup jobs associated with a VM

[Joel_Miller]

I am using the new Update 4 Powershell snap-in. I have a specific server (rel-dev001) that I need to find the backup job associated with it. I would like to do a file level restore.

It appears to do this I need to know the name of the job for this server. However, I know the server name only. Is there a way to get the job name for this server using the PowerShell module?

Maybe I am taking the wrong approach if anyone has a better idea. I know the name of the server I want to restore from. I need to start a file level restore for the server using the newest backup.

Thanks!

[Lucas_H]

Hello,

I would do something like this :

Code: Select all

asnp VeeamPSSnapin
$ServerName = "Your server name"
$Jobs = Get-VBRJob | ? {$_.IsBackup}
ForEach ($job in $jobs) {
    foreach ($object in $job.GetObjectsInJob()) {
    If ($object.Name -eq $ServerName) 
        {
        $JobWithServ = Get-VBRJob -Name $Job.Name
        $lastsession = $job.FindLastSession() 
        Write-Host $lastsession.Name  $lastsession.Result  $lastsession.CreationTimeUTC
        }
    }
}

It gets all the Job which are backup Job, after that it gets the Job which contains your Server ($ServerName), and finally it gets the last Session for the Job and print out the Job name, the Job Results and the Session CreationTime.
After this, you can select the Job with the latest CreationTime, and then build your file level restore script based on this Job.

Feel free to correct my script as I'm for sure no Powershell expert.

Cheers,
Lucas H

[Vek17]

Worth mentioning that there are some limitations in what will be returned with the above method depending on how your jobs are configured. It will only return if your jobs are populated by VM and not by host and it will not return for agent jobs.

[Lucas_H]

Agreed, It will only work if you populated your Job by VM.
For Windows agent Job, you could use :

Code: Select all

$jobs =  Get-VBRJob | ? {$_.TypetoString -eq "Windows Agent Backup"}

And If you populated yor Job by host you could do another loop to get what is inside the host and get the VM name after that.

I'm not aware of another way of doing this but maybe there is.

Cheers,
Lucas H

[Vek17]

There are two other ways that I know of. One you can searched based on restore points and has the advantage of being comprehensive and well supported at the cost of being fairly slow

Code: Select all

function Find-JobByObjectName{
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$True)]
        [String]$VMName
    )  
    foreach($Backup in Get-VBRBackup){
        foreach($RestorePoint in $Backup | Get-VBRRestorePoint){
            if($RestorePoint.Name -eq $VMName){
                $Backup.getJob()
                break;
            }
        }
    }
}

The other option is to fairly directly query the DB which is very fast but not supported and may change with updates

Code: Select all

function Invoke-VeeamSQL{
    [CmdletBinding()]
    param(
        [Parameter(
            Mandatory=$True, 
            ValueFromPipeline=$True,
            ValueFromPipelineByPropertyName=$True
        )]
        [String]$SQLQuery
    )
    BEGIN{
        $VeeamSQLServer   = Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication\' -Name 'SqlServerName'
        $VeeamSQLDatabase = Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication\' -Name 'SqlDatabaseName'
        $VeeamSQLInstace  = Get-ItemPropertyValue -Path 'HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication\' -Name 'SqlInstanceName'
        $Result = @()
        Write-Verbose "SqlServerName: $($VeeamSQLServer)"
        Write-Verbose "SqlDatabaseName: $($VeeamSQLDatabase)"
        Write-Verbose "SqlInstanceName: $($VeeamSQLInstace)"
    }
    PROCESS{
        Write-Verbose $SQLQuery
        $SQLConn = [System.Data.SqlClient.SqlConnection]::New()
        $SQLConn.ConnectionString = "Server=$VeeamSQLServer\$VeeamSQLInstace;Database=$VeeamSQLDatabase;Integrated Security=True"
        $SQLConn.Open()
        $SQLCmd = [System.Data.SqlClient.SqlCommand]::New()
        $SQLCmd.Connection  = $SQLConn
        $SQLCmd.CommandText = $SQLQuery
        $SQLReader  = $SQLCmd.ExecuteReader()
        $SQLColumns = $SQLReader.GetSchemaTable().ColumnName
        while($SQLReader.Read()){ 
            $Temp = [PSCustomObject]::new()
            foreach($Column in $SQLColumns){
                $Temp | Add-Member -MemberType NoteProperty -Name $Column -Value $SQLReader[$Column]
            }
            $Result += $Temp
        }
        $SQLConn.Close()
    }
    END{
        return $Result
    }
}

Function Find-JobByObjectName {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$True,
		ValueFromPipeline=$True)]
        [String]$VMName
    )  
    BEGIN{
        $Results = @()
    }
    PROCESS{
        $Results += Invoke-VeeamSQL -SQLQuery "
        SELECT job_id FROM [Backup.Model.Backups] WHERE ID IN (
	        SELECT backup_id FROM [Backup.Model.Points] WHERE ID IN (
		        SELECT point_id FROM [Backup.Model.OIBs] WHERE object_id IN (
			        SELECT bObject_id FROM [BObjectsSensitiveInfo] WHERE object_name = '$($VMName)'
                )   
            )
        )"
    }
    END{
        foreach($Result in $Results){
            [Veeam.Backup.Core.CBackupJob]::Get($Result.job_id)
        }
    }
}