Get tape content via powershell

[AndrewAdvnetsol]

Hello

Is there a solution or a script, to find out, what backup is written to what tape?
If i open the Tape infra view, select some offline or online tape from the view and then use the Files view, it shows what backup and from what Repo it was written, but how can i get it for all the tapes?

@JTT

I use the following scripts to pull that info. You can try them and see if they work for you. Hopefully they help.

This is a script I named Tape Name Script:

Code: Select all

$ReportDir = 'C:\Scripts\Veeam\Reports'
$date = Get-Date -format "yyyyMMdd-HHmmss"
$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 | export-csv $ReportDir\tape_$Date.csv
Write-Host "Tape inventory has been exported to: "
Write-Host "$ReportDir\tape_$Date.csv"

This is a script I named Tape Inventory Script: (note you will have to modify this with the hostname and SQL Instance Name of your system)

Code: Select all

$VeeamSqlServer = 'hostname\SQLInstanceName' 
$ScriptDir = 'C:\Scripts\Veeam'
$ReportDir = 'C:\Scripts\Veeam\Reports'
#Import-module "sqlps"
$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"

You will also need this info saved in a file called VeeamTapeInventory.sql. (I just pasted it into a txt file then renamed file in file explorer to change the .txt to .sql)

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_Name AS Name,

--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,
TTM_Media_Time AS Media_Time,
/*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.name as TTM_name,
TTM.media_time as TTM_media_time,
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 NOT (NOT (BackupSets.TBS_ID IS NULL) AND (TF_Name IS NULL))

ORDER BY TTM_name ASC

[roland]

Hi there,

I'm wondering if the actual Veeam PowerShell module is capable of providing us with tape content directly without using "unsupported features".
My main goal is to list every files (vbk, vib, and others) stored on a specif tape even if this file is made with "file to tape job " and not a "backup to tape job"....
Thanks

[david.domask]

Hi roland,

Right now you will need to use the unsupported workarounds found in this topic; the request for official cmdlets/api endpoints for this is known, but for now use the workarounds in this topic. Thanks!