I have been looking in ways to ensure that a SQL Server backup file is backed up by
NetBackup, so that the backup file can be deleted from the local disk. I have not found a API for the local NetBackup client, but there are a lot of command-line tools.
In this case our backup vendor has pointed out the tool "bplist" (bplist.exe). Usually bplist is located in the folder „
%ProgramFiles\Veritea\NetBackup\bin\“.
A bplist answer example looks like this:
-rwx------ root;SQL1 root;Doma 485888 Jul 29 20:00:01 E:\MSSQL\Backup\ReportServer_backup_2012_07_28_221142_2861771.bak
if bplist is called with the parameters „
-l -b -Listseconds“.
WARNING!!!
No year on NetBackup backup.
This is also discussed in the forum thread "
bplist does not display the year of a backup?".
It looks like there is an issue on the age of the backup, if it is more than six months old or not.
Also when I look in the documentation, there are some differences on the answer from the NetBackup server if it is Windows or Linux. Please notice that it is the NetBackup server, not the client operating system that gives the answer.
bplist can be executed in PowerShell with the invoke operator (&):
& "<NetBackup folder>\bplist.exe" -l -b "X:\MSSQL\Backup\sqladmin_repository_backup_2012_12_24_172249_7006410.bak"
The full path must be provided for the file to examine in NetBackup.
To look at how to handle bplist and its answer in a automated way, I made this PowerShell spike script:
param (
[Parameter()]
[ValidateScript({Get-ChildItem -Path $_})]
[string]$NetBackup_Folder = 'C:\Program Files\Veritas\NetBackup\bin'
)
Set-StrictMode -Version 2.0
function Get-NetBackup_bplist {
[CmdletBinding()]
param(
[Parameter(Mandatory=$true,ValueFromPipeline=$true)]
[System.IO.FileInfo]$File
)
BEGIN { Write-Verbose "OS Version = $([System.Environment]::OSVersion.VersionString)" }
PROCESS {
Write-Verbose "File name = '$($File.FullName)'."
$BpList = New-Object -TypeName PSObject
$BpList = $File
$BpList.PSObject.TypeNames.Insert(0,'SqlAdmin.NetBackup.BpList')
# Get file backup status from NetBackup
$bplist_answer = $(& "$NetBackup_Folder\bplist.exe" -l -b -Listseconds "$($File.FullName)") 2>&1 # Redirect bplist.exe error to $bplist_answer
if ($bplist_answer.GetType().IsArray) { # Multiple backups in NetBackup
Write-Verbose " $($bplist_answer.Length) backups found in NetBackup. Will continue on last backup."
$_bplist = $bplist_answer[0] # Get last backup in NetBackup
}
else {
$_bplist = $bplist_answer
}
Write-Verbose " $($_bplist)"
# Evaluate file backup status from NetBackup. Add -PassThru to last added member.
if ($_bplist.ToString() -ceq 'EXIT STATUS 227: no entity was found') { # File not in NetBackup
Write-Verbose " --- NO backup in NetBackup ($($File.Name))."
Add-Member -InputObject $BpList -MemberType NoteProperty -Name HasBackup -Value $false
Add-Member -InputObject $BpList -MemberType NoteProperty -Name BackupLength -Value $null
Add-Member -InputObject $BpList -MemberType NoteProperty -Name BackupTime -Value $null
}
else {
Write-Verbose " +++ Backup is in NetBackup ($($File.Name))."
Add-Member -InputObject $BpList -MemberType NoteProperty -Name HasBackup -Value $true
# Get backup details
$regex = [regex]"\w+"
$Backup = $($regex.matches($_bplist)) # Returns [System.Text.RegularExpressions.Match]
if ($Backup[5].Value.SubString($Backup[5].Length-1) -eq 'K') { # Is the most right char 'K'?
$Backup_Size = [int]$Backup[5].Value.SubString(0,$Backup[5].Length-1) * 1024 # Convert from KB to Bytes
}
else {
$Backup_Size = [int]$Backup[5].Value
}
Add-Member -InputObject $BpList -MemberType NoteProperty -Name BackupLength -Value $Backup_Size
$DateParse = "$($Backup[7].Value) $($Backup[6].Value) $([System.DateTime]::Now.Year) $($Backup[8].Value):$($Backup[9].Value):$($Backup[10].Value)"
Add-Member -InputObject $BpList -MemberType NoteProperty -Name BackupTime -Value $([System.DateTime]::Parse($DateParse))
}
Write-Output $BpList
}
END {}
}
### INVOCATION ###
switch -casesensitive ($ComputerName) {
'TRACY.SQLADMIN.LAN' {
Get-ChildItem -Path 'D:\MSSQL_Backup' |
Sort-Object -Property Length -Descending |
Sort-Object -Property LastWriteTime -Descending |
Select-Object -First 100 |
Get-NetBackup_bplist | #-Verbose |
Where-Object { $_.HasBackup -eq $true } |
#Format-Table Name,Length,HasBackup,BackupTime,BackupLength -AutoSize
ForEach-Object { Remove-Item $_.FullName -Confirm:$true }
break
}
default {
Get-ChildItem -Path 'X:\MSSQL\Backup' |
Sort-Object -Property Length -Descending |
Select-Object -First 20 |
Get-NetBackup_bplist |
#Format-Table Name,Length,HasBackup,BackupTime -AutoSize
ForEach-Object { Remove-Item $_.FullName -Confirm:$true }
break
}
}
Again – a spike…
The general motivation to look into this is to ensure that the restore chain is complete, also in the secondary backup on NetBackup.
The complete restore chain is necessary to ensure complete recovery.
(This is a running update on a post from 2012-08-27)
Posts
