Solved

SQL Commands Veeam


Userlevel 1

SQL Commands for Veeam to be able to link a Backup Job that was done before the Veeam B&R Server was migrated to a new server. and Perform a Backup Copy Job.

icon

Best answer by Chris.Childerhose 12 April 2022, 15:40

View original

11 comments

Userlevel 7
Badge +20

SQL Commands for Veeam to be able to link a Backup Job that was done before the Veeam B&R Server was migrated to a new server. and Perform a Backup Copy Job.

I am not aware of any SQL commands as you typically use the Map Backup feature in the job to remap to an existing backup location as per here - Step 8. Map Backup File - User Guide for VMware vSphere (veeam.com)

Userlevel 7
Badge +20

Chris is right here, plus you should finish and disable all jobs before a migration to ensure database consistency with the underlying backups

Userlevel 7
Badge +20

Chris is right here, plus you should finish and disable all jobs before a migration to ensure database consistency with the underlying backups

Yes this is very important as well.

Userlevel 7
Badge +13

I know just 2 situations where I needed direct SQL access to get the work done. One for getting the absolute path of backup files located in SOBR, the second for querying backup files on tape. For all other I highly recommend not to use any direct SQL access - especially for changing something. This is something Veeam support should do.

Userlevel 5
Badge +3

I know just 2 situations where I needed direct SQL access to get the work done. One for getting the absolute path of backup files located in SOBR, the second for querying backup files on tape. For all other I highly recommend not to use any direct SQL access - especially for changing something. This is something Veeam support should do.

This one isn’t entirely true, but I agree it’s not clear if you don’t know how SOBR paths works. 

 

SOBR uses relative paths as a given backup file may be on any extent; to avoid constant lookups to the infra/DB, the paths are stored as relative.

 

I’ll share what’s in my private notes document for engineers and an example:

#Storage Paths (Backup paths)

* Scale-out Repositories (SOBR) and non-SOBR repos have different paths
  * Non-SOBR use full path on system and can be retrieved with just the path for the Storage Object
  * SOBR uses relative paths for the Storage and need to be constructed with:
    * Extent.GetPath()
    * MetaPath property†
    * Storage Name (Storage = backup file, e.g., vbk/vib/vrb)
    *  † You can also use the Job.Name property but this will fail for imported backups on a SOBR as well as Orphaned, so it's better to use the MetaFile Path
    * SOBR requires a metafile for Backups when adding an extent, so it's safe to assume this will be here. Backups on SOBR that somehow have a NULL or empty meta file path should be treated as exceptions and do not need special handling; by definition it is a problem with the environment and it needs to be fixed, not handled by code

In code, you can build this with the following:

 

function Get-StoragesPathsAndBlocksizeFromBackup-v11 {
param(
[Parameter(Mandatory=$true, Position=0)]
[Object[]]$Backup
)
$Storages = $Backup[0].GetallChildrenStorages() | Sort-Object -Property PartialPath, CreationTime -Descending
$Repository = $Backup.FindRepository()[0]
$StoragePathsandBlocksize = @()
if($Repository.Type -eq "ExtendableRepository"){
foreach($Storage in $Storages){
$Extent = $Repository.FindRepositoryForExistingStorage($Storage.Id)
if($Backup.JobID -eq '00000000-0000-0000-0000-000000000000'){
$JobName = $Backup.GetMetaFilePath().Elements[0]
}Else{
$jobName = $Backup.FindJob().Name
}
$StoragePathsandBlocksize += New-Object -TypeName psobject -Property @{Extent=$Extent.Name;Path=$($Extent.Path.ToString(),$JobName.ToString(),$Storage.filepath -join "\");BlockSize=$Storage.BlockAlignmentSize;CreationTime=$Storage.CreationTime}
}
} else {
$StoragePathsandBlocksize += $Storages | Sort-Object -Property PartialPath, CreationTime -Descending | Select-Object -Property PartialPath,BlockAlignmentSize
}
return $StoragePathsandBlocksize
}

This is from a script I built for other diagnostic puropses, and you can check a storage object for other elements you might want in the output. It’s built off of some older code from Tim Dewin I found on the forums from long ago, and just updated it to accommodate for some edge cases. v10 has some differences on the underlying methods, so if it’s required, I can share the v10 code.

 

As for the question at hand, in most cases as long as the old infrastructure is still present and used, there’s no need to edit anything or even remap unless the backup files were hosted on the Veeam server directly (DAS or other volumes right on the server) and the old server was decomissioned. A remap should solve it if that’s the case, and you can use the Copy-VBRJob with the -Repository flag to make copies of all jobs and point them to the new repository, then just clean up the old jobs. Veeam should automatically know to map the jobs. (Take a configuration backup first though as a safety!)

Userlevel 7
Badge +13

I know just 2 situations where I needed direct SQL access to get the work done. One for getting the absolute path of backup files located in SOBR, the second for querying backup files on tape. For all other I highly recommend not to use any direct SQL access - especially for changing something. This is something Veeam support should do.

This one isn’t entirely true, but I agree it’s not clear if you don’t know how SOBR paths works. 

 

SOBR uses relative paths as a given backup file may be on any extent; to avoid constant lookups to the infra/DB, the paths are stored as relative.

 

I’ll share what’s in my private notes document for engineers and an example:

#Storage Paths (Backup paths)

* Scale-out Repositories (SOBR) and non-SOBR repos have different paths
  * Non-SOBR use full path on system and can be retrieved with just the path for the Storage Object
  * SOBR uses relative paths for the Storage and need to be constructed with:
    * Extent.GetPath()
    * MetaPath property†
    * Storage Name (Storage = backup file, e.g., vbk/vib/vrb)
    *  † You can also use the Job.Name property but this will fail for imported backups on a SOBR as well as Orphaned, so it's better to use the MetaFile Path
    * SOBR requires a metafile for Backups when adding an extent, so it's safe to assume this will be here. Backups on SOBR that somehow have a NULL or empty meta file path should be treated as exceptions and do not need special handling; by definition it is a problem with the environment and it needs to be fixed, not handled by code

In code, you can build this with the following:

 

function Get-StoragesPathsAndBlocksizeFromBackup-v11 {
param(
[Parameter(Mandatory=$true, Position=0)]
[Object[]]$Backup
)
$Storages = $Backup[0].GetallChildrenStorages() | Sort-Object -Property PartialPath, CreationTime -Descending
$Repository = $Backup.FindRepository()[0]
$StoragePathsandBlocksize = @()
if($Repository.Type -eq "ExtendableRepository"){
foreach($Storage in $Storages){
$Extent = $Repository.FindRepositoryForExistingStorage($Storage.Id)
if($Backup.JobID -eq '00000000-0000-0000-0000-000000000000'){
$JobName = $Backup.GetMetaFilePath().Elements[0]
}Else{
$jobName = $Backup.FindJob().Name
}
$StoragePathsandBlocksize += New-Object -TypeName psobject -Property @{Extent=$Extent.Name;Path=$($Extent.Path.ToString(),$JobName.ToString(),$Storage.filepath -join "\");BlockSize=$Storage.BlockAlignmentSize;CreationTime=$Storage.CreationTime}
}
} else {
$StoragePathsandBlocksize += $Storages | Sort-Object -Property PartialPath, CreationTime -Descending | Select-Object -Property PartialPath,BlockAlignmentSize
}
return $StoragePathsandBlocksize
}

This is from a script I built for other diagnostic puropses, and you can check a storage object for other elements you might want in the output. It’s built off of some older code from Tim Dewin I found on the forums from long ago, and just updated it to accommodate for some edge cases. v10 has some differences on the underlying methods, so if it’s required, I can share the v10 code.

 

As for the question at hand, in most cases as long as the old infrastructure is still present and used, there’s no need to edit anything or even remap unless the backup files were hosted on the Veeam server directly (DAS or other volumes right on the server) and the old server was decomissioned. A remap should solve it if that’s the case, and you can use the Copy-VBRJob with the -Repository flag to make copies of all jobs and point them to the new repository, then just clean up the old jobs. Veeam should automatically know to map the jobs. (Take a configuration backup first though as a safety!)

Thank you for your function @ddomask ! I really wasn’t aware of this way! Thanks for the clarification!

Userlevel 7
Badge +13

I know just 2 situations where I needed direct SQL access to get the work done. One for getting the absolute path of backup files located in SOBR, the second for querying backup files on tape. For all other I highly recommend not to use any direct SQL access - especially for changing something. This is something Veeam support should do.

This one isn’t entirely true, but I agree it’s not clear if you don’t know how SOBR paths works. 

 

SOBR uses relative paths as a given backup file may be on any extent; to avoid constant lookups to the infra/DB, the paths are stored as relative.

 

I’ll share what’s in my private notes document for engineers and an example:

#Storage Paths (Backup paths)

* Scale-out Repositories (SOBR) and non-SOBR repos have different paths
  * Non-SOBR use full path on system and can be retrieved with just the path for the Storage Object
  * SOBR uses relative paths for the Storage and need to be constructed with:
    * Extent.GetPath()
    * MetaPath property†
    * Storage Name (Storage = backup file, e.g., vbk/vib/vrb)
    *  † You can also use the Job.Name property but this will fail for imported backups on a SOBR as well as Orphaned, so it's better to use the MetaFile Path
    * SOBR requires a metafile for Backups when adding an extent, so it's safe to assume this will be here. Backups on SOBR that somehow have a NULL or empty meta file path should be treated as exceptions and do not need special handling; by definition it is a problem with the environment and it needs to be fixed, not handled by code

In code, you can build this with the following:

 

Thank you for your function @ddomask ! I really wasn’t aware of this way! Thanks for the clarification!

I have to correct myself, I was aware of this already 😁

But interesting, I use my script now and get some strange value in PartialPath. I see two directories in PartialPath. One is the copy and one is the backup target for this VM. Directories are separated by a pipe (“|”). @ddomask  any ideas why?

Userlevel 5
Badge +3

Hi @vNote42, can you show an example? The pipe you can ignore :)

 

When you say copy, do you mean like a catalyst copy maybe?

Userlevel 7
Badge +13

Hi @vNote42, can you show an example? The pipe you can ignore :)

 

When you say copy, do you mean like a catalyst copy maybe?

No, normal copy jobs. But I think I found it already. Mirror-Copy-Jobs seem to create an additional sub-directory in copy-target directory. Looks like this: “D:\Repo\Copy-Job\Backup-Job\vm...vib” This path is returned in PartialPath but with an pipe instead of “/” resp. “\”. In my example “Copy-Job|Backup-Job” I guess this is because Veeam will replace the pipe by forward slash for Linux and back slash for Windows repos.

Userlevel 5
Badge +3

aha, expected on both accounts :)

 

Immediate (Simple) copy has a Worker style resource manager; all processed objects have their own worker thread as the immediate copy may start them at different times, so they get their own folders for each worker.

Userlevel 7
Badge +13

aha, expected on both accounts :)

 

Immediate (Simple) copy has a Worker style resource manager; all processed objects have their own worker thread as the immediate copy may start them at different times, so they get their own folders for each worker.

sounds fair 😊 Just need to use the .replace method to put in the right delimiter for Linux resp. Windows.

Comment