Daily Tape Out Report

[nunciate]

I have Veeam One but I don't see the report I need so I wonder if others are doing this and how.
We back up to disk each night and then those are sent to tape immediately after using GFS Tape Jobs.

I need a daily report that tells me all of the VM backups that made it to tape and what retention.
We do Daily Incremental, Weekly Full, and once a month we do Monthly full to tape.

I need to know that all of my backups make it to tape and I don't see an easy way to report this every day.
At minimum, I need to know that all of my Full backups each week make it to either a Weekly or Monthly retention tape

Any Ideas?

[Dima P.]

Hello Alan,

Veeam B&R provides basic reporting options, so it creates email reports with used tapes/written data as soon as job finishes and does not have an option to generate a daily email summary. As for Veeam One reports, I've asked fellow PMs responsible for this product to join the discussion. Thank you!

[jorgedlcruz]

Hello Alan,
What information can you see on the Backups on Tape, please? https://helpcenter.veeam.com/docs/one/r ... ml?ver=120 That should show you the Job and workloads that go on each Job.

I am trying to find more information about the data we have, as perhaps we need to extend Workload Protection History with Tape data.

Please give us some time.

[RomanK]

Hello Alan,

I moved this topic to the Veeam ONE forum.

Please take a look at Tape GFS Backup Files it also contains a drill-down page when you click on the restore point date.

As Jorge told Workload Protection History would also help to filter tape jobs and Restore Point Removal Date for GFS.

Thanks

[nunciate]

I never was able to get what I wanted from Veeam One Reports so I tried a different route with this. Please feel free to move this to a different forum if it makes sense. Not sure if it is appropriate for this forum anymore.

On my Windows File Repository, I created the following PowerShell script to collect all .vib and .vbk files on the server. So all of my disk backup file names.
Using the script I bulk-inserted the resulting CSV file into a table in a DB I created. The output is just 1 column with no header and a simple list of file names found.

Using this method my SQL Script returned over 4k files that it didn't find on tape. That seems very high but I now wonder if this method is accurate.
I use GFS tape jobs so I wonder if the .vib and .vbk file it places on tapes are the same names as the actual files on the file system? Maybe it is creating new names when it places the files on tapes?
If so this method won't work so I'd need to figure out a way to know what file on tape is the same as the file on the server.

For Windows Repository Server:

Code: Select all

## Input Variables
$csvPath = "\\ServerName\ShareName\Filename.csv"  ## I had to create a share and granted access to the SQL Server service account.
$serverName = "SQLSERVERNAME"
$databaseName = "DB_NAME" ## Mine was named DBA
$tableSchema = "dbo"
$tableName = "VeeamFiles"  ## The table you created with a single NVARCHAR(MAX) Column

## Get All VIB & VBK Files into  CSV File Excludes other file types
## I have multiple lines as my backup server has 5 large drives for repositories
$ExcludeExtensions = ".vbm", ".vsb"
Get-ChildItem M:\Backups -Recurse | Where-Object {(-not $_.PSIsContainer) -and ($ExcludeExtensions -notcontains $_.Extension)} Where-Object {$_.LastWriteTime -gt (Get-Date).AddDays(-7)} | select -ExpandProperty Name | Out-File $csvPath
Get-ChildItem N:\Backups -Recurse | Where-Object {(-not $_.PSIsContainer) -and ($ExcludeExtensions -notcontains $_.Extension)} Where-Object {$_.LastWriteTime -gt (Get-Date).AddDays(-7)} | select -ExpandProperty Name | Out-File $csvPath -Append
Get-ChildItem O:\Backups -Recurse | Where-Object {(-not $_.PSIsContainer) -and ($ExcludeExtensions -notcontains $_.Extension)} Where-Object {$_.LastWriteTime -gt (Get-Date).AddDays(-7)} | select -ExpandProperty Name | Out-File $csvPath -Append
Get-ChildItem P:\Backups -Recurse | Where-Object {(-not $_.PSIsContainer) -and ($ExcludeExtensions -notcontains $_.Extension)} Where-Object {$_.LastWriteTime -gt (Get-Date).AddDays(-7)} | select -ExpandProperty Name | Out-File $csvPath -Append
Get-ChildItem Q:\Backups -Recurse | Where-Object {(-not $_.PSIsContainer) -and ($ExcludeExtensions -notcontains $_.Extension)} Where-Object {$_.LastWriteTime -gt (Get-Date).AddDays(-7)} | select -ExpandProperty Name | Out-File $csvPath -Append

## Truncate Table
Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query "TRUNCATE TABLE $tableSchema.$tableName"

## Import CSV into SQL
Invoke-Sqlcmd -Database $databaseName -ServerInstance $serverName -Query "BULK INSERT [dbo].[VeeamFiles] FROM '$($csvPath)' WITH (FIRSTROW = 1, DATAFILETYPE='widechar', FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', TABLOCK, KEEPNULLS)"

The SQL Query: Returns only files that were not found on tapes.

Code: Select all

USE YOUR_VEEAM_DATABASE;

    WITH PathInfo AS
    (
     SELECT  [Id]
    ,Parent_Id
    ,Name
    ,FolderPath = CONVERT(NVARCHAR(800), name)
       FROM [dbo].[Tape.directories]
      WHERE Parent_Id IS NULL
      UNION ALL
     SELECT  TempTD.Id
    ,TempTD.Parent_Id
    ,TempTD.name
    ,FolderPath = CONVERT(NVARCHAR(800), cte.FolderPath+'\'+TempTD.name)
       FROM [dbo].[Tape.directories] TempTD
       JOIN PathInfo cte ON cte.Id = TempTD.Parent_Id
    )

SELECT
-- TF_Name AS File_Name,
--TB_Name AS Tape_Backup_Job,
--TBS_Name AS Tape_Backup_Set,
--TTM_Barcode AS BarcodeID
VF.VeeamFiles

FROM
(SELECT TFV.file_id AS TFV_FileID,
TFV.backup_set_id AS TFV_BackupSetID,
TFV.id AS TFV_ID,
CAST(TFV.Size / 1073741824.0E AS DECIMAL(10, 2)) AS File_Size_GB,
TF.directory_id AS TF_DirectoryID,
TF.name AS TF_Name,
TFP.media_sequence_number AS TFP_MediaSequenceNumber,
TFP.id AS TFP_ID,
TFP.file_version_id AS TFP_FileVersionID,
TFP.incompletion AS TFP_Incompletion,
TH.name AS TH_Name,
PathInfo.folderpath AS Folder_Path
     FROM [Tape.file_versions] AS TFV
LEFT JOIN [dbo].[Tape.file_parts] TFP  
ON TFV.id = TFP.file_version_id
LEFT JOIN [Tape.files] TF 
ON TFV.file_id = TF.id
LEFT JOIN [Tape.directories] TD 
ON TF.directory_id = TD.id
LEFT JOIN [Tape.hosts] TH 
ON TD.host_id = TH.id
INNER JOIN PathInfo
ON PathInfo.id = TD.id
) AS FileParts
  RIGHT JOIN 
(SELECT TTM.id AS TTM_ID,
TTM.barcode as TTM_Barcode,
TTM.media_sequence_number AS TTM_MediaSequenceNumber,
TTM.location_address AS TTM_LocationAddress,
TTM.Last_Write_Time AS TTM_LastWriteTime,
TTM.Description AS TTM_Description,
CASE TTM.Protected
WHEN '0' THEN 'No'
WHEN '1' THEN 'Yes'
ELSE 'Other'
END AS TTM_Protected,
TTMBS.tape_medium_id AS TTMBS_TapeMediumID,
TTMBS.backup_set_id AS TTMBS_BackupSetID,
TBS.id AS TBS_ID,
TBS.name AS TBS_Name,
TBS.backup_id AS TBS_BackupID,
TBS.expiration_date AS TBS_ExpirationDate,
TB.name AS TB_Name,
TMV.description AS TMV_Description,
TMV.name AS TMV_Name,
CAST(TTM.Capacity / 1073741824.0E AS DECIMAL(10, 2)) AS Tape_Capacity_GB,
CAST(TTM.Remaining / 1073741824.0E AS DECIMAL(10, 2)) AS Tape_Remaining_GB,
TL.Name AS TL_Name,
TL.id AS TL_ID,
TL.tape_server_id AS TL_TapeServerID,
TTM.Location_type AS TTM_LocationType,
CASE TTM.Location_Type
WHEN '0' THEN TL.Name + ' - Tape Drive'
WHEN '1' THEN TL.Name + ' - Slot ' + CAST((TTM.Location_Address + 1) AS NVARCHAR(255))
WHEN '2' THEN 'Tape Vault - ' + TMV.Name
ELSE 'Other'
END AS Tape_Physical_Location,
TMP.name AS TMP_Name,
TMP.Description AS TMP_Description  
FROM [Tape.tape_mediums] AS TTM
LEFT JOIN [dbo].[Tape.tape_medium_backup_sets] TTMBS  
ON TTM.id = TTMBS.tape_medium_id
LEFT JOIN  [dbo].[Tape.backup_sets] TBS 
ON TTMBS.backup_set_id = TBS.id
LEFT JOIN [Tape.backups] TB 
ON TBS.backup_id = TB.id
LEFT JOIN [Tape.media_in_vaults] TMIV
ON TTM.id = TMIV.media_id
LEFT JOIN [Tape.media_vaults] TMV
ON TMIV.vault_id = TMV.id
LEFT JOIN [Tape.libraries] TL
ON TTM.location_library_id = TL.id
INNER JOIN [Tape.media_pools] TMP
ON media_pool_id = TMP.id 
) AS BackupSets
ON BackupSets.TBS_ID = FileParts.TFV_BackupSetID
AND BackupSets.TTM_MediaSequenceNumber = FileParts.TFP_MediaSequenceNumber
RIGHT OUTER JOIN [DBA].[dbo].[VeeamFiles] VF ON FileParts.TF_Name = VF.VeeamFiles COLLATE Latin1_General_BIN
WHERE FileParts.TF_Name IS NULL

[oleg.feoktistov]

Hi Alan,

I'm afraid reporting on that level, especially with the equivalent of the SQL query you shared, is not currently possible in powershell.
I'll note it as a feature request, but it might be faster to support this in Veeam ONE.

Best regards,
Oleg

[jorgedlcruz]

Thank you for the detailed queries, and the needed output.

We are always in the search of how to improve Reports, and visibility in general. So this is truly appreciated.

Thank you