Get tape content via powershell

[veremin]

No, currently there are no substitutions for the mentioned cmdlet. Do you have Veeam ONE deployed, by the way? Thanks.

[spgeblg]

Hello Guys,

Is there any command I can get the objects stored on a tape medium ?

Thank you.

[veremin]

Veeam ONE with its predefined report might be exactly what you're looking for. Thanks.

[McClane]

Buying the expensive Veeam One just for the purpose of knowing what's on your backup tapes is just wrong.
If I hadn't B&R already I would buy another backup product. What might be the case if Veeam keeps raising the price tag with every other major release which has always "The next big thing" that I never use.

[veremin]

Buying the expensive Veeam One just for the purpose of knowing what's on your backup tapes is just wrong.

Actually, the said product has much more than just tape backup advanced reporting. Anyway, I was just trying to propose the easiest option in case you have the given solution.

Is there any command I can get the objects stored on a tape medium ?

You can try to modify the script provided in this thread and see whether it meets your expectations. Below, kindly, find a rough example:

Code: Select all

Asnp VeeamPSSnapin

$table = New-Object system.Data.DataTable “Tape content”
$col1 = New-Object system.Data.DataColumn FilePath,([string])
$col2 = New-Object system.Data.DataColumn FileName,([string])
$col3 = New-Object system.Data.DataColumn Barcode,([string])

$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)

Foreach ($TapeCatalog in Find-VBRTapeCatalog)
    {
    $row = $table.NewRow()         
    $row.FilePath = $TapeCatalog.Parent.Path.Parent.Link
    $row.FileName = $TapeCatalog.name
    $row.Barcode = $TapeCatalog.Versions.parts.GetTapeMedium().barcode
    $table.Rows.Add($row)
    }

$table

Thanks.

[McClane]

Actually, the said product has much more than just tape backup advanced reporting. Anyway, I was just trying to propose the easiest option in case you have the given solution.

Thanks.

That I know. But many companies already have products for monitoring so I could never justify the expenses for another product with the same functionalities only to get what is on my tapes. I know that all software developers want everyone to just use their products, but that is an unrealistic appeal. The per head licence costs for all products we use are getting higher and higher every year with literally no additional use. Even worse, even without changing the hardware you have to pay more because some companies will charge you per core nowadays from one version to the next. It gets harder every year to calculate the financial risks. Maybe next year Microsoft will charge for every bit you send over the virtual network cards, who knows.

[bevair]

"Buying the expensive Veeam One just for the purpose of knowing what's on your backup tapes is just wrong."
Actually, the said product has much more than just tape backup advanced reporting. Anyway, I was just trying to propose the easiest option in case you have the given solution.

The fundamental issue is that tape media information cmdlets have been deprecated without an amiable replacement. The fact that you keep pushing Veeam One for information that had always been accessible, but now has been officially taken away, is a bitter pill to swallow.

[veremin]

The fundamental issue is that tape media information cmdlets have been deprecated without an amiable replacement.

Which cmdlet are you talking about? The modified version of my original script still makes use of Find-VBRTapeCatalog cmdlet.

The fact that you keep pushing Veeam One for information that had always been accessible, but now has been officially taken away, is a bitter pill to swallow.

Again, I'm not pushing Veeam ONE. If I were, I would not have provided an original script in the first place.

It's just easier to check at the beginning whether a customer happens to have solution that have all of the said issues addressed, before writing a script (which might be a time-consuming process).

Thanks.

[bevair]

The modified version of my original script still makes use of Find-VBRTapeCatalog cmdlet.

That cmdlet is deprecated, which means that the example script you graciously provided, won't be supported past B&R 8.0

I'm seeking, in earnest, a legitimate means to create a custom report via powershell which charts a full backup map of all VMs, their location in repository/tape, and the backup type.

If a viable means to do so doesn't come about, then I suppose I'll have to pursue writing some SQL queries against the Veeam database to get the information we want to see. I was just hoping I wouldn't have to spend even more time on understanding the table/key relationships to make that happen.

[veremin]

It still works, though. It's been marked deprecated due to it lacking the so-called wrapper. Since it operates with product db (changes to which might be introduced with every product update/release) directly , we cannot guarantee its reliable work any longer. Thus, the "deprecated" status.

I encourage you to still try it out and see whether it meets your expectations. The last time I played with it, it worked more or less OK, however, I had to change the original script slightly.

Thanks.

[ober72]

Hi folks,

I just tried it and it won't work. Also We have Veeam One but the report does not seem to give Tape content, unless I am missing something.

thanks

[ober72]

Wait I retract that, read the output, says no files found in the output... something. I will investigate. That might explain why Veeam one report not putting out much info either.

Thanks

[veremin]

Just to be on the same page - which script and report did you try? I was talking about this script and this report. Thanks.

[ober72]

Yes both of those ones.

Thanks

[ober72]

The Veeam one version looks like it only reports VM backups and not file to tape.

thanks

[veremin]

I've checked the script provided previously and it seems to have worked more or less OK, apart from file path being output wrongly.

The updated version seems to work as expected for file to tape job:

Code: Select all

Asnp VeeamPSSnapin

$table = New-Object system.Data.DataTable “Tape content”
$col1 = New-Object system.Data.DataColumn FilePath,([string])
$col2 = New-Object system.Data.DataColumn FileName,([string])
$col3 = New-Object system.Data.DataColumn Barcode,([string])

$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)

Foreach ($TapeCatalog in Find-VBRTapeCatalog)
    {
    $row = $table.NewRow()         
    $row.FilePath = $TapeCatalog.Parent.Path.Link
    $row.FileName = $TapeCatalog.name
    $row.Barcode = $TapeCatalog.Versions.parts.GetTapeMedium().barcode
    $table.Rows.Add($row)
    }
$table

Veeam ONE provides report for backup to tape jobs, indeed.

Thanks.

[ober72]

Hi Vladimir,

I get this result Find-VBRTapeCatalog : No files found in the backup set (here there is a long number)

In my tape infrastructure I have a lot of tapes and all with files on them.

cheers

[veremin]

The scripts throws an error and doesn't proceed any further, right? What if you make it continue silently?

Code: Select all

Find-VBRTapeCatalog -ErrorAction SilentlyContinue

Thanks.

[ober72]

Hi Vladimir

Sorry got very busy. I still get this: Find-VBRTapeCatalog : No files found in the backup set c23423234..... ......

cheers

[winnt]

If a viable means to do so doesn't come about, then I suppose I'll have to pursue writing some SQL queries against the Veeam database to get the information we want to see. I was just hoping I wouldn't have to spend even more time on understanding the table/key relationships to make that happen.

I was putting this off as well thinking that the "next version" would surely have this basic feature added, but I'm realizing after multiple upgrades it isn't going to happen. Would you be able to share any of your key relationships so I won't have to start from scratch? Using SQL queries will allow me to fetch the tape contents based upon barcode label, VM, file name, or path. Thanks!

[ober72]

Second that vote for key relationships. I too will probably have to go the SQL query route, either that or manually checking properties of each tape and putting it into a spreadsheet.

thanks guys, and Happy Holidays, congrats to those not working

[veremin]

Unfortunately, I don't think I can help here any further, since I have not come across the "No files found" error during my tests.

Let me double check the issue internally, once everyone is back from the holidays.

Thanks.

[winnt]

Happy Friday night to you all!

Since all I heard were crickets from our tape reporting questions, I spent a few minutes this evening assembling a SQL query that produces a functional tape listing. As it is, the query will output the contents on each tape as well as which backup set the files are on and the vault location if vaulted. I figured it would be easiest to just run this in SQL Server Management Studio then export to a CSV, but you can also uncomment the WHERE clause and specify criteria such as barcode or file name. I haven't tested it extensively, but from my spot checks, it seems to be accurate. Please give it a whirl and see if it seems accurate. I'm not an SQL guru, so run it at your own risk. I hope this is as helpful to you as it is for me. Thanks.
P.S. I was joking about only taking a few minutes. I've been painstakingly working on this in my spare time over the past two weeks.

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 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,
IsNULL(TMV.name, 'Not Vaulted') as Vault_Name,
IsNULL(TMV.description, 'Not Vaulted') AS Vault_Description
  
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
LEFT JOIN [Tape.media_in_vaults] TMIV
ON TMIV.media_id = TTM.id
LEFT JOIN [Tape.media_vaults] TMV
ON vault_id = TMV.id

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

--Filter on filename written to tape
--WHERE (TF.name like '%WOPR47%')
--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

[winnt]

After looking through the query results, I decided to add additional columns. It now displays the following:
Barcode ID
Backup Set
Backup Job
Backup Host
Full folder path of file
File name backed up
File size in GB of file
Total tape capacity in GB
Remaining space on tape in GB
Last time tape was written to
Tape expiration date
Tape description
Tape Media Pool
Tape Media Pool Description
Tape Physical location (i.e. HP MSL6060 - Slot 11, or HP MSL6060 - Tape Drive, or Tape Vault - Iron Mountain Yearly)

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

[winnt]

Since this is a PowerShell forum, save the SQL query as C:\Scripts\Veeam\VeeamTapeInventory.sql and then load up the query results:

Code: Select all

$TapeInventory = Invoke-Sqlcmd -InputFile C:\Scripts\Veeam\VeeamTapeInventory.sql -ServerInstance "YourVeeamSQLServer\veeamsql2012"

#Show file names on specific tape 
$TapeInventory | select Barcode_ID, Tape_File_Name | where barcode_id -eq 'JD4747L3'

#Show tapes and file names with full backups from December 2016 (actually just searching on file name)
$TapeInventory | select Barcode_ID, Tape_File_Name | where tape_file_name -like '*2016-12*.vbk*'

#Show tapes and file names with file names containing WOPR:
$TapeInventory | select Barcode_ID, Tape_File_Name | where tape_file_name -like '*WOPR*.vbk*'

#Show tapes that are loaded in the HP MSL library
$TapeInventory | select -unique Barcode_ID, Tape_Physical_Location | where tape_physical_location -like '*MSL*'

#Show tapes that are slotted in library
$TapeInventory | select -unique Barcode_ID, Tape_Physical_Location | where tape_physical_location -like '*slot*'

#Show tapes that are loaded in tape drives
$TapeInventory | select -unique Barcode_ID, Tape_Physical_Location | where tape_physical_location -like '*tape drive*'

[ober72]

Hi Winnt,

Thanks for this!! I will try it as well.

cheers

Geoff

[winnt]

It isn't perfect, as I think I have found a discrepancy. I am figuring it out in my spare time. I think it is giving me too many files on some tapes. Still useful, just don't submit reports to your boss with it yet.

[ober72]

sounds good

cheers

[bnieman]

Hello,

I'm working on a project for the database team to provide a list of all files that were successfully backed up for a file to tape job. We perform a file to tape job on a physical server that backs up a large number of files. Is there a way to export contents of a media tape to a csv or a txt file? Possibly a query that can be ran against our Veeam database to find these results or a powershell cmdlet to use? I know that particular tapes can be navigated to find files, but for DR purposes it's easier for us to have a searchable list. If this isn't possible, it would be a great feature going forward for file to tape jobs. Thank you for the help!

[veremin]

Kindly, check examples provided above. Thanks.