Backup size report

[masonit]

Hi!

If you choose "Properties" on a backup job under Backups\Disk in Veeam Backup & Replication. Then there is a column that show "Backup size" of every restore point. Where is this information stored? In the MS SQL database? I would like to build reports that show the total backup size of each backup job. For me it would be easiest to do this with SQL querys if that is possible?

\Masonit

[Gostev]

Hi Magnus, yes this is stored in the SQL database. You can try and query SQL directly, however keep in mind that the next release may change the structure. Thanks!

[masonit]

Hi Magnus, yes this is stored in the SQL database. You can try and query SQL directly, however keep in mind that the next release may change the structure. Thanks!

Sounds great! Please point me in the right direction. What tables and columns should i query?

\Masonit

[foggy]

Magnus, please review this topic, should help. Thanks.

[masonit]

Magnus, please review this topic, should help. Thanks.

I am running 6.5 and I can't find the table "reportsessionsview". Do you know what table that has replaced it?

\Masonit

[foggy]

This is exactly what we are talking about when saying that the database structure can change between the releases. Unfortunately, I cannot advise you here, but you can try to figure that out yourself.

[tsightler]

While accessing the SQL tables directly would work, as others have pointed out, this is not supported due to the fact that the database schema is subject to change between versions and is not documented as it's not intended as a customer interface. Use at your own risk.

You should be able to access the information you are looking for easily enough by using PowerShell. A very simple powershell example that shows the same information:

Code: Select all

$backup = Get-VBRBackup -Name "Backup Job 1"
($backup.GetStorages() | Select-Object -ExpandProperty stats)

[tsightler]

If you're looking for output that almost perfectly matches the "Properties" output in the GUI, then the following is a slight modification of my previous post into a "one-liner" (it's technically a single line, but for posting on the forum I split it across multiple lines in the hope that it would be more clear). This produces nearly identical output to the GUI, minus the friendly things like converting to MB/GB in the output, although I guess you could add this simply enough as well:

Code: Select all

(Get-VBRBackup -Name "<Backup_Job_Name>").GetStorages() | Select-Object -Property `
    @{N="Name";E={$_.FileName}},`
    @{N="Date";E={$_.CreationTime}},`
    @{N="Data Size";E={$_.Stats.DataSize}},`
    @{N="Backup Size";E={$_.Stats.BackupSize}},`
    @{N="De-dupe Ratio";E={$_.Stats.DedupRatio}},`
    @{N="Compress Ratio";E={$_.Stats.CompressRatio}} | Sort Date -Descending | Format-Table

There's probably an easier/slicker way to do this as output in Powershell is not exactly my thing, but the main goal was to show how easy it is to get this information via Powershell, stuff it into a variable, and then do whatever you want with it.

[masonit]

If you're looking for output that almost perfectly matches the "Properties" output in the GUI, then the following is a slight modification of my previous post into a "one-liner" (it's technically a single line, but for posting on the forum I split it across multiple lines in the hope that it would be more clear). This produces nearly identical output to the GUI, minus the friendly things like converting to MB/GB in the output, although I guess you could add this simply enough as well:

Code: Select all

(Get-VBRBackup -Name "<Backup_Job_Name>").GetStorages() | Select-Object -Property `
    @{N="Name";E={$_.FileName}},`
    @{N="Date";E={$_.CreationTime}},`
    @{N="Data Size";E={$_.Stats.DataSize}},`
    @{N="Backup Size";E={$_.Stats.BackupSize}},`
    @{N="De-dupe Ratio";E={$_.Stats.DedupRatio}},`
    @{N="Compress Ratio";E={$_.Stats.CompressRatio}} | Sort Date -Descending | Format-Table

There's probably an easier/slicker way to do this as output in Powershell is not exactly my thing, but the main goal was to show how easy it is to get this information via Powershell, stuff it into a variable, and then do whatever you want with it.

Thanks!

But I don't get it to work. I have made it to one line and changed <Backup_Job_Name> to a real job. This is the error i get:

Code: Select all

Select-Object : A positional parameter cannot be found that accepts argument 'System.Object[]'.
At line:1 char:75
+ (Get-VBRBackup -Name "infrastruktur_backup").GetStorages() | Select-Object <<<<  -Property `@{N="Name";E={$_.FileName}},`@{N="Date";E={$_.CreationT
ime}},`@{N="Data Size";E={$_.Stats.DataSize}},`@{N="Backup Size";E={$_.Stats.BackupSize}},`@{N="De-dupe Ratio";E={$_.Stats.DedupRatio}},`@{N="Compres
s Ratio";E={$_.Stats.CompressRatio}} | Sort Date -Descending | Format-Table
    + CategoryInfo          : InvalidArgument: (:) [Select-Object], ParameterBindingException
    + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.SelectObjectCommand

\Masonit

[tsightler]

Looks like line wrap may still be messing up something. What happens if you just run the following:

Code: Select all

(Get-VBRBackup -Name "<Backup_Job_Name>").GetStorages()

The rest of the code is basically just for formatting.

[masonit]

Looks like line wrap may still be messing up something. What happens if you just run the following:

Code: Select all

(Get-VBRBackup -Name "<Backup_Job_Name>").GetStorages()

The rest of the code is basically just for formatting.

That works, I get the following columns:

Info
Id
FilePath
FileName
DirPath
HostId
BackupId
CreationTime
Stats
BlockSize

I am total new to powershell but trying to learn. Is "GetStorages" a powershell command? I can't find that command when using "Get-VBRCommand".

\Masonit

[veremin]

It seems like you haven’t deleted “`” sign while copying the script body. Without it everything should work fine.

Hope this helps.
Thanks.

[tsightler]

Honestly, I'm not a Powershell guru myself, I've mainly just picked it up, I'm more of a Powershell hacker, so I probably won't explain stuff the way a Powershell programmer actually would, but I'll give it a shot.

Basically, Powershell is object oriented so when you run a command they always return an object that has members. These members typically consist of "properties" and "methods". Properties are basically the "variables" that define the object, typically strings, numbers, etc., but can also contain other objects. Methods are effectively functions that can be called on the object. You can get a list of members on any object with the "Get-Member" cmdlet (many times abbreviated as gm).

So, for example, if you run the following:

Code: Select all

Get-VBRBackup -Name "<backup_job>"

You will get a VBRBackup object, if you instead pipe this object to "get-member"

Code: Select all

Get-VBRBackup -Name "<backup_job>" | gm

It will give a long list of methods and properties for this object. The properties will be things like the job name, job id, etc., and the methods will be various functions that you can perform against the job object, one of which is the GetStorages() method (there are many other methods such as GetObjects, which would get objects in the backup, etc., most are pretty self explanatory). The GetStorages() method returns a list of restore points, and their details, for the backup object.

Now that we know this we can explain what is happening when running the following:

Code: Select all

(Get-VBRBackup -Name "<backup_job").GetStorages()

So the Get-VBRBackup cmdlet returns the object of the job with the name "<backup_job>" and then the GetStorages() method is called on the object that is returned. This GetStorages() method then returns an array of objects that are all of the restore points in that backup object. In the slightly more complex example that I posted earlier, I then pipe these objects to the "Select-Object" cmdlet, which gathers only the objects properties that I want, with my own property names that match the GUI, pipes them to the "Sort" cmdlet, to sort them in date order (sort on CreationDate), and then finally pipes them to the Format-Table cmdlet, which outputs them in a fairly readable table format that is similar to the output of the GUI.

If you can get it to work, the output looks something like this:

Code: Select all

Name            Date                      Data Size    Backup Size   De-dupe Ratio   Compress Ratio
----            ----                      ---------    -----------   -------------   --------------
Backup2013-0... 1/10/2013 1:01:28 AM   279173029946    74433353216              47               55
Backup2013-0... 1/9/2013 1:01:00 AM      9757833152     2954955776              75               40
Backup2013-0... 1/8/2013 1:01:36 AM      2050007956      395783680              96               19
Backup2013-0... 1/7/2013 1:01:12 AM      1871750033      359310848              98               19
Backup2013-0... 1/6/2013 1:01:19 AM      1933088187      375085056              92               20
Backup2013-0... 1/5/2013 1:01:21 AM      3029638311      528112640              87               19
Backup2013-0... 1/4/2013 1:01:10 AM      1751682738      350942208              99               20

Below is a copy without the "`" marks as perhaps some browsers are being confused by this and not properly copying the code.

Code: Select all

(Get-VBRBackup -Name "<Backup_Job_Name>").GetStorages() | Select-Object -Property @{N="Name";E={$_.FileName}},@{N="Date";E={$_.CreationTime}},@{N="Data Size";E={$_.Stats.DataSize}},@{N="Backup Size";E={$_.Stats.BackupSize}},@{N="De-dupe Ratio";E={$_.Stats.DedupRatio}},@{N="Compress Ratio";E={$_.Stats.CompressRatio}} | Sort Date -Descending | Format-Table

I hope all of that made sense. If you have more questions about Powershell you might want to move over to our Powershell forum, lot's of good info over there.

[masonit]

It seems like you haven’t deleted “`” sign while copying the script body. Without it everything should work fine.

Hope this helps.
Thanks.

Yes it did. Thanks!

Why can't I use wildcard in job name?

\Masonit

[masonit]

If you have more questions about Powershell you might want to move over to our Powershell forum, lot's of good info over there.

I will, thank you for great explanation!

\Masonit

[tsightler]

Why can't I use wildcard in job name?

The code as written should work fine with a wildcard in the job name, but the output is not sorted or separated by job. If you just want every restore point from every job in a sorted list, by job name then you should just tweak the sort parameter to sort by Name and Date instead of just the Date as it currently does. Just changes the sort as follows:

Code: Select all

Sort Name,Date -Descending

If you want each job to output it's own unqiue list, it wouldn't be hard to add some looping with a "ForEach-Object" cmdlet.