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]



    ,FolderPath = CONVERT(NVARCHAR(800), name)

       FROM [dbo].[Tape.directories]

      WHERE Parent_Id IS NULL


     SELECT  TempTD.Id



    ,FolderPath = CONVERT(NVARCHAR(800), cte.FolderPath+'\'

       FROM [dbo].[Tape.directories] TempTD

       JOIN PathInfo cte ON cte.Id = TempTD.Parent_Id




TTM_Name AS Name,


--TH_Name AS Backup_Server,


TF_Name AS File_Name,

TFP_Incompletion AS FileSegmentNumber, 




--TTM_Protected AS IsTapeProtected,

TTM_Media_Time AS Media_Time,


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



(SELECT TFV.file_id AS TFV_FileID,

TFV.backup_set_id AS TFV_BackupSetID, AS TFV_ID,

CAST(TFV.Size / 1073741824.0E AS DECIMAL(10, 2)) AS File_Size_GB,

TF.directory_id AS TF_DirectoryID, AS TF_Name,

TFP.media_sequence_number AS TFP_MediaSequenceNumber, AS TFP_ID,

TFP.file_version_id AS TFP_FileVersionID,

TFP.incompletion AS TFP_Incompletion, AS TH_Name,

PathInfo.folderpath AS Folder_Path

     FROM [Tape.file_versions] AS TFV

LEFT JOIN [dbo].[Tape.file_parts] TFP  

ON = TFP.file_version_id

LEFT JOIN [Tape.files] TF 

ON TFV.file_id =

LEFT JOIN [Tape.directories] TD 

ON TF.directory_id =

LEFT JOIN [Tape.hosts] TH 

ON TD.host_id =


ON =

) AS FileParts



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, AS TBS_ID, AS TBS_Name,

TBS.backup_id AS TBS_BackupID,

TBS.expiration_date AS TBS_ExpirationDate, AS TB_Name,

TMV.description AS TMV_Description, 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, 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, AS TMP_Name,

TMP.Description AS TMP_Description  

FROM [Tape.tape_mediums] AS TTM

LEFT JOIN [dbo].[Tape.tape_medium_backup_sets] TTMBS  

ON = TTMBS.tape_medium_id

LEFT JOIN  [dbo].[Tape.backup_sets] TBS 

ON TTMBS.backup_set_id =

LEFT JOIN [Tape.backups] TB 

ON TBS.backup_id =

LEFT JOIN [Tape.media_in_vaults] TMIV

ON = TMIV.media_id

LEFT JOIN [Tape.media_vaults] TMV

ON TMIV.vault_id =

LEFT JOIN [Tape.libraries] TL

ON TTM.location_library_id =

INNER JOIN [Tape.media_pools] TMP

ON media_pool_id =

) AS BackupSets

ON BackupSets.TBS_ID = FileParts.TFV_BackupSetID

AND BackupSets.TTM_MediaSequenceNumber = FileParts.TFP_MediaSequenceNumber





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
,FolderPath = CONVERT(NVARCHAR(800), name)
   FROM [dbo].[Tape.directories]
,FolderPath = CONVERT(NVARCHAR(800), cte.FolderPath+'\'
   FROM [dbo].[Tape.directories] TempTD
   JOIN PathInfo cte ON cte.Id = TempTD.Parent_Id

TTM.barcode AS Barcode_ID, 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,
TTM.Description AS Tape_Description, 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
[Tape.tape_mediums] TTM
INNER JOIN [Tape.tape_medium_backup_sets] TTMBS
ON TTMBS.tape_medium_id =
INNER JOIN [Tape.backup_sets] TBS
ON = TTMBS.backup_set_id
INNER JOIN [Tape.file_versions] TFV
ON TFV.backup_set_id =
INNER JOIN [Tape.files] TF
ON = TFV.file_id
INNER JOIN [Tape.backups] TB
ON = TBS.backup_id
INNER JOIN [Tape.directories] TD
ON = TF.directory_id
INNER JOIN [Tape.hosts] TH
ON = TD.host_id
ON =
INNER JOIN [Tape.media_pools] TMP
ON media_pool_id =
LEFT JOIN [Tape.media_in_vaults] TMIV
ON TMIV.media_id =
LEFT JOIN [Tape.media_vaults] TMV
ON TMIV.vault_id =
LEFT JOIN [Tape.libraries] TL
ON TTM.location_library_id =

--Filter on barcode id
--WHERE (TTM.barcode = 'JD4747L3')

--Filter on filename written to tape
--WHERE ( like '%2016-04-07%')

--Filter on the tape media vault name
--WHERE ( LIKE '%2015a%')

ORDER BY Barcode_ID ASC, Backup_Set ASC

Sample output:

Additional resources:
Tape powershell cmdlet:

Wednesday, February 7, 2024

Performance Benchmark Amd Ryzen 2400G vs 5600G

My Promox server hit to performance issue.

Therefore I have decided to change my old CPU AMD Ryzen 2400G (4 cores, 8 threads) running on Promox to AMD Ryzen 5600G (6 cores,12 threads). 

Before i made a CPU swap, I did a benchmark & below is the result.

Tool: Sysbench

sudo apt install sysbench

AMD Ryzen 2400G

sysbench cpu --threads=8 run

Result: Refer to event per seconds. Total 7804.45

AMD Ryzen 5600G

sysbench cpu --threads=12 run

Result: Refer to event per second. Total 33266.27

The highest is the number of events per second, better is the CPU performance. 

The winner was AMD Ryzen 5600G (as expected) by 4x

Saturday, February 3, 2024

Unable to Validate License When Using Veeam AI

On Veeam latest version 12.1, you can use Veeam AI similar to chatgpt to inquiry on how to use Veeam.

However, if you encountered an error message " Unable to validate license", you may wonder why this incident occurred?


1. Don't use Community Edition or NFR License file. You must have a paid license and support contract or Evaluation license to use Veeam AI Online Assistant

Friday, February 2, 2024

Backup Promox VM and Container Using Promox Backup Server

Yesterday I've talked about backup natively from Promox VE (this link). Let's look into Promox Backup Server. Promox Backup Server is a client-server solution that allows you to backup virtual machines and containers in Promox VE. 

To begin,

1. Download the installer from link

2. Deploy on Promox VE. Once perform the initial configuration, you can access using web browser url https://<ip>: 8007

From here, you can start backup to disk or to tape.

3. Before we begin, add Datastore disk

Summary datastore (backupdisk1)

Next, remember to add Storage from Promox VE.

4. Go to Datacenter > Storage > Add > Promox Backup Server

4. Define Backup retention policy

5. Create Backup Policy 

Go to Datacenter > Backup > Add > Add policy setting

a) Storage : PromoxBackup

b) Schedule: your schedule

c) Select VM & Container

d) Define Retention

e) Mode: Snapshot

Summary of backup policies

Select Policy and click Run Now

Thursday, February 1, 2024

Backup Promox Virtual Machines and Container

In today's post, we are going to explore how to protect Promox virtual machines and Container

Natively Promox is included with a backup option.

To begin with:

1. Configure storage where you want to put the backup data

Go to Datacenter > Storage > Add

In my lab, i will select NFS as my target

2. Once you have defined the storage, let's move into creating backup policies

Go to Datacenter > Backup > Click Add

a) Select the target to NFS that you created earlier.

b) Tick VM and LXC that you would like to backup

c) Define mode: Snapshot

d) Compression: ZSTD (fast and good)

e) Define backup retention to keep

Once done, all policies will listed on the screen

Click Run Now to execute the backup. You can view the progress of the backup status by double click the task.