PowerShell script exchange
winnt
Influencer
Posts: 23
Liked: 9 times
Joined: Apr 03, 2015 9:19 pm
Full Name: J D
Contact:

Re: Get tape content via powershell

Post by winnt » Oct 06, 2017 6:37 pm

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: 153
Liked: 20 times
Joined: Jul 20, 2016 8:02 am
Contact:

Re: Get tape content via powershell

Post by dbewernick » Nov 23, 2017 7:46 pm

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 » Feb 02, 2018 11:42 am

Is anyone have a working experience how to get tape content via powershell or API?

winnt
Influencer
Posts: 23
Liked: 9 times
Joined: Apr 03, 2015 9:19 pm
Full Name: J D
Contact:

Re: Get tape content via powershell

Post by winnt » May 03, 2018 9:07 pm 1 person likes 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
Influencer
Posts: 23
Liked: 9 times
Joined: Apr 03, 2015 9:19 pm
Full Name: J D
Contact:

Re: Get tape content via powershell

Post by winnt » May 04, 2018 2:50 pm 1 person likes 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: 184
Liked: 33 times
Joined: Jul 26, 2012 8:04 pm
Full Name: Erik Kisner
Contact:

Re: Get tape content via powershell

Post by ekisner » Jun 11, 2018 3:15 pm

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_ » Aug 02, 2018 8:46 pm

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!

v.Eremin
Veeam Software
Posts: 15209
Liked: 1146 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get tape content via powershell

Post by v.Eremin » Aug 03, 2018 9:07 am

Check the scripts provided above; should be close to what you're looking for. Thanks.

Narwhal
Lurker
Posts: 2
Liked: never
Joined: Aug 03, 2018 2:08 pm
Full Name: Narwhal
Contact:

Getting tape content

Post by Narwhal » Aug 03, 2018 2:16 pm

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?

v.Eremin
Veeam Software
Posts: 15209
Liked: 1146 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get tape content via powershell

Post by v.Eremin » Aug 03, 2018 2:18 pm

An alternative is provided on the last page of this thread; the one that operates with configuration db directly. Thanks.

sbk
Influencer
Posts: 15
Liked: 1 time
Joined: Apr 06, 2017 2:03 pm
Full Name: Shawn Krawczyk
Contact:

[MERGED] Full/incremental backup to tape report

Post by sbk » Sep 12, 2018 12:41 pm

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.

v.Eremin
Veeam Software
Posts: 15209
Liked: 1146 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Get tape content via powershell

Post by v.Eremin » Sep 17, 2018 10:54 am

Check the script above; should give you some ideas on how the whole process can be scripted. Thanks.

Post Reply

Who is online

Users browsing this forum: No registered users and 9 guests