Query backup job space on Veeam Repositories and find orphaned files with PowerShell

Some time ago I had to investigate discrepancies in the space consumption of large Veeam scale-out repositories. Theoretically, about the same space should have been allocated. But in reality the difference was quite huge. In this post I show how to query backup job space on Veeam Repositories and how to find orphaned files with PowerShell.

In the first part I show how to easily query used space by backup jobs. For more details, even used space of each machine within a backup job can be shown. In second part you can find code snippets to check files on repository (even scale-out) against backup files in Veeam database. Physicals files not listed in database are orphaned.

Analyze used Repository space

Restore Points are files in repositories. Veeam is aware of these files and keeps track of storage space they occupy. To check this space for complete backup jobs, you can use the following code.

(Get-VBRBackup | Where-Object {$_.JobType -eq "Backup"})  |Select-Object jobname, @{N="Backupsize"; E={(($_.GetAllStorages().stats.backupsize | Measure-Object -Sum).Sum) }}

Output contains jobname and used space on storage. In this code I selected just backup-jobs.

If space occupied by each object within a backup job is interesting, the following code can be used to analyze.

(Get-VBRBackup  | Get-VBRRestorePoint)  |Select-Object vmname, @{N="Job"; E={$_.getsourcejob().name }}, @{N="Repo"; E={$_.getrepository().name }} , @{N="size"; E={$_.getstorage().stats.BackupSize }}

The output of this one-liner should be the same as properties of disk backups.

When total over all this corresponds to used space on repository volumes, everything is OK. But take also space savings of Fast Cloning with ReFS and XFS into account. If not, further investigations should be done.

Check files against restore points (find orphaned files)

You can check each physical file with Veeam known files in database. To start with, get a list of all file in a repository.

Export file list

To export all repository files in XML, you can run following code.

$result = @()
$dirs = (Get-ChildItem path_to_repo).fullname
foreach ($dir in $dirs) {
    $temp = @()
    $temp = Get-ChildItem -Path $dir -Depth 2 -file | Select-Object FullName, pschildname, length, creationtime, lastaccesstime
    $result += $temp
}
$result | Export-Clixml -Path C:\path_to_xml.xml

Notes

• Replace path_to_repo by your repository path.
• Works this way just for Windows.

Next build an array of Veeam files in database with absolute path.

Query database pre-v10

In Version 9.5 I have not found any other way than querying the SQL server directly.

I used this script (http://sebastiaan.tempels.eu/2017/05/05/veeam-orphaned-files/) to start with. This script is limited to local data. To use it for distributed environments, you can start with the following snippets.

$vbrdata=@(); $Source=@(); 
$Source  = Import-Clixml -Path C:\path_to_xml.xml
$SOR = Get-VBRBackupRepository -name repo_name -ScaleOut | Get-VBRRepositoryExtent
$data = Get-VBRBackup  | Get-VBRRestorePoint
foreach ($d in $data){
    $extent = (Invoke-Sqlcmd -Query "SELECT [dependant_repo_id] FROM [VeeamBackup].[dbo].[Backup.ExtRepo.Storages] WHERE [storage_id] = '$($d.StorageId)';" -ServerInstance "sql_server_Instance").dependant_repo_id
    $base = ($SOR | Where-Object {$_.id -eq $extent}).Repository.FriendlyPath
    $path = $base+"\"+$d.FindBackup().DirPath+"\"+$d.GetStorage().FilePath
    $vbrdata += $path
}

Notes

• Replace path_to_xml.xml by your preferred locations of the XML file.
• Set repo_name to your repository name.
• Your SQL Server and instance name should be coded instead of sql_server_Instance.
• If passthrough authentication to SQL Server does not work, you can use parameters -Username and -Password.

Query database post-v9

In current version v10 and v11 there is better way to find needed information. Use this code to do so.

$vbrdata=@(); $Source=@(); 
$Source  = Import-Clixml -Path C:\path_to_xml.xml
$SOBR = Get-VBRBackupRepository -name SOBR_blob -ScaleOut
$SOR = $SOBR | Get-VBRRepositoryExtent
$data = Get-VBRBackup  | ? {$_.getrepository().name -in $SOBR.Name} 
$repo = $data[0].GetRepository()
foreach ($d in $data.GetAllStorages()){
    $extent = $repo.FindRepositoryForExistingStorage($d.Id)
    $base = ($SOR | Where-Object {$_.id -eq $extent.id}).Repository.FriendlyPath
	if ($extent.type -eq "LinuxLocal") {$delimiter = '/'} elseif {$delimiter = '\'}
    $path = $base+$delimiter+$d.FindBackup().DirPath+$delimiter+$d.FilePath
    $path = $base+"\"+$d.FindBackup().DirPath+"\"+$d.FilePath
    $vbrdata += $path
}

Notes

• Replace path_to_xml.xml by your preferred locations of the XML file.
• This code works for VBR v11. For v10 you need to replace method FindRepositoryForExistingStorage by FindExtentRepo.
• At the time of writing I noticed my 9.5 code does not work with versions post-v9. Honestly this code is neither very well tested nor very efficient. I will update the script when I analyze a more current version.
• Variable $delimiter should ensure that the code works for Linux repositories too.

List orphaned files

Compare physical files with files in Veeam database.

$Orphant = @()
foreach ($s in $Source){
    if ($vbrdata -notcontains $s.fullname){
        $Orphant += $s
    }
}

Array $Orphant now contains all filesystem files, not known by Veeam as restore points. Notice: With these snippets, DB-transaction logs backups are also in the list. They are shown as *.vlb files.

General notes

• Get your trial of Veeam Backup&Replication!
• Also check my posts about analyzing space savings with Veeam Fast Clone for ReFS and XFS used.