Get tape content via powershell

PowerShell script exchange

Re: Get tape content via powershell

Veeam Logoby v.Eremin » Tue Dec 13, 2016 2:48 pm

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.
v.Eremin
Veeam Software
 
Posts: 13709
Liked: 1026 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Get tape content via powershell

Veeam Logoby ober72 » Tue Dec 13, 2016 6:30 pm

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
Geoff Burke
VMCE V9
ober72
Service Provider
 
Posts: 299
Liked: 40 times
Joined: Fri Jan 24, 2014 4:10 pm
Location: CANADA
Full Name: Geoff Burke

Re: Get tape content via powershell

Veeam Logoby v.Eremin » Wed Dec 14, 2016 11:20 am

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.
v.Eremin
Veeam Software
 
Posts: 13709
Liked: 1026 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Get tape content via powershell

Veeam Logoby ober72 » Thu Dec 22, 2016 7:50 pm

Hi Vladimir

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

cheers
Geoff Burke
VMCE V9
ober72
Service Provider
 
Posts: 299
Liked: 40 times
Joined: Fri Jan 24, 2014 4:10 pm
Location: CANADA
Full Name: Geoff Burke

Re: Get tape content via powershell

Veeam Logoby winnt » Tue Dec 27, 2016 4:48 pm

bevair wrote: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!
winnt
Influencer
 
Posts: 16
Liked: 7 times
Joined: Fri Apr 03, 2015 9:19 pm
Full Name: J D

Re: Get tape content via powershell

Veeam Logoby ober72 » Wed Dec 28, 2016 5:06 pm

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 :)
Geoff Burke
VMCE V9
ober72
Service Provider
 
Posts: 299
Liked: 40 times
Joined: Fri Jan 24, 2014 4:10 pm
Location: CANADA
Full Name: Geoff Burke

Re: Get tape content via powershell

Veeam Logoby v.Eremin » Wed Jan 04, 2017 12:27 pm

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.
v.Eremin
Veeam Software
 
Posts: 13709
Liked: 1026 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

Re: Get tape content via powershell

Veeam Logoby winnt » Sat Mar 04, 2017 2:58 am 1 person likes this post

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
Influencer
 
Posts: 16
Liked: 7 times
Joined: Fri Apr 03, 2015 9:19 pm
Full Name: J D

Re: Get tape content via powershell

Veeam Logoby winnt » Sun Mar 05, 2017 9:11 pm 1 person likes this post

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
Influencer
 
Posts: 16
Liked: 7 times
Joined: Fri Apr 03, 2015 9:19 pm
Full Name: J D

Re: Get tape content via powershell

Veeam Logoby winnt » Mon Mar 06, 2017 12:32 pm 1 person likes this post

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

winnt
Influencer
 
Posts: 16
Liked: 7 times
Joined: Fri Apr 03, 2015 9:19 pm
Full Name: J D

Re: Get tape content via powershell

Veeam Logoby ober72 » Sun Mar 12, 2017 11:17 pm

Hi Winnt,

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

cheers

Geoff
Geoff Burke
VMCE V9
ober72
Service Provider
 
Posts: 299
Liked: 40 times
Joined: Fri Jan 24, 2014 4:10 pm
Location: CANADA
Full Name: Geoff Burke

Re: Get tape content via powershell

Veeam Logoby winnt » Sun Mar 12, 2017 11:28 pm

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.
winnt
Influencer
 
Posts: 16
Liked: 7 times
Joined: Fri Apr 03, 2015 9:19 pm
Full Name: J D

Re: Get tape content via powershell

Veeam Logoby ober72 » Mon Mar 13, 2017 5:12 pm

:) sounds good

cheers
Geoff Burke
VMCE V9
ober72
Service Provider
 
Posts: 299
Liked: 40 times
Joined: Fri Jan 24, 2014 4:10 pm
Location: CANADA
Full Name: Geoff Burke

[MERGED] Files on tape export to csv

Veeam Logoby bnieman » Thu Oct 05, 2017 4:59 pm

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!
bnieman
Novice
 
Posts: 4
Liked: never
Joined: Thu Feb 23, 2017 4:24 pm
Full Name: Bryan Nieman

Re: Get tape content via powershell

Veeam Logoby v.Eremin » Fri Oct 06, 2017 11:02 am

Kindly, check examples provided above. Thanks.
v.Eremin
Veeam Software
 
Posts: 13709
Liked: 1026 times
Joined: Fri Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin

PreviousNext

Return to PowerShell



Who is online

Users browsing this forum: No registered users and 1 guest