Discussions specific to the VMware vSphere hypervisor
Post Reply
masonit
Service Provider
Posts: 218
Liked: 15 times
Joined: Oct 09, 2012 2:30 pm
Full Name: Magnus
Contact:

Backup size report

Post by masonit » Dec 28, 2012 2:05 pm

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
SVP, Product Management
Posts: 24788
Liked: 3521 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Backup size report

Post by Gostev » Dec 29, 2012 5:42 pm

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
Service Provider
Posts: 218
Liked: 15 times
Joined: Oct 09, 2012 2:30 pm
Full Name: Magnus
Contact:

Re: Backup size report

Post by masonit » Jan 07, 2013 2:45 pm

Gostev wrote: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
Veeam Software
Posts: 18251
Liked: 1558 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup size report

Post by foggy » Jan 07, 2013 6:23 pm

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

masonit
Service Provider
Posts: 218
Liked: 15 times
Joined: Oct 09, 2012 2:30 pm
Full Name: Magnus
Contact:

Re: Backup size report

Post by masonit » Jan 08, 2013 4:11 pm

foggy wrote: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
Veeam Software
Posts: 18251
Liked: 1558 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Backup size report

Post by foggy » Jan 09, 2013 1:00 pm

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
VP, Product Management
Posts: 5418
Liked: 2240 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: Backup size report

Post by tsightler » Jan 09, 2013 2:06 pm 1 person likes this post

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
VP, Product Management
Posts: 5418
Liked: 2240 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: Backup size report

Post by tsightler » Jan 09, 2013 3:10 pm

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
Service Provider
Posts: 218
Liked: 15 times
Joined: Oct 09, 2012 2:30 pm
Full Name: Magnus
Contact:

Re: Backup size report

Post by masonit » Jan 10, 2013 2:13 pm

tsightler wrote: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
VP, Product Management
Posts: 5418
Liked: 2240 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: Backup size report

Post by tsightler » Jan 10, 2013 2:19 pm

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
Service Provider
Posts: 218
Liked: 15 times
Joined: Oct 09, 2012 2:30 pm
Full Name: Magnus
Contact:

Re: Backup size report

Post by masonit » Jan 10, 2013 2:38 pm

tsightler wrote: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
Product Manager
Posts: 16884
Liked: 1433 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Backup size report

Post by veremin » Jan 10, 2013 3:16 pm

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

Hope this helps.
Thanks.

tsightler
VP, Product Management
Posts: 5418
Liked: 2240 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: Backup size report

Post by tsightler » Jan 10, 2013 3:22 pm

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
Service Provider
Posts: 218
Liked: 15 times
Joined: Oct 09, 2012 2:30 pm
Full Name: Magnus
Contact:

Re: Backup size report

Post by masonit » Jan 10, 2013 3:30 pm

v.Eremin wrote: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
Service Provider
Posts: 218
Liked: 15 times
Joined: Oct 09, 2012 2:30 pm
Full Name: Magnus
Contact:

Re: Backup size report

Post by masonit » Jan 10, 2013 3:34 pm

tsightler wrote: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
VP, Product Management
Posts: 5418
Liked: 2240 times
Joined: Jun 05, 2009 12:57 pm
Full Name: Tom Sightler
Contact:

Re: Backup size report

Post by tsightler » Jan 10, 2013 3:51 pm

masonit wrote: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.

Post Reply

Who is online

Users browsing this forum: No registered users and 14 guests