PowerShell script exchange
winnt
Enthusiast
Posts: 29
Liked: 20 times
Joined: Apr 03, 2015 9:19 pm
Full Name: Jason D
Contact:

Re: Get tape content via powershell

Post by winnt »

My SQL query has an issue with a join returning extra information. bnieman, maybe your database team can fix my query, and in return they get the tape inventory they requested. I think the problem is properly matching up the media_sequence_number in Tape.Tape_mediums and Tape.File_parts. Through all my joins, I am getting fields linked that shouldn't be. I have this diagrammed in a color-coded Excel spreadsheet if you are up for the challenge. Thanks.
dbewernick
Veeam Software
Posts: 673
Liked: 107 times
Joined: Jul 20, 2016 8:02 am
Full Name: David Bewernick
Contact:

Re: Get tape content via powershell

Post by dbewernick »

Thank you for the time you put into this. Did you achieve any further progress so far? Since Find-VBRTapeCatalog is not supported anymore the SQL solution seems to be a great idea. :wink:
user77
Lurker
Posts: 1
Liked: never
Joined: Feb 02, 2018 11:39 am
Contact:

Re: Get tape content via powershell

Post by user77 »

Is anyone have a working experience how to get tape content via powershell or API?
winnt
Enthusiast
Posts: 29
Liked: 20 times
Joined: Apr 03, 2015 9:19 pm
Full Name: Jason D
Contact:

Re: Get tape content via powershell

Post by winnt » 5 people like this post

Well hello again everyone! I am back with an updated tape inventory script! I've spot-checked my results multiple times and they seem to be accurate. Hopefully we can finally get this question answered after almost four years of it being asked! :shock:

Here is the SQL query with the snippet of PowerShell code at the header if you wish to export to a CSV file, which I highly recommend.

Code: Select all

/*

File Name: VeeamTapeInventory.sql

This query will display a list of tapes sorted by barcode id, along with the file name/size, tape space used/free, and where the tape is located whether vaulted or in a library slot.

By winnt posted to Veeam forum on May 3, 2018

I have found this query most useful when exported to a CSV file from a PowerShell script such as this:

# ------- ExportVeeamTapeInventory.ps1 -------

# PowerShell script that runs an SQL query to inventory tapes within Veeam and then exports to a CSV file

# By winnt posted to Veeam forum on May 3, 2018

# ****************************************************************************************

# You will need to install the SQL Server PowerShell Module

# You need to change the Veeam backup server / SQL Server name/instance to match yours.

# You need to change the $ScriptDir and $ReportDir variables, or leave them as-is and make 

# sure the paths exist for this script file, the SQL input file, and exported tape report.

# ****************************************************************************************

$VeeamSqlServer = 'WPBACKUP01\veeamsql2012'

$ScriptDir = 'C:\Scripts\Veeam'

$ReportDir = 'C:\Scripts\Veeam\Reports'

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

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

# ------- ExportVeeamTapeInventory.ps1 -------

*/




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 BarcodeID,
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,
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.barcode as TTM_Barcode,
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_Barcode ASC



If anyone is good with macros, maybe they could script these steps in Excel?
1. Bold top row
2. Freeze top row (View, Freeze Panes, Freeze Top Row)
3. Enable data filter on all columns (highlight entire worksheet, Data, Filter)
4. Save as .xlsx

Thanks!
winnt
Enthusiast
Posts: 29
Liked: 20 times
Joined: Apr 03, 2015 9:19 pm
Full Name: Jason D
Contact:

Re: Get tape content via powershell

Post by winnt » 2 people like this post

Here is the PowerShell script separated out.

Code: Select all

# ------- ExportVeeamTapeInventory.ps1 -------

# PowerShell script that runs an SQL query to inventory tapes within Veeam and then exports to a CSV file

# By winnt posted to Veeam forum on May 3, 2018

# ****************************************************************************************

# You will need to install the SQL Server PowerShell Module

# You need to change the Veeam backup server / SQL Server name/instance to match yours.

# You need to change the $ScriptDir and $ReportDir variables, or leave them as-is and make

# sure the paths exist for this script file, the SQL input file, and exported tape report.

# ****************************************************************************************

$VeeamSqlServer = 'WPBACKUP01\veeamsql2012'

$ScriptDir = 'C:\Scripts\Veeam'

$ReportDir = 'C:\Scripts\Veeam\Reports'

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

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

# ------- ExportVeeamTapeInventory.ps1 -------

ekisner
Expert
Posts: 202
Liked: 34 times
Joined: Jul 26, 2012 8:04 pm
Full Name: Erik Kisner
Contact:

Re: Get tape content via powershell

Post by ekisner » 1 person likes this post

Winnt, you are fantastic. This is fantastic. It shouldn't be this difficult!
newguise_
Novice
Posts: 7
Liked: never
Joined: Mar 26, 2015 5:27 am
Full Name: Matt Nock
Contact:

[MERGED] find out which tape(s) were used for a job run

Post by newguise_ »

VEEAM B&R 8.0 (yes, I know I should upgrade).

After VEEAM writes a backup to tape, I'd like to determine programmatically (using PowerShell) which tape(s) were used for a job, and other details such as the media set, the sequence number of each tape, etc. This is so I can automate an email to our tape storage provider telling them what tapes to collect/drop off.

What commands can give the required output? I've tried Get-VBRTapeBackup but this returns "WARNING: This cmdlet is obsolete and no longer supported" which makes me reluctant to use it. That said, the output object of this command has the method GetOibsTape, which looks promising, if a bit fiddly (shows the media set, etc). Still, I would prefer a solution that doesn't complain about being unsupported every time it's run.

Anyone have any tips, or has implemented something similar? Thanks in advance!
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get tape content via powershell

Post by veremin »

Check the scripts provided above; should be close to what you're looking for. Thanks.
Narwhal
Novice
Posts: 5
Liked: never
Joined: Aug 03, 2018 2:08 pm
Full Name: Narwhal
Contact:

Getting tape content

Post by Narwhal »

Hi,

I scheduled a "Files to Tape" job for which I'd like to get its contents to verify that information was backed up properly.

I opened a case (case #03130555) about this and they pointed me back to this forum, to this thread specifically:

https://forums.veeam.com/powershell-f26 ... 22142.html

On that thread the cmdlet Find-VBRTapeCatalog is mentioned. But when I run this on Veeam 9.5, I get the following error:

Code: Select all

Find-VBRTapeCatalog : Object reference not set to an instance of an object.
t line:1 char:1
 Find-VBRTapeCatalog | select {$_.Parent.Path.Parent.Path}, name, {$_. ...
 ~~~~~~~~~~~~~~~~~~~
   + CategoryInfo          : NotSpecified: (:) [Find-VBRTapeCatalog], NullReferenceException
   + FullyQualifiedErrorId : System.NullReferenceException,Veeam.Backup.PowerShell.Cmdlets.FindVBRTapeCatalog
It *does* seem like content is listed, but I'm not sure how well that works. I also get a deprecation warning.

I've been trying to find alternatives, but couldn't find one (for example, Get-VBRFilesInRestorePoint does not seem to work with "Files to Tape" jobs).

Do other alternative exist to get this kind of information?
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get tape content via powershell

Post by veremin »

An alternative is provided on the last page of this thread; the one that operates with configuration db directly. Thanks.
sbk
Enthusiast
Posts: 32
Liked: 2 times
Joined: Apr 06, 2017 2:03 pm
Full Name: Shawn Krawczyk
Contact:

[MERGED] Full/incremental backup to tape report

Post by sbk »

Hey all,

Wondering if anyone has created or may be able to help point me in the right direction to create a couple of pretty basic reports for tape backups. Not a powershell guru by any means and each time I attempt to create these I just can't seem to pull the correct information. The tape report that can be generated from Veeam ONE has all the information, it just has too much information and the report keeps getting bigger and bigger with information I no longer need. So what I end up having to do is run the report from Veeam ONE, then manually copy/paste the information I need into a spreadsheet. Here is what I am trying to accomplish:

Report #1:
Powershell script that pulls the following information for all full backup to tape jobs (ran once a month after all jobs have completed):

Job name, Restore Point, Tapes used for only FULL backup to tape jobs for the current month.
Detail: We do a full backup to tape run each month then when off siting tapes to our DR location, all tapes get stuck into a bin with a printed report for restore purposes that contains a list with all tape jobs and the associated tapes for the given month/tape job. These tapes are retained forever, never being over written.

Report #2:
Powershell script that pulls the following information for all incremental to tape jobs:
Job name, Restore point, Tapes used for only incremental to tape jobs for the current day (report would be executed daily).

Appreciate any help or advice anyone may have.
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get tape content via powershell

Post by veremin »

Check the script above; should give you some ideas on how the whole process can be scripted. Thanks.
wkjan
Lurker
Posts: 1
Liked: never
Joined: Mar 03, 2019 11:19 am
Full Name: Jan Ziemann
Contact:

[MERGED] List tapes used by an restore point

Post by wkjan »

Hi,

I would like to produce a list of all VM restore points on tape together with a list of tapes they resides on.
The first part I get with

Code: Select all

Get-VBRRestorePoint | ?{$_.GetBackup().JobType -eq "VmTapeBackup"} | select Name, creationtime, Type
But how can I get the list of tapes involved in each restore point? I know the list ist included in the session object, but I don't know how to map the restore points to sessions eather.

Can anyone help?
veremin
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get tape content via powershell

Post by veremin »

Kindly, see the examples provided above; should be something you're after. Thanks!
winnt
Enthusiast
Posts: 29
Liked: 20 times
Joined: Apr 03, 2015 9:19 pm
Full Name: Jason D
Contact:

Re: Get tape content via powershell

Post by winnt » 1 person likes this post

I tested the script on a new Windows 10 machine, and found that the only PowerShell requirement was to install the SQL Server module:

install-module -name sqlserver

If it prompts to install NuGet, go ahead and install it.

Now, whenever you run .\ExportVeeamTapeInventory.ps1 from a standard PowerShell window (never needed to run from Veeam PowerShell) you won't need to add any snap-ins.

The following lines should also be deleted since they aren't needed:

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

Thanks.
woifgaung
Veeam Software
Posts: 48
Liked: 7 times
Joined: Oct 15, 2015 2:57 pm
Full Name: Wolfgang Scheer
Contact:

Re: Get tape content via powershell

Post by woifgaung » 1 person likes this post

Thank you, winnt for your SQL and PowerShell script! I can confirm, it still works fine in VBR v11!
Wolfgang | vnote42.net | @vNote42
ThierryF
Expert
Posts: 129
Liked: 33 times
Joined: Mar 31, 2018 10:20 am
Contact:

[MERGED]WORM Media : List MediaSet Backups before offlining

Post by ThierryF »

Hello,

Looking at implementing WORM Medias to battle and protect against Cyber attack on
infrastructure and VBR Backup repository/ies, I would like, when offlining Media Sets
from lib, joining a hard copy listing of backups stored on medias.

Any ideas how to achieve it and automate it from Powershell ?

From Get-VBRTapeMedium, I can retrieve tape media(s) and media set(s)
but any way to query Veeam for listing backups on a given media or mediaset ?

Thanks for suggestions.

TH
Mildur
Product Manager
Posts: 8549
Liked: 2223 times
Joined: May 13, 2017 4:51 pm
Full Name: Fabian K.
Location: Switzerland
Contact:

Re: Get tape content via powershell

Post by Mildur » 1 person likes this post

Hi Thierry

I moved your post to this topic. I believe you should find a way with the scripts posted in this topic.
If not, let me know.

Thanks
Fabian
Product Management Analyst @ Veeam Software
ThierryF
Expert
Posts: 129
Liked: 33 times
Joined: Mar 31, 2018 10:20 am
Contact:

Re: Get tape content via powershell

Post by ThierryF »

Hello Fabian,
Querying SQL seems to make me happy.
Just 2 points I haven't solved yet :
- How to grab library name ? from [dbo].[Tape.tape_mediums], I retrieve a "location_library_id" but cannot link it with [dbo].[Tape.changers].device_id
- How to grab media set name the mediums belong to ?

Thanks for your ideas ...
Th
oleg.feoktistov
Veeam Software
Posts: 1912
Liked: 635 times
Joined: Sep 25, 2019 10:32 am
Full Name: Oleg Feoktistov
Contact:

Re: Get tape content via powershell

Post by oleg.feoktistov »

Hi ThierryF,

I think you can easily avoid using SQL in this case:

Code: Select all

$mediums = Get-VBRTapeMedium
$libraries = Get-VBRTapeLibrary
foreach ($medium in $mediums) {
    $library = $libraries | where {$_.Id -eq $medium.LibraryId}
    $medium | select Barcode, LibraryId, @{n='LibraryName';e={$library.Name}}, @{n='MediaSetName';e={$_.MediaSet.Name}}
}
Thanks,
Oleg
apackard
Novice
Posts: 3
Liked: never
Joined: Mar 11, 2022 2:24 pm
Full Name: Almon Packard
Contact:

[MERGED] How to Export or Archive Information Detailing Backup Jobs on Tape

Post by apackard »

For many years we have used tapes for off-site storage of backups and have a couple hundred of them. Recently we stopped using tapes because we installed a new Backup Server using object storage in the cloud for off-site storage to replace tapes. We did not import any information regarding the tapes or the backup jobs on them. We are decommissioning the old Backup Server but want to keep the information detailing which Jobs are on which Tape, in case we need to restore anything.

Is there a utility or script that will export this information? Do I need to use write my own PowerShell script?
Mildur
Product Manager
Posts: 8549
Liked: 2223 times
Joined: May 13, 2017 4:51 pm
Full Name: Fabian K.
Location: Switzerland
Contact:

Re: Get tape content via powershell

Post by Mildur »

Hi Almon

I moved your request to this topic.
For Backup on Tapes, you may checkout Veeam One:
https://helpcenter.veeam.com/docs/one/r ... ml?ver=110

For File to Tape Backup Jobs we don't have a supported tool to export the information. You can try the provided Script in this topic (still unsupported).
Maybe it also works for Backup to Tape Jobs. I didn't had the chance to try it myself.

Thanks,
Fabian
Product Management Analyst @ Veeam Software
albertwt
Veeam Legend
Posts: 879
Liked: 46 times
Joined: Nov 05, 2009 12:24 pm
Location: Sydney, NSW
Contact:

Re: Get tape content via powershell

Post by albertwt »

winnt wrote: Mar 05, 2017 9:11 pm 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

Thank you for sharing it here Jason.
--
/* Veeam software enthusiast user & supporter ! */
stefanbrun
Service Provider
Posts: 27
Liked: 5 times
Joined: Apr 26, 2011 7:36 am
Full Name: Stefan Brun | Streamline AG
Location: Switzerland
Contact:

Re: Get tape content via powershell

Post by stefanbrun »

Hi,

created the ExportVeeamTapeInventory.ps1 and VeeamTapeInventory.sql

When i run the Script i receive following error:

Code: Select all

PS C:\Scripts\Veeam> C:\Scripts\Veeam\ExportVeeamTapeInventory.ps1
Invoke-Sqlcmd : A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, 
error: 0 - The certificate chain was issued by an authority that is not trusted.)
So i changed the file ExportVeeamTapeInventory.ps1 to this (added only "-TrustServerCertificate" to Invoke-Sqlcmd Command):

Code: Select all

# ------- ExportVeeamTapeInventory.ps1 -------

# PowerShell script that runs an SQL query to inventory tapes within Veeam and then exports to a CSV file

# By winnt posted to Veeam forum on May 3, 2018

# ****************************************************************************************

# You will need to install the SQL Server PowerShell Module

# You need to change the Veeam backup server / SQL Server name/instance to match yours.

# You need to change the $ScriptDir and $ReportDir variables, or leave them as-is and make 

# sure the paths exist for this script file, the SQL input file, and exported tape report.

# ****************************************************************************************

$VeeamSqlServer = 'SERVERNAME\INSTANCENAME'

$ScriptDir = 'C:\Scripts\Veeam'

$ReportDir = 'C:\Scripts\Veeam\Reports'

# Add-PSSnapin SqlServerCmdletSnapin100

# Add-PSSnapin SqlServerProviderSnapin100

$date = Get-Date -format "yyyyMMdd-HHmmss"

$TapeInventory = Invoke-Sqlcmd -InputFile $ScriptDir\VeeamTapeInventory.sql -ServerInstance "$VeeamSQLServer" -TrustServerCertificate

$TapeInventory | Export-Csv $ReportDir\VeeamTapeInventory_$Date.csv -NoTypeInformation

Write-Host "Tape inventory has been exported to: "

Write-Host "$ReportDir\VeeamTapeInventory_$Date.csv"

# ------- ExportVeeamTapeInventory.ps1 -------
Now it works without any Problem.

Thx for the Script!
AndrewAdvnetsol
Service Provider
Posts: 11
Liked: never
Joined: Jan 24, 2020 6:06 pm
Full Name: Andrew Carmichael
Contact:

Re: Get tape content via powershell

Post by AndrewAdvnetsol »

Hi,

First off thank you to everyone who has worked to get this script working. I have been looking for something like this for a bit now. I have created the SQL query and Powershell script. All the output it generates is great. The only thing I would like it to have are columns for Tape Name and Media Set. Basically what I am looking for is know what VBK file is on which tape. But I do like a lot of what is already generated with the script so I might choose to remove some of the information from the script I run in the future.

Thank you in advance for you help.
winnt
Enthusiast
Posts: 29
Liked: 20 times
Joined: Apr 03, 2015 9:19 pm
Full Name: Jason D
Contact:

Re: Get tape content via powershell

Post by winnt »

Each row has the file name with the barcode ID in the first column. You are using the script from May 3, 2018 correct?
AndrewAdvnetsol
Service Provider
Posts: 11
Liked: never
Joined: Jan 24, 2020 6:06 pm
Full Name: Andrew Carmichael
Contact:

Re: Get tape content via powershell

Post by AndrewAdvnetsol »

Yes that is correct. I am using the script from May 3rd. My tapes do not have barcodes assigned to them. It doesn't look like our tape drive generates barcodes. I have a Dell PowerVault LTO9 tape drive, which is a Dell branded IBM Ultrium-HH9 drive. When I look at the properties on the tapes in Veeam the barcode field is blank. So the output in the Barcode ID column is blank. Also I don't have a column that matches the Media Set in Veeam. In Veeam the media set shows up as "Weekly media set #XX followed by a date and time". I do see a column that shows Tape Backup Set, but it list the out put as "Full backup set followed by a date and time." I am guessing the Media set in Veeam is when the media set was created and the Tape Backup Set is when the file was put on the tape.

Also in Veeam my tapes are just named Tape 1, Tape 2, Tape 3, etc.

I can run this script and get VM Name, Creation Time, Tape Name to display. But I like yours better because it gives me the File Name in the output.

$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
AndrewAdvnetsol
Service Provider
Posts: 11
Liked: never
Joined: Jan 24, 2020 6:06 pm
Full Name: Andrew Carmichael
Contact:

Re: Get tape content via powershell

Post by AndrewAdvnetsol »

@winnt,

Is there a way with your script get the tape name to display? Since I don't have barcodes on my tapes the barcode column doesn't help me. I would also like to have the media set show up in the excel file if that is possible.

Thank you for your help.
JTT
Service Provider
Posts: 99
Liked: 2 times
Joined: Jan 02, 2017 7:31 am
Full Name: JTT
Contact:

[MERGED] What backups are written on tapes

Post by JTT »

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?
Mildur
Product Manager
Posts: 8549
Liked: 2223 times
Joined: May 13, 2017 4:51 pm
Full Name: Fabian K.
Location: Switzerland
Contact:

Re: Get tape content via powershell

Post by Mildur »

Hi JTT

Please check this topic. There are some scripts which could help you to achieve your goal.

Best,
Fabian
Product Management Analyst @ Veeam Software
Post Reply

Who is online

Users browsing this forum: No registered users and 22 guests