Creating a Valid Per VM Backup Without a VBM

[Vek17]

Nothing in this post is supported and I strongly advise you engage Veeam support before attempting anything

All content has only been tested in my environment using Reverse Incremental chains with Per VM Backup Files on Update 4 (9.5.4.2615)
Jobs with SQL log processing are not addressed
Apply at your own risk
This topic is related to but not the same as: powershell-f26/creating-vbm-from-scratch-t20568.html

I recently had an instance where I needed to import a backup but the .VBM file required was no longer present. I engaged Veeam support and with my settings (Per VM, Reverse Incremental) they didn't have a good solution for me and recommended that I just active full everything again to generate a fresh chain. I ended up doing this and things worked just fine as would be expected in a fresh chain, but I decided to figure out how I might be able to do the import in my test environment.

Without a .VBM you can import a backup directly from a .VBK and get the chain of that VM. This however leaves us with two important hurtles to overcome.

• The imported backup is considered to be on a backup repository and cannot be mapped to a job
• The imported backup will only contain the chain of the imported VM, not the data of all the VMs in the backup

For the first hurtle I could not locate a way to change the repository of the backup via powershell or the C# objects (it quite possibly does exist but I know not where) but it seemed simple enough to simply edit the repository_id in the Veeam database for that backup since as far as I can tell that is the only place that was referenced.

Code: Select all

UPDATE [Backup.Model.Backups] SET repository_id = 'REPO ID' WHERE id = 'BACKUP ID'

Once this is done the backup becomes mapable and if you apply to a job you will be able to resume incrementals of that VM. If you only have one VM, or if you are not using per VM backups you are most likely done here.

The second hurtle was where it became a big confusing. Initially I thought that by simply changing the backup_id in all the relevant tables I would be able to combine the manually imported backups into a single working backup. The relevant tables are:

Code: Select all

[Backup.Model.Backups]
[Backup.Model.Points]
[Backup.Model.Storages] 

Naturally I attempted this, all VMs appeared in the backup (so far so good), and I mapped to my test job. However, once I ran the test job only a single VM seemingly at random would get an incremental backup and all other VMs would take a new full then purge ALL of their old backups to retention. After digging in for significantly longer then I would like I realized that [Backup.Model.OIBs] (where restore point information is stored) had shared mappings for each VM in a job to a single point that corresponded to the job run. This was different to what I was doing before where every VM had their own entry in [Backup.Model.Points] for a job run.

With this new information I decided instead of remapping the backup_id in [Backup.Model.Points] I would instead decide on one of my imported backups as the "master" and remap the [Backup.Model.OIBs] entry to the "master's" entry in [Backup.Model.Points]. Because these all came from the same job I ended up basing this mapping on creation_time as all entries were identical for each VM. This worked EXACTLY as I had hoped and all VMs now had incremental backups after being merged from multiple manual imported backups and modified in the DB all without a VBM.

The full script I used is here:

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
    }
}
$Master       = Get-VBRBackup | Where-Object {$_.JobId -eq [GUID]::Empty} |  Out-GridView -OutputMode Single -Title "Select Master Backup"
$MergeBackups = Get-VBRBackup | Where-Object {$_.JobId -eq [GUID]::Empty -and $_.id -ne $Master.id} |  Out-GridView -OutputMode Multiple -Title "Select Backups to Merge"

forEach ($Backup in $MergeBackups){
   foreach($Point in Invoke-VeeamSQL -SQLQuery "SELECT * FROM [Backup.Model.Points] WHERE [backup_id] = '$($Backup.id)' order by creation_time"){
        #Invoke-VeeamSQL -SQLQuery "SELECT * FROM [Backup.Model.Points] WHERE [backup_id] = '$($Master.id)' and [creation_time] = (SELECT creation_time FROM [Backup.Model.Points] WHERE [id] = '$($Point.id)')"
        Invoke-VeeamSQL -SQLQuery "UPDATE [Backup.Model.OIBs] SET point_id = (
                                        SELECT id FROM [Backup.Model.Points] WHERE [backup_id] = '$($Master.id)' 
                                        and [creation_time] = (SELECT creation_time FROM [Backup.Model.Points] 
                                        WHERE [id] = '$($Point.id)')) 
                                    WHERE point_id = '$($Point.id)'"
    }
    Invoke-VeeamSQL -SQLQuery "UPDATE [Backup.Model.Storages] SET backup_id = '$($Master.id)' WHERE backup_id = '$($Backup.id)'"
}
$MergeBackups | Remove-VBRBackup

$Repositories = @(Get-VBRBackupRepository ;Get-VBRBackupRepository -ScaleOut)
$TargetRepo = $Repositories | select name, type, id | Out-GridView -OutputMode Single -Title "Select Repo for Re-Assembled Backup"

Invoke-VeeamSQL -SQLQuery "UPDATE [Backup.Model.Backups] SET repository_id = '$($TargetRepo.id)' WHERE id = '$($Master.id)'"

Note this assumes SQL access via AD Authentication on the Veeam Database. This works with default Veeam install settings but if you are using an alternate SQL setup this may not work.

[nielsengelen]

Thanks for sharing and I'm happy you added a clear red warning on the start of the post .