Script to list the VMs on tapes

[ithark]

Dear Team,
Good Day! Hope you all are doing well
We currently have backup to tapes in our environment. The thing is, we can retrieve the contents stored on the tape but they would only display the vbks.( I do understand that this is the expected output). What we really need is a way to find out the list of VMs which are being backed up to tape. I know Veeam one has a report but I am looking for a way to accomplish this PowerShell.

.

[oleg.feoktistov]

Hi Karthik,

Can you, please, elaborate? Do you need a way to retrieve the list of VMs being backed up every period of time chosen or just the list of VMs currently residing on chosen tape mediums?

Thanks,
Oleg

[ithark]

Hi @oleg,
Thanks for your response.
Every day, we do backups to tape. On Each monday, we export the tapes used for the last 7 days and get them to the safe.
According to our policy, we need to know which VMs were backed up to tape for the past 7 days when the tape is exported on Monday.
Thanks to another Veeam forum, I managed to export the contents of the tape and presented it to our internal auditors but since it only contained the vbk, they were not happy. They said they need the VM names which are backed up to tape.
Hope this explains my requirement.
Thanks in advance!
Regards,
Karthik

[oleg.feoktistov]

Hi Karthik,

Sorry for being away. Been busy with tech tasks. Can you, please, share the script you used to extract information on vbk storages residing on tapes for your report?

Thanks,
Oleg

[ithark]

Hi @Oleg
No worries.
Here is the script i use to get the tape contents

Code: Select all

$VeeamSqlServer = 'XXXX-XXXX\VEEAMSQL2016'
$ScriptDir = 'C:\Scripts\Veeam'
$ReportDir = 'C:\Scripts\Veeam\Reports'
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
$date = Get-Date -format "yyyyMMdd-HHmmss"
$TapeInventory = Invoke-Sqlcmd -InputFile $ScriptDir\VeeamTapeInventory.sql -ServerInstance "$VeeamSQLServer"
$TapeInventory | Export-Csv $ReportDir\VeeamTapeInventory_$Date.csv -NoTypeInformation
Write-Host "Tape inventory has been exported to: "
Write-Host "$ReportDir\VeeamTapeInventory_$Date.csv"

And the following is the SQL query

Code: Select all

USE VeeamBackup;

    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
TTM_Barcode AS BarcodeID,
--TH_Name AS Backup_Server,
Folder_Path,
TF_Name AS File_Name,
--TFP_Incompletion AS FileSegmentNumber, 
File_Size_GB,
--Tape_Capacity_GB,
--Tape_Remaining_GB,
--TTM_Protected AS IsTapeProtected,
CASE WHEN 
Tape_Physical_Location IS NULL THEN 'Offline'
ELSE Tape_Physical_Location
END AS Tape_Physical_Location,
TB_Name AS Tape_Backup_Job,
TBS_Name AS Tape_Backup_Set,
TBS_ExpirationDate AS Tape_Backup_Set_Expiration,
TTM_LastWriteTime AS Last_Write_Time,
--TTM_Description AS Tape_Description,
TMP_Name AS Tape_Media_Pool
--TMP_Description AS Tape_Media_Pool_Description

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

WHERE Tape_Physical_Location in ('Tape Vault - Vault - Bank') and TTM_LastWriteTime >= GETDATE() - 7

--WHERE NOT (NOT (BackupSets.TBS_ID IS NULL) AND (TF_Name IS NULL)) 

ORDER BY TTM_Barcode ASC

[oleg.feoktistov]

Hi Karthik,

Since you already parse data through SQL, you can easily derive VM names from storage file paths. SQL has a function for that.
Here is a simple example:

Code: Select all

SELECT TOP (100) [id]
      ,[directory_id]
      ,[name] AS [path]
      ,[is_deleted]
	  ,PARSENAME(name, 3) AS [name]
  FROM [VeeamBackup].[dbo].[Tape.files]

Please also note that's just a workaround and direct queries to Veeam DB are not supported.

Thanks,
Oleg

[ithark]

Hi @Olag,
Thanks for spending your time to find me this query. Really appreciated!
Unfortunately, the above query doesn't seem to work for me.
My DBA is also not available at the moment. Nevertheless, I am trying to achieve this only via PowerShell scripting.
The reason is, even if the script is broken or something, I don't want to be dependent on my DBA to fix it for me, additional to your point that direct queries to Veeam DB are not supported
I just want the following for the Auditors,
For the past 7 days, need data for the following table.
Tape Label | VM Name | Restore Point
Any help would be greatly appreciated!
Many thanks.
Regards,
Karthik

[oleg.feoktistov]

Hi Karthik,

So far I couldn't find any method, which would correlate VM Names with Tape Barcodes of mediums they were written to.
The best I came up with is VM Name and CreationTime. Theoretically, we can correlate restore point creation time info with the same property
of task session and find out which tape mediums were used in a particular task session. But that info wouldn't be so precise, I'm afraid.
I asked internally if there is any other approach to it besides querying the DB directly. Let's see what we can find.

Thanks,
Oleg

[ithark]

Hi Oleg,

Thanks for your response and taking time to look into my query.
Will wait for further update from you.

[oleg.feoktistov]

Hi Karthik,

As per dev team comment, there is no direct correlation between tape task sessions and tape restore points.
So, I'm afraid there is no easy way to get this info through Powershell other than with the approach I described above.
Otherwise, SQL queries, which we discussed. We don't support those, but can help through forums on best effort basis.

Thanks,
Oleg

[ithark]

Hi @Oleg,
Thank you. If there is no other way, then it is fine. Thanks for trying though.
Regards,
Karthik

[oleg.feoktistov]

For anyone looking for restore points and barcodes correlations. It appears I have found another workaround (still unsupported) through DBManager:

Code: Select all

$backups = Get-VBRTapeBackup | where {$_.VMCount -ne 0}
$rps = Get-VBRRestorePoint -Backup $backups
foreach ($rp in $rps) {
  $dbOib = [Veeam.Backup.DBManager.CDBManager]::Instance.TapeOibs.GetMediaTapeNamesByOibs($rp.Id)
  $barcode = $dbOib.Values
  $rp | select Name, @{n='TapeMedium';e={$barcode}}
}

Thanks,
Oleg

[Clumsy Penguin]

Hello,

I am looking for a way to verify that my tape backups adhere to my backup policy. So that there are sufficient Weekly, Monthly, Yearly etc. backups. Additionally, I would like to have an overview of the tapes that are used for the backups.

If possible, the information should be on the per VM level. Job level is ok.

My preference would be a machine readable output like

Code: Select all

{
	"tape_backups": [{
			"vm_name": "fancy_vm_name",
			"daily": [],
			"weekly": [
				[
					"tape0",
					"tape1"
				],
				[
					"tape2"
				]
			]
		},
		{
			"vm_name": "another_fancy_vm_name",
			"daily": [
				[
					"tape4"
				]
			],
			"weekly": []
		}
	]
}

I don't care if it is XML, JSON, CSV, ...

Above example: two VMs:

• fancy_vm_name with 0 daily, and 2 weeklies with three tapes.
• another_fancy_vm_name with 1 daily, and 0 weeklies.

My second choice would be something like "fancy_vm_name: 0 daily, 2 weeklies" in a CVS, Excel, ...

Does Veeam support this?

[Mildur]

Hi Clumsy

I moved your question to this topic.
Please see the examples in previous answers. It should help to give you an idea on how to build your own PowerShell script to export your list.

You may also check out our Veeam One Reports:
https://helpcenter.veeam.com/docs/one/r ... ml?ver=110

Best,
Fabian

[albertwt]

For anyone looking for restore points and barcodes correlations. It appears I have found another workaround (still unsupported) through DBManager:

Code: Select all

$backups = Get-VBRTapeBackup | where {$_.VMCount -ne 0}
$rps = Get-VBRRestorePoint -Backup $backups
foreach ($rp in $rps) {
  $dbOib = [Veeam.Backup.DBManager.CDBManager]::Instance.TapeOibs.GetMediaTapeNamesByOibs($rp.Id)
  $barcode = $dbOib.Values
  $rp | select Name, @{n='TapeMedium';e={$barcode}}
}

Thanks,
Oleg

Hi Oleg & Mildur,

Will there be an updated feature for this in the future, or can we put this into a Feature request?

[oleg.feoktistov]

Hi,

No short-term plans to implement it, but I noted it as a feature request.

Best regards,
Oleg

[albertwt]

That's great, thank you Oleg for your assistance and update on this matter.

[AndrewAdvnetsol]

Hi @Oleg
No worries.
Here is the script i use to get the tape contents

Code: Select all

$VeeamSqlServer = 'XXXX-XXXX\VEEAMSQL2016'
$ScriptDir = 'C:\Scripts\Veeam'
$ReportDir = 'C:\Scripts\Veeam\Reports'
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
$date = Get-Date -format "yyyyMMdd-HHmmss"
$TapeInventory = Invoke-Sqlcmd -InputFile $ScriptDir\VeeamTapeInventory.sql -ServerInstance "$VeeamSQLServer"
$TapeInventory | Export-Csv $ReportDir\VeeamTapeInventory_$Date.csv -NoTypeInformation
Write-Host "Tape inventory has been exported to: "
Write-Host "$ReportDir\VeeamTapeInventory_$Date.csv"

And the following is the SQL query

Code: Select all

USE VeeamBackup;

    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
TTM_Barcode AS BarcodeID,
--TH_Name AS Backup_Server,
Folder_Path,
TF_Name AS File_Name,
--TFP_Incompletion AS FileSegmentNumber, 
File_Size_GB,
--Tape_Capacity_GB,
--Tape_Remaining_GB,
--TTM_Protected AS IsTapeProtected,
CASE WHEN 
Tape_Physical_Location IS NULL THEN 'Offline'
ELSE Tape_Physical_Location
END AS Tape_Physical_Location,
TB_Name AS Tape_Backup_Job,
TBS_Name AS Tape_Backup_Set,
TBS_ExpirationDate AS Tape_Backup_Set_Expiration,
TTM_LastWriteTime AS Last_Write_Time,
--TTM_Description AS Tape_Description,
TMP_Name AS Tape_Media_Pool
--TMP_Description AS Tape_Media_Pool_Description

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

WHERE Tape_Physical_Location in ('Tape Vault - Vault - Bank') and TTM_LastWriteTime >= GETDATE() - 7

--WHERE NOT (NOT (BackupSets.TBS_ID IS NULL) AND (TF_Name IS NULL)) 

ORDER BY TTM_Barcode ASC

I have tried this script, but for some reason it isn't pulling any data. My excel file is showing 0KB. But I have run a very similar script found here powershell-f26/get-tape-content-via-pow ... 42-60.html. But I don't use barcodes on my tapes, not sure that my model has that option, so I would like to pull tape name and media set names instead of barcode info. Is that possible? Powershell scripting is a bit over my head so any help is greatly appreciated.

I can run this script and get VM Name, Creation Time, Tape Name to display. But it doesn't list the .VBK files that are on the tapes or the tape media set info.

$backups = get-vbrtapebackup | where {$_.VMCount -ne 0}
$rps = Get-VBRRestorePoint -Backup $backups
$results = foreach ($rp in $rps) {
$dbOib = [Veeam.Backup.DBManager.CDBManager]::Instance.TapeOibs.GetMediaTapeNamesByOibs($rp.Id)
$barcode = $dbOib.Values
$rp | select Name, creationtime, @{n='TapeMedium';e={$barcode}}
}
$results