Get-VBRBackupRepository - Why no size info?

[Backerupper]

Long over-due feature request - Please add size information to the Get-VBRBackupRepository cmdlet.

For years many of us have used the following function written by ThomasMC found here - http://forums.veeam.com/powershell-f26/ ... tml#p46401

This function, though great at the time has not aged well for many reasons:

• With each update to VBR, often the code needs to be (not easily!) tweaked
  There are issues using this code against remote (non-Windows) CIFS shares larger than 4TB (this is just a general Scripting.FileSystemObject issue)
  It does not include new storage types (such as DD)

I'm not sure how something so simple is being overlooked? Just a simple Total and Used/Free space would be wonderful!

[tsightler]

I agree that this would be useful to have as a native property, and we would probably also need a method to refresh the information since right now the information kept in our DB is only updated on certain events, like at the end of a job, during a repo rescan, or when triggered by the GUI. We can already force a complete repo rescan via Powershell but that's pretty heavy handed just to refresh space info.

As a workaround you can try the method below in place of the original code from ThomasMC. I put it in the same format of his original function but I came up with the concept some time ago for another user. It's a simpler concept where I make a single call to an internal function to refresh the free space for the repo in the Veeam DB and then I just grab the values straight from there. I believe it addresses most of the limitations of the original code. It shouldn't have any issues with reporting the right amount of space for CIFS repos, and it should work with any new storage types, and I don't remember changing this code at all since it was written and it still works with v8. It's certainly possible the table I'm pulling the data from might change, but I'm only using the ID and the free/total space columns, so as long as those are still there and the table name doesn't change it will still work. Certainly the internal function call might change, but it would be easy to fix if any of those things changed.

Might be an improvement for now. Feedback is certainly welcome. The code grabs the DB information directly from the registry on the Veeam server and should work fine if you're using integrated SQL authentication but would need some minor modifications if you're using the new SQL auth support in v8.

Code: Select all

function Get-vPCRepoInfo {
[CmdletBinding()]
   param (
      [Parameter(Position=0, ValueFromPipeline=$true)]
      [PSObject[]]$Repository
      )
   Begin {
      $dbServer = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication').SqlServerName
      $dbInstance = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication').SqlInstanceName
      $dbName = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication').SqlDatabaseName
      $outputAry = @()
      function Build-Object {param($name, $path, $free, $total)
         $repoObj = New-Object -TypeName PSObject -Property @{
               Target = $name
               storepath = $path
               StorageFree = [Math]::Round([Decimal]$free/1GB,2)
               StorageTotal = [Math]::Round([Decimal]$total/1GB,2)
               FreePercentage = [Math]::Round(($free/$total)*100)
            }
         return $repoObj
      }
   }
   Process {
      foreach ($r in $Repository) {
         if ($r.GetType().Name -eq [String]) {
            $r = Get-VBRBackupRepository -Name $r
         }
         # Force refresh of free space in repo
         [Veeam.Backup.Core.CBackupRepositoryEx]::SyncSpaceInfoToDb($r, $true)
         # Grab free space for repo directly from DB
         $rspace = Invoke-Sqlcmd -Query "SELECT total_space, free_space from [dbo].[BackupRepositories] where id = '$($r.id.ToString())'" -ServerInstance $($dbServer + "\" + $dbInstance) -Database $dbName
         $outputObj = Build-Object $r.Name $r.Path $rspace.free_space $rspace.total_space
         $outputAry = $outputAry + $outputObj
      }
   }
   End {
      $dbInstance
      $outputAry
   }
}

[Backerupper]

Hi Tom - thanks for the quick reply!

I love the idea behind going straight to the DB for the info - the only issue is the availability of sqlps (Invoke-Sqlcmd) on your typical VBR server (think remote SQL servers for large installations and express/embedded for smaller ones). I'd really like to not have the dependency if at all possible.

[tsightler]

My setup actually uses remote SQL, but I think I happen to have sqlcmd installed due to some other reasons so I get your point. In all honesty I was just being lazy using Invoke-Sqlcmd. You can easily modify the script to use the .NET Framework Data Provider for SQL Server which, as far as I know, has no other dependencies. I'm not as familiar with it, and it takes more lines of code, but below is an example that I hacked together and it worked for me. I tested it on a fresh Windows 2008R2 install with nothing but Veeam v8 Patch 1 installed and it worked just fine.

BTW, the code below has this little magic:

Code: Select all

[Veeam.Backup.Core.CBackupRepositoryEx]::SyncSpaceInfoToDb($r, $true)

This forces Veeam to refresh the current space information for the specific repository and is an internal call that has changed at least once very recently. When V8 Patch 1 was released it added a second parameter so I had to modify that. It was a very simple modification, but that is one of the risk when calling directly into the underlying code. The user that I originally developed the entire "repo free space" code for wasn't actually using this call so that's why I didn't remember it, he was just living with the fact that pulling from the database wasn't 100% accurate in real time. It's up to you whether you consider it valuable enough. My concern was that a large job can run a long time and consume a lot of space before it is updated in the DB, but if you have a lot of jobs running all the time this may not be much of an issue since there will always be jobs ending and updating the data.

I guess the supported way to do this would be to do something like:

Code: Select all

Get-VBRBackupRepository -Name "Name of repository" | Rescan-VBREntity -Wait

But as I mentioned in the first post that's pretty heavy as it rescans the entire repo and can take quite a long time for a large repo with many jobs/restore points. I'm actively looking for an alternative method to trigger this.

Interestingly the REST API does offer free/total space information, but it's also not real time and even calling the above code does not update it immediately. But perhaps that's also an alternative, should be pretty easy to make the REST call from Powershell, although I've never really tried it.

Once again, feedback is welcome and thanks for the discussion. Here is the modified code using System.Data.SqlClient instead of Invoke-Sqlcmd:

Code: Select all

function Get-vPCRepoInfo {
[CmdletBinding()]
   param (
      [Parameter(Position=0, ValueFromPipeline=$true)]
      [PSObject[]]$Repository
      )
   Begin {
      $dbServer = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication').SqlServerName
      $dbInstance = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication').SqlInstanceName
      $dbName = (Get-ItemProperty -Path 'HKLM:\SOFTWARE\Veeam\Veeam Backup and Replication').SqlDatabaseName
      $outputAry = @()
      function Build-Object {param($name, $path, $free, $total)
         $repoObj = New-Object -TypeName PSObject -Property @{
               Target = $name
               storepath = $path
               StorageFree = [Math]::Round([Decimal]$free/1GB,2)
               StorageTotal = [Math]::Round([Decimal]$total/1GB,2)
               FreePercentage = [Math]::Round(($free/$total)*100)
            }
         return $repoObj
      }
   }
   Process {
      foreach ($r in $Repository) {
         if ($r.GetType().Name -eq [String]) {
            $r = Get-VBRBackupRepository -Name $r
         }
         # Force refresh of free space in repo
         # Setup SQL server connection
         [Veeam.Backup.Core.CBackupRepositoryEx]::SyncSpaceInfoToDb($r, $true)

         # Grab Total/Free space from DB
         $sqlConn = New-Object System.Data.SqlClient.SqlConnection
         $sqlConn.ConnectionString = "Server=$dbServer;Database=$dbName;Integrated Security=True"
         $sqlConn.Open()
         $sqlCmd = New-Object System.Data.SqlClient.SqlCommand
         $sqlCmd.Connection = $sqlConn
         $sqlCmd.CommandText = "SELECT total_space, free_space from [dbo].[BackupRepositories] where id = '$($r.id.ToString())'"
         $sqlReader = $sqlCmd.ExecuteReader()
         while ($sqlReader.Read()) { 
            $totalspace = $sqlReader["total_space"]
            $freespace = $sqlReader["free_space"]
         }
         $sqlConn.Close()

         $outputObj = Build-Object $r.Name $r.Path $freespace $totalspace
         $outputAry = $outputAry + $outputObj
      }
   }
   End {
      $dbInstance
      $outputAry
   }
}

[Backerupper]

Very nice Tom

Thanks a bunch!

Now if we could only get the Get-VBRBackupRepository cmdlet updated

[Talom]

I don't know why, but i had to modify the connection String to get the function work.

Code: Select all

$sqlConn.ConnectionString = "Server=$dbServer\$dbInstance;Database=$dbName;Integrated Security=True"

I think this Connection String should work on every machine or am i wrong?

[Backerupper]

It looks as though this info has been added to v9 (yay!)

Code: Select all

$repolist = Get-VBRBackupRepository
$r = $repolist | select -First 1
$rTotal = $r.Info.CachedTotalSpace / 1GB
$rFree = $r.Info.CachedFreeSpace / 1GB

Is there any way to refresh this info via PS?
I am aware of the following method (which does appear to work) but would prefer to use native PS if possible

Code: Select all

[Veeam.Backup.Core.CBackupRepositoryEx]::SyncSpaceInfoToDb($r, $true)

[tomhkr]

The command doesn't give ScaleOut repository sizes.

Using the following there is nothing to expand.

Code: Select all

Get-VBRBackupRepository -ScaleOut

Using the following, the extents of a ScaleOut repository are not shown at all.

Code: Select all

Get-VBRBackupRepository

For storage monitoring purposes I'd really like if there was a Powershell way of getting a ScaleOut repository size.

[veremin]

I don't have a console at hand, but try the following:

* Get a Scale-Out Backup Repository (Get-VBRBackupRepository -ScaleOut)
* Get particular extent (Get-VBRRepositoryExtent)
* Get underlying repository ($Extent.Repository)
* Get required information, using parameters of underlying repository

Check our Help Center for more information.

Thanks.

[tomhkr]

Hey, thanks a lot. This works well!

Code: Select all

(Get-VBRBackupRepository -ScaleOut|Get-VBRRepositoryExtent).Repository.Info.CachedTotalSpace
(Get-VBRBackupRepository -ScaleOut|Get-VBRRepositoryExtent).Repository.Info.CachedFreeSpace

[veremin]

By default, Scale-Out Backup Repositories and Extents get filtered out from Get-VBRBackupRepository input. So, you have to use slightly different approach to get needed information.

Glad to hear that now everything works as expected.