Disclaimer: This script took from veeam forum
Scenario: Veeam VBR 12.1 + SQL Server. Will not work on postgresql
Guide 1
- Create a folder C:\Scripts\Veeam\Reports
- Install SQL Powershell module on where script going to run or install SQL Management Studio
Guide 2
- Create a file VeeamTapeInventory.sql into C:\Scripts\Veeam
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
Guide 3
- Create a Powershell script called “VeeamTapeInventory.ps1” and put on C:\Scripts\
Change the VeeamSQLServer Name
$VeeamSqlServer = 'VBR121SQL\SQLEXPRESS'
$ScriptDir = 'C:\Scripts\Veeam'
$ReportDir = 'C:\Scripts\Veeam\Reports'
$date = Get-Date -format "yyyyMMdd-HHmmss"
#$uname = "vbr121sql\laiys"
#$pwd = "P@ssw0rd"
$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"
Guide 4
Run the powershell and get the csv output
Sample output:
Guide 5 [Add-on]Another SQL script: VeeamTapeInventory2.sql
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 Barcode_ID,
TBS.name AS Backup_Set,
TB.Name AS Backup_Job,
TH.Name AS Backup_Host,
PathInfo.folderpath AS Folder_Path,
TF.Name AS Tape_File_Name,
CAST(Size / 1073741824.0E AS DECIMAL(10, 2)) AS File_Size_GB,
CAST(Capacity / 1073741824.0E AS DECIMAL(10, 2)) AS Tape_Capacity_GB,
CAST(Remaining / 1073741824.0E AS DECIMAL(10, 2)) AS Tape_Remaining_GB,
Continuation,
TTM.Last_Write_Time,
Expiration_Date,
TTM.Description AS Tape_Description,
TMP.name AS Tape_Media_Pool,
TMP.Description AS Tape_Media_Pool_Description,
CASE TTM.Location_Type
WHEN '0' THEN TL.Name + ' - Tape Drive'
WHEN '1' THEN TL.Name + ' - Slot ' + CAST((Location_Address + 1) AS NVARCHAR(255))
WHEN '2' THEN 'Tape Vault - ' + TMV.Name
ELSE 'Other'
END AS Tape_Physical_Location
FROM
[Tape.tape_mediums] TTM
INNER JOIN [Tape.tape_medium_backup_sets] TTMBS
ON TTMBS.tape_medium_id = TTM.id
INNER JOIN [Tape.backup_sets] TBS
ON TBS.id = TTMBS.backup_set_id
INNER JOIN [Tape.file_versions] TFV
ON TFV.backup_set_id = TBS.id
INNER JOIN [Tape.files] TF
ON TF.id = TFV.file_id
INNER JOIN [Tape.backups] TB
ON TB.id = TBS.backup_id
INNER JOIN [Tape.directories] TD
ON TD.id = TF.directory_id
INNER JOIN [Tape.hosts] TH
ON TH.id = TD.host_id
INNER JOIN PathInfo
ON PathInfo.id = TD.id
INNER JOIN [Tape.media_pools] TMP
ON media_pool_id = TMP.id
LEFT JOIN [Tape.media_in_vaults] TMIV
ON TMIV.media_id = TTM.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
--Filter on barcode id
--WHERE (TTM.barcode = 'JD4747L3')
--Filter on filename written to tape
--WHERE (TF.name like '%2016-04-07%')
--Filter on the tape media vault name
--WHERE (TMV.name LIKE '%2015a%')
ORDER BY Barcode_ID ASC, Backup_Set ASC
Sample output:
Additional resources:
Tape powershell cmdlet: https://helpcenter.veeam.com/docs/backup/powershell/tape_devices.html?ver=120
