Determine if SQL Backup failure was a tape backup or a local backup

A lot of Administrators spend a great deal of time investigating backup failures in Microsoft SQL. That is if they want to avoid a disaster anyway. Backups are important so if you ignore the failures you will pay for it later.

Anyway I discovered some interesting points about some of the specific Windows EventIDs that are in the Windows Application logs. If you focus specifically on EventIDs 18264 and 18265 Database and Transaction Log backups respectively. You can use PowerShell automation and these 2 events to quickly develope a diagnostic script that can determine if the backup failure was a disk or a tape. Then you know who to scream at, or for some Administrators that may be themselves.

First you find the time of the failure:

$Database = Read-Host "Enter the name of the database"
$lastfail = Get-EventLog -LogName Application -Source *SQL* -Message *Backup*$Database*  | Where-Object {$_.EventID -eq 3041} | Select-Object -First 1 -ExpandProperty TimeGenerated

Once you have that datetime object stored as a variable you will use it to find the corresponding 18264 or 18265 EventID that is newer than the last failure. For this example we’ll use the database failure and EventID 18264.

$18264 = Get-EventLog -LogName Application -Source *SQL* -After $lastfail -Data *$Database* | Where-Object {$_.EventID -eq 18264}
$BUdevice = $18264.ReplacementStrings[7]
if ($BUdevice -notmatch "TYPE=")
    "We could not determine the backup device type"
elseif ($BUdevice -match "TYPE=DISK")
    "The backup device was a DISK or UNC path"
elseif ($BUdevice -match "TYPE=VIRTUAL_DEVICE")
   "The backup device was most likely a tape backup"

It is important to mention that for the Transaction Log backup EventID 18265 the index of replacement strings is a six instead of a seven because of the placement of that string in the message. So for EventID 18265 use “ReplacementStrings[6]”.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s