-
- Service Provider
- Posts: 327
- Liked: 23 times
- Joined: Oct 09, 2012 2:30 pm
- Full Name: Maso
- Contact:
Backup size report
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
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
-
- Chief Product Officer
- Posts: 31746
- Liked: 7250 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: Backup size report
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!
-
- Service Provider
- Posts: 327
- Liked: 23 times
- Joined: Oct 09, 2012 2:30 pm
- Full Name: Maso
- Contact:
Re: Backup size report
Sounds great! Please point me in the right direction. What tables and columns should i query?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!
\Masonit
-
- Veeam Software
- Posts: 21133
- Liked: 2140 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: Backup size report
Magnus, please review this topic, should help. Thanks.
-
- Service Provider
- Posts: 327
- Liked: 23 times
- Joined: Oct 09, 2012 2:30 pm
- Full Name: Maso
- Contact:
Re: Backup size report
I am running 6.5 and I can't find the table "reportsessionsview". Do you know what table that has replaced it?foggy wrote:Magnus, please review this topic, should help. Thanks.
\Masonit
-
- Veeam Software
- Posts: 21133
- Liked: 2140 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: Backup size report
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.
-
- VP, Product Management
- Posts: 6029
- Liked: 2856 times
- Joined: Jun 05, 2009 12:57 pm
- Full Name: Tom Sightler
- Contact:
Re: Backup size report
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:
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)
-
- VP, Product Management
- Posts: 6029
- Liked: 2856 times
- Joined: Jun 05, 2009 12:57 pm
- Full Name: Tom Sightler
- Contact:
Re: Backup size report
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:
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.
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
-
- Service Provider
- Posts: 327
- Liked: 23 times
- Joined: Oct 09, 2012 2:30 pm
- Full Name: Maso
- Contact:
Re: Backup size report
Thanks!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:
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.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
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
-
- VP, Product Management
- Posts: 6029
- Liked: 2856 times
- Joined: Jun 05, 2009 12:57 pm
- Full Name: Tom Sightler
- Contact:
Re: Backup size report
Looks like line wrap may still be messing up something. What happens if you just run the following:
The rest of the code is basically just for formatting.
Code: Select all
(Get-VBRBackup -Name "<Backup_Job_Name>").GetStorages()
-
- Service Provider
- Posts: 327
- Liked: 23 times
- Joined: Oct 09, 2012 2:30 pm
- Full Name: Maso
- Contact:
Re: Backup size report
That works, I get the following columns:tsightler wrote:Looks like line wrap may still be messing up something. What happens if you just run the following:
The rest of the code is basically just for formatting.Code: Select all
(Get-VBRBackup -Name "<Backup_Job_Name>").GetStorages()
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
-
- Product Manager
- Posts: 20360
- Liked: 2287 times
- Joined: Oct 26, 2012 3:28 pm
- Full Name: Vladimir Eremin
- Contact:
Re: Backup size report
It seems like you haven’t deleted “`” sign while copying the script body. Without it everything should work fine.
Hope this helps.
Thanks.
Hope this helps.
Thanks.
-
- VP, Product Management
- Posts: 6029
- Liked: 2856 times
- Joined: Jun 05, 2009 12:57 pm
- Full Name: Tom Sightler
- Contact:
Re: Backup size report
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:
You will get a VBRBackup object, if you instead pipe this object to "get-member"
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:
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:
Below is a copy without the "`" marks as perhaps some browsers are being confused by this and not properly copying the code.
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.
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>"
Code: Select all
Get-VBRBackup -Name "<backup_job>" | gm
Now that we know this we can explain what is happening when running the following:
Code: Select all
(Get-VBRBackup -Name "<backup_job").GetStorages()
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
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
-
- Service Provider
- Posts: 327
- Liked: 23 times
- Joined: Oct 09, 2012 2:30 pm
- Full Name: Maso
- Contact:
Re: Backup size report
Yes it did. Thanks!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.
Why can't I use wildcard in job name?
\Masonit
-
- Service Provider
- Posts: 327
- Liked: 23 times
- Joined: Oct 09, 2012 2:30 pm
- Full Name: Maso
- Contact:
Re: Backup size report
I will, thank you for great explanation!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.
\Masonit
-
- VP, Product Management
- Posts: 6029
- Liked: 2856 times
- Joined: Jun 05, 2009 12:57 pm
- Full Name: Tom Sightler
- Contact:
Re: Backup size report
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:masonit wrote:Why can't I use wildcard in job name?
Code: Select all
Sort Name,Date -Descending
Who is online
Users browsing this forum: No registered users and 2 guests