Discovering Symantec NetBackup

I have worked with a large organization (150,000+ employees). This organization has a central location and hundreds of subordinate sites. Headquarters (HQ) has dictated that the Symantec NetBackup MS SQL Client will be used for all SQL Server backups. Each site has local backup appliances which are replicated to off-site locations for more redundancy.

At one of the site’s, however, there had been a mixture of backup strategies actually implemented and I was going to align all the systems with HQ’s policies and then improve upon the backup monitoring systems. In order to do this I needed to answer two important questions:

  • what servers are configured correctly?
  • of the misconfigured servers, what are they using?

This blog post will be an explanation of how I answered those two questions.

NOTE 1: This solution is not very polished, but it pointed me to all the correct servers to reconfigure.

NOTE 2: Later, I was able to get access to the NetBackup application and was provided an audit report of the servers using the MS SQL Client. The solution below I had put together because I wanted faster answers to my questions.

Assumptions

Since this post is an explanation of real events, it will not be a generic catch-all solution for others who need those same two questions answered. These are the assumptions that I was able to make based on my institutional knowledge of the organization.

  • We do not have access to NetBackup.
  • We do have access to the server file systems, a complete server list, and all the SQL Server instances.
  • NetBackup has been the only application, within this organization, to use VSS (Volume Shadow Copy Service framework) for backups. Another way to say this is that, aside from NetBackup, SQL Server native backups have been performed. Specifically, Ola Hallengren maintenance solution, SSMS maintenance plans, and Minion Backups have been discovered on the servers.
  • Our company uses a standard naming convention for FULL and LOG backup configurations for NetBackup. FULL configurations are dbase.bch and LOG configurations are logs.bch.
  • The configurations are in NetBackup’s default location.

Using msdb

msdb has a handful of tables which make up your backup history for the SQL Server. Any 3rd party backup software worth paying for will make sure that history records are inputted into msdb. NetBackup is one such application. The query below searches the backup history for FULL backups which have not used a UNC or local file path pattern. This indirectly indicates that they are using VSS.  It then returns a list of databases which have not had a backup utilizing VSS.

I am using an optional WHERE clause to only work with records in the last week. The reason behind this is to point out servers which might have used NetBackup before but have not completed a recent backup with it. Those servers need my attention as much as any other server in this audit.

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server] ,
d.name [database_name]
FROM sys.databases d
WHERE d.name <> 'tempdb'
EXCEPT
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server] ,
bs.database_name
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
-- FULL backups only.
WHERE bs.[type] = 'D'
-- Eliminate UNC and local paths.
-- Indirectly equates to VSS entries.
AND bmf.[physical_device_name] NOT LIKE '\\%'
AND bmf.[physical_device_name] NOT LIKE '_:\%'
-- Only recent backups can exempt the db from our audit.
AND bs.[backup_finish_date] > DATEADD(dd,-7,GETDATE())
GROUP BY bs.database_name
ORDER BY database_name

We will need to run this on every server in our enterprise and compile the results. For this I will use PowerShell and handle the coding in the same script as I search for the NetBackup configuration files. See below.

NetBackup configuration search

NetBackup stores its configuration files, by default, in C:\Program Files\Veritas\NetBackup\DbExt\MsSql. As the assumptions listed above mention, this organization used this same place as the only approved place to store the configurations. Part of our audit will be to detect if we have the appropriately named configurations in the correct location.

Below is a pretty simple PowerShell script which will search all the servers that you have listed in SQL Server Central Management Server for the right files and execute the msdb query that we spoke of in the last section. It will output a list of servers missing the configurations, a list of servers where the file search could not be conducted, and a list of database servers with errors. For the database call, an error can be a true exception or the presence of databases which do not have a recent NetBackup backup. You will need to investigate these servers individually for corrective action.

Output:

netbackup-script-output

Script:

$ErrorActionPreference = "stop";
Import-Module SQLPS -DisableNameChecking;
Set-Location C:\;

$query = @"
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server] ,
d.name [database_name]
FROM sys.databases d
WHERE d.name <> 'tempdb'
EXCEPT
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS [Server] ,
bs.database_name
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
-- FULL backups only.
WHERE bs.[type] = 'D'
-- Eliminate UNC and local paths.
-- Indirectly equates to VSS entries.
AND bmf.[physical_device_name] NOT LIKE '\\%'
AND bmf.[physical_device_name] NOT LIKE '_:\%'
-- Only recent backups can exempt the db from our audit.
AND bs.[backup_finish_date] > DATEADD(dd,-7,GETDATE())
GROUP BY bs.database_name
ORDER BY database_name
"@

$netbackupPath = '\\{0}\C$\Program Files\Veritas\NetBackup\DbExt\MsSql'
$serversMissingFULLConfigs = @();
$serversMissingLOGConfigs = @();
$serversWithFileErrors = @();
$serversWithDBErrors = @();

# For simplicity I didn't write this to be asynchronous, sorry.

Foreach ($serverName in (Search-CMS).ServerName)
{
try
{
$files = Get-ChildItem -Path ($netbackupPath -f ($serverName.Split("\\")[0]))
}
catch
{
$serversWithFileErrors += $serverName;
}

try
{
$databases = Invoke-Sqlcmd -Query $query -ServerInstance $serverName -Database msdb;

if ($databases)
{
$serversWithDBErrors += $serverName;
}
}
catch
{
$serversWithDBErrors += $serverName;
}

if (-not $files | Where-Object { $_.Name -ilike "dbase.bch" })
{
$serversMissingFULLConfigs += $serverName;
}

if (-not $files | Where-Object { $_.Name -ilike "logs.bch" })
{
$serversMissingLOGConfigs += $serverName;
}
}

Write-Host "==================================" -ForegroundColor Green -BackgroundColor Black
Write-Host "Servers Missing FULL Configs" -ForegroundColor Green -BackgroundColor Black
Write-Host "==================================" -ForegroundColor Green -BackgroundColor Black
$serversMissingFULLConfigs | fl;
Write-Host "==================================" -ForegroundColor Green -BackgroundColor Black
Write-Host "Servers Missing LOG Configs" -ForegroundColor Green -BackgroundColor Black
Write-Host "==================================" -ForegroundColor Green -BackgroundColor Black
$serversMissingLOGConfigs | fl;
Write-Host "==================================" -ForegroundColor Green -BackgroundColor Black
Write-Host "Servers with file access errors" -ForegroundColor Green -BackgroundColor Black
Write-Host "or missing the NetBackup directory" -ForegroundColor Green -BackgroundColor Black
Write-Host "==================================" -ForegroundColor Green -BackgroundColor Black
$serversWithFileErrors | fl;
Write-Host "==================================" -ForegroundColor Green -BackgroundColor Black
Write-Host "Servers with database errors" -ForegroundColor Green -BackgroundColor Black
Write-Host "==================================" -ForegroundColor Green -BackgroundColor Black
$serversWithDBErrors | fl;

You may have noticed a non-standard PowerShell CmdLet in my script, Search-CMS. It is my solution for searching SQL Server’s Central Management Server from PowerShell. See my post on Multiple Server Management – Finding the Database Server for more details and the code.

Leave a Reply

%d bloggers like this: