Search This Blog

Tuesday, April 16, 2024

Powershell to Inventory Veeam Tape

 Disclaimer: This script took from veeam forum

Scenario: Veeam VBR 12.1 + SQL Server. Will not work on postgresql

Guide 1

  1. Create a folder C:\Scripts\Veeam\Reports
  2. Install SQL Powershell module on where script going to run or install SQL Management Studio

Guide 2
  1. 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

  1. 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