Backup reporting using MS-SQL databases

PowerShell script exchange

Backup reporting using MS-SQL databases

Veeam Logoby clement.cromology » Mon Jun 13, 2016 9:52 am

Hey,

I'm trying to build a custom reporting for backup objects using Veeam databases. Is there a documentation about the way data is organized and what it means ? I'm trying to do a "per-vm, cross jobs" reporting.

First, what's the difference between table [VeeamBackup].[dbo].[Backup.Model.BackupTaskSessions] and [VeeamBackupReporting].[dbo].[C.Backup.Model.BackupTaskSessions] ? The data seems the same in both tables.

Secondly, I'm trying to analyse the status code. As far as I've gone, it seems that :
- 0 is success
- 2 is error
- 3 is canceled

Is there a return code 1 ? Or any other return codes ?

Thanks.
clement.cromology
Novice
 
Posts: 6
Liked: 1 time
Joined: Tue May 31, 2016 11:11 am
Full Name: Clement ODOT

Re: Backup reporting using MS-SQL databases

Veeam Logoby PTide » Mon Jun 13, 2016 10:21 am

Hi,

Is there a documentation about the way data is organized and what it means ?
No, there is no such documentation. Please keep in mind that database schema is a subject to change so it would be better to use either VBR specific PowerSHell cmdlets or Veeam ONE reporting tool. May I ask you what kind of data do you want to collect?

Thanks
PTide
Veeam Software
 
Posts: 3019
Liked: 246 times
Joined: Tue May 19, 2015 1:46 pm

Re: Backup reporting using MS-SQL databases

Veeam Logoby clement.cromology » Mon Jun 13, 2016 11:19 am

I wanted to extract the following data to export it to the central database where all our backup reporting is consolidated :

For each virtual machine backup job by Veeam :
- VM name
- Start time
- End time
- Status (success or fail)
- Duration
- Backup size

I've check the Powershell module but haven't found a cmdlet returning this kind of data without too much complexity.

Using the database, I use the following query :

SELECT
[creation_time]
,[object_name]
,[status]
,[reason]
,[end_time]
,DATEDIFF(minute, [creation_time], [end_time]) as duration_minutes
,([stored_size]/1024/1024) as storedsize_MB
FROM [VeeamBackup].[dbo].[Backup.Model.BackupTaskSessions]
WHERE creation_time BETWEEN dateadd(day, -1, GETDATE()) and GETDATE()
AND (status = 2 OR status = 0)
ORDER BY [creation_time] DESC

Thanks.
clement.cromology
Novice
 
Posts: 6
Liked: 1 time
Joined: Tue May 31, 2016 11:11 am
Full Name: Clement ODOT

Re: Backup reporting using MS-SQL databases

Veeam Logoby PTide » Mon Jun 13, 2016 11:40 am

You can get all that info via powershell, no need to dig through the database - please use search to find examples of how users do that. Also there is a PS-based reporting tool that you might be interested in.

Thanks
PTide
Veeam Software
 
Posts: 3019
Liked: 246 times
Joined: Tue May 19, 2015 1:46 pm


Return to PowerShell



Who is online

Users browsing this forum: No registered users and 4 guests