List database in backup from PowerShell

[vlad.p]

We developed a PowerShell script to list all the databases included in a Veeam backup job. Starting from Veeam 12.1, it gives an error.

Here is part of the script that is failing:

Code: Select all

#########################################
$jobBackup = GetLastBackup -job $job
$rps = Get-VBRRestorePoint -Backup $jobBackup
$vmNames = $rps | select name -Unique

ForEach ($device in $vmNames.name)
{
	$rp = $rps | where {$_.Name -eq $device} | Sort-Object -Descending CreationTime | Select-Object -First 1
	$gdb = $jobBackup.GetGuestDatabases($rp.ObjectId) | ?{-not $_.isSystem}
}
###################################

Error message:

Code: Select all

	... $gdb = $jobBackup.GetGuestDatabases($rp.ObjectId) | ?{-no ...
	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	CategoryInfo : InvalidOperation: (:) [], RuntimeException
	FullyQualifiedErrorId : MethodNotFound
	Method invocation failed because [Veeam.Backup.Core.CBackup] does not contain a method named 'GetGuestDatabases'.
###################################

From the error message, it's clear that the method named 'GetGuestDatabases' is not supported. I have researched the Veeam forum and the internet and have not been able to find a single PowerShell command that lists all the databases included in the backup.

Does anyone have any suggestions on what command I can use to list the entire database from a backup job from PowerShell or is there a replacement for the GetGuestDatabases method?

[david.domask]

Hi @vlad.p,

You can fetch this from the AuxData property of a COib object returned by Get-VBRestorepoint:

Code: Select all

$backup = Get-VBRBackup -Name 'name of backup with SQL databases'
$rps = Get-VBRRestorePoint -Backup $backup | Sort CreationTime -Descending #Just an example, I usually sort Descending so most recent point is first
PS C:\Users\Administrator> $rps.AuxData


VmMoRef                  : vm-147857
VmRef                    : vm-147857
Platform                 : EVmware
...#omitted some data
VssPersistentSnapInfo    :
OsInfo                   : Veeam.Backup.Model.CGuestOsInfo
ExchangeInfo             :
VeeamArchiverInfo        :
SharePointInfo           :
SqlInfo                  : Veeam.Backup.Model.CGuestSqlInfo  <=== SQL Info
AdInfo                   :                                                         <=== and so on for the next few
OracleInfo               :                                                        <=== and so on for the next few
PostgreSqlInfo           :                                                        <=== and so on for the next few
OraclePfiles             :                                                        <=== and so on for the next few
LocationInfo             :
CreationUsn              : 1
HasVssMetadata           : True
NeedHealthcheckRepair    : False
StorageMetaCorrupted     : False
IsAwsMachine             : False
IsAzureMachine           : False
IsGoogleMachine          : False
GoogleMetadata           :
CreationTimeUtc          : 24/01/22 4:50:27 PM
IsAppAwareProcessed      : True
GuestMountPoints         :



$rps.AuxData.SqlInfo.Databases.Name
master
model
msdb
ccase
acase_reporting
VeeamBackupReporting
master
model
msdb
VeeamBackupReporting
master
model
msdb
VeeamONE

[vlad.p]

Hi @david.domask,

Thanks for the info, tried it and I can get the list of database names in the output. The only thing I noticed is that the output is slightly different from the previous code and my script is producing an error because the ID column is missing in the output. I need this ID column later in my script to compare with the output of a command:

$dbOibs = $rp.GetGuestDatabaseToOibLinks()

and to create a final array based on these two outputs.

Tried today to find any relation in the output from the code "$rps.AuxData.SqlInfo.Databases | ?{-not $_.isSystem}" you shared with me and the output of the command "$gdb = $jobBackup.GetGuestDatabases($rp.ObjectId) | ?{-not $_.isSystem}" but didn't find any solution yet. I will try to research more, in case you have any ideas/suggestions/directions on how can I get the ID column from "$rps.AuxData.SqlInfo.Databases" let me know.

Thanks
Vlad

[david.domask]

Hi @vlad.p, can I ask, which id are you needing? One of the UUIDs to pass this along to other Veeam cmdlets or just the database ID from SQL? The latter is in the AuxData I mentioned.

The methods you're having trouble with I regrettably must inform are all unsupported methods which is why I'm not digging deep into those operations -- these can and will change from release to release so trying to find a way to get your needed output without these methods, so if you can elaborate a little more on what your script is expecting and for what purpose, we can advise better.

[vlad.p]

Hi @david.domask,

There is a current version of the script (created by a previous colleague) that I need to update to support Veeam 12.1, but the more I try to find a brief solution that will support the current script, the more I see that this script needs to be rewritten. I will start on a new script based on your previous suggestion. Anyway, thank you very much for your help. Here is the original part of the script from where you can see why I need an ID column:

Code: Select all

$job = Get-VBRJob -Name $jobName -WarningAction SilentlyContinue
$jobBackup = GetLastBackup -job $job
$rps = Get-VBRRestorePoint -Backup $jobBackup
$vmNames = $rps | select name -Unique
$device = $vmNames.name

$rp = $rps | where {$_.Name -eq $device} | Sort-Object -Descending CreationTime | Select-Object -First 1

#this (now unsupported) method will give us ID column
$gdb = $jobBackup.GetGuestDatabases($rp.ObjectId) | ?{-not $_.isSystem}

If ($rp -ne $null -and $gdb -ne $null)
{
	#from here we are getting column GuestDbId
	$dbOibs = $rp.GetGuestDatabaseToOibLinks()
	
	#here we compare GuestDbId column from $dbOibs and column ID from $gdb to create a new array
	$dbs = $dbOibs | Join-Object -On $gdb -Where {$a.GuestDbId -eq $b.id -and -not $b.isSystem} -Select {@{
	"Name"=$b.Dbname;
	"Servername"=$b.ServerName; 
	"InstanceName"=$b.InstanceName;
	"ObjId"=$b.ObjectId; 
	"RestorePointId"=$a.oibId;
	"dbGuid"=$b.id}}
	write-log "$($dbs.Count) Dbs found for device [$device]"
	
	$dbs| ForEach-Object {
		$dbKey = "$($_.serverName)_$($_.instanceName)_$($_.name)"
		$obj = New-Object System.Object | select planName, deviceName, database, guid, state
		$obj.PlanName = $job.Name
		$obj.deviceName = $device
		$obj.database = $dbKey
		$obj.state = $state
		$obj.guid = "$($_.name)-$($_.dbGuid)"
		$data += $obj
		}
}