2010
03.16

Abusing Backup Exec’s internal database

Category: IT / Tag: , , , ,  / Add Comment
3 people like this post.
Unlike
Share

What’s the point of having data stored somewhere if you can’t access it and turn it into useful information? Of course the means to do so should be safe, supported, non destructive and flexible if not easy. But usually all you’re left with is some kind of “reporting” feature that necessarily doesn’t do exactly what you need, doesn’t output in a convenient format and so on.

But enough squabbling: in this article I’ll deal with Backup Exec’s internal database.
Here’s what I’m trying to do:

  • Look up all the “Duplicate” Jobs. Show when they started, how long they took to complete, the rate, …
  • For each one of them, try and find the relevant tapes.

I will use the generated report to know which media I should eject out of the library for safe storage. The report will also allow me to quickly and easily update the Excel worksheets where we keep track of how backup’s going.

Our BE database runs on Microsoft SQL Server Express. First thing to do is configure the instance to allow remote TCP/IP connections. Refer to this post, and KB914277.

Then I’m able to point SQL Server Management Studio at it1, and see how the BEDB database is organized.

The view named vwJobHistorySummary is the equivalent of what is seen in BE’s GUI, under Job Monitor → Job List → Job History. Easy enough to find out.

What’s not that immediate to guess is how Media IDs relate to Job IDs: skimming through the database tables doesn’t help… How could you reverse engineer BE GUI and discover what SQL queries it’s doing to carry out its job? In fact, there’s a way to “sniff” SQL queries while they’re running:

  • open up BE GUI and select (but don’t open) a completed Job in Job History.
  • run SQL Server Profiler.
  • create a New Trace.
  • Under the Event Selection tab, deselect everything except SQL:BatchStarting. This is not a particularly crowded database, hence no need for filters.
  • Double click on the previously selected Job; SQL Profiler should capture a query similar to:
SELECT * FROM dbo.vwJobHistory WHERE
JobHistoryID='8507cfa9-8417-44ae-88e6-9ac19a0333a9' ORDER BY [JHD.StartTime]
FOR XML AUTO

Looks like Job details are fetched as globs of XML data, perfect to throw our beloved Regular Expressions at.

You can find the script I made at the end of the post. The obligatory notes are:

  • By convention, in our scenario, Policies used to create Duplicate jobs bear a name ending with “-D”. I’m SELECting the last Job IDs with a similar pattern; change it according to your needs, for instance if you’re interested in all the tape directed Jobs (and not just the Duplicate ones).
  • Columns are as follow:
    • Job name. In case you wonder, “FSIWDTH” means: Full Saturday, Incremental Weekdays, Duplicate on Thursday.
    • Actual start timestamp.
    • End timestamp.
    • Elapsed time (seconds).
    • Total bytes written. No bytes written? I skip this Job.
    • Rate (MBytes/minute). Oddly, BE doesn’t seem to always get this value right.
  • The “convert( varchar(” stuff in the main query is needed to fetch dates in a non driver-dependent format (see FreeTDS FAQ).
  • Dates are stored in UTC timezone. I make sure of adding the local TZ offset before printing them out.

Example output:

./be_job_media_report.pl
Sel SERVER03-FSIWDTH-D;20100311 09:15;20100312 11:26;94239;602103130444;405
AK8008L3
AK8011L3
Sel SERVER07-FSIWDTH-D;20100311 09:00;20100311 09:15;921;5133161638;452
AK8011L3
Sel SERVER16-FSIWDWE-D;20100310 10:35;20100310 17:34;25155;5352;0
AK8011L3
Sel SERVER13-FSIWDWE-D;20100310 09:00;20100310 17:29;30572;230425324573;515
AK7140L1
AK8011L3

And the script itself:

#!/usr/bin/perl
# Giuliano - http://www.108.bz
use strict;
use DBI;
use List::Uniq qw(uniq);
use Time::Piece;

sub pretty_time($) {
    my $time;
    $time = Time::Piece->strptime(shift, "%Y-%m-%d %H:%M:%S"); # 2010-03-10 16:34:19
    $time += $time->localtime->tzoffset;
    return $time->strftime('%Y%m%d %H:%M');
}

sub print_last_jobids($$;$) {
    my ($dbh, $number, $jobname_like) = @_;

    my $q = <<EOQ;
SELECT TOP 20 JobHistoryID, JobName,
              convert( varchar(30), OriginalStartTime, 120),
              convert( varchar(30), ActualStartTime, 120),
              convert( varchar(30), EndTime, 120),
              ElapsedTimeSeconds,
              FinalJobStatus, FinalErrorCode, TotalDataSizeBytes, TotalRateMBMin
FROM vwJobHistorySummary
-- WHERE
-- Jobname LIKE '$jobname_like'
ORDER BY ActualStartTime DESC
EOQ
    $q =~ s/-- (WHERE)/$1/ if $jobname_like;
    $q =~ s/-- (Jobname LIKE)/$1/ if $jobname_like;
    my $sth = $dbh->prepare($q);
    $sth->execute();
    my $row;
    while ( $row = $sth->fetchrow_arrayref ) {
        if ($row->[8]) { # TotalDataSizeBytes > 0
            $row->[3] = pretty_time($row->[3]);
            $row->[4] = pretty_time($row->[4]);
            printf "%s;%.f\n", (join ';', @{$row}[1,3,4,5,8]), $row->[9];
        }
        print_media_by_jobhistoryid($dbh, $row->[0]);
    }
}

sub print_media_by_jobhistoryid($$) {
    my ($dbh, $jobid) = @_;

    my $sth = $dbh->prepare(<<EOQ
SELECT * FROM dbo.vwJobHistory where
JobHistoryID='$jobid' ORDER BY [JHD.StartTime]
FOR XML AUTO
EOQ
    );
    $sth->execute();
    my @media;
    my $row;
    while ( $row = $sth->fetchrow_arrayref ) {
        my $record = join ';', @$row;
        push @media, $1 if $record =~ /Data="(.*?)"/;
    }
    print +(join "\n", uniq({sort => 1},@media)), "\n" if @media;
}

### Main

my $dbh = DBI->connect('dbi:Sybase:server=bedbdatasource;database=BEDB','DOMAIN\username','password') or die;
print_last_jobids($dbh, 10, '%-D');

exit;
  1. No need to enable TCP/IP on the instance, if Management Studio is installed on BE server itself ↩
Share