Get-VBRBackupRepository - Why no size info?

PowerShell script exchange

Get-VBRBackupRepository - Why no size info?

Veeam Logoby Backerupper » Fri Apr 03, 2015 4:14 pm

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 - powershell-f26/repository-size-t10674.html#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!
Backerupper
Enthusiast
 
Posts: 42
Liked: 10 times
Joined: Fri Dec 28, 2012 2:32 pm

Re: Get-VBRBackupRepository - Why no size info?

Veeam Logoby tsightler » Fri Apr 03, 2015 11:04 pm 1 person likes this post

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
   }
}
tsightler
Veeam Software
 
Posts: 4765
Liked: 1737 times
Joined: Fri Jun 05, 2009 12:57 pm
Full Name: Tom Sightler

Re: Get-VBRBackupRepository - Why no size info?

Veeam Logoby Backerupper » Sat Apr 04, 2015 12:11 am

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.
Backerupper
Enthusiast
 
Posts: 42
Liked: 10 times
Joined: Fri Dec 28, 2012 2:32 pm

Re: Get-VBRBackupRepository - Why no size info?

Veeam Logoby tsightler » Sat Apr 04, 2015 3:09 am 1 person likes this post

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
   }
}
tsightler
Veeam Software
 
Posts: 4765
Liked: 1737 times
Joined: Fri Jun 05, 2009 12:57 pm
Full Name: Tom Sightler

Re: Get-VBRBackupRepository - Why no size info?

Veeam Logoby Backerupper » Mon Apr 06, 2015 9:17 pm

Very nice Tom

Thanks a bunch!

Now if we could only get the Get-VBRBackupRepository cmdlet updated ;-)
Backerupper
Enthusiast
 
Posts: 42
Liked: 10 times
Joined: Fri Dec 28, 2012 2:32 pm

Re: Get-VBRBackupRepository - Why no size info?

Veeam Logoby Talom » Mon Sep 28, 2015 1:29 pm

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?
Talom
Influencer
 
Posts: 13
Liked: 1 time
Joined: Tue Oct 21, 2014 7:56 am

Re: Get-VBRBackupRepository - Why no size info?

Veeam Logoby Backerupper » Tue Feb 09, 2016 5:12 pm

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)
Backerupper
Enthusiast
 
Posts: 42
Liked: 10 times
Joined: Fri Dec 28, 2012 2:32 pm


Return to PowerShell



Who is online

Users browsing this forum: No registered users and 4 guests