SQL Database backup monitoring

SQL backups and their monitoring is one of the most important things. We often talk about rather complex situations including transaction-logfiles and various other stuff.

Monitoring those things was something that did cost us to many sensors with the standard-scripts etc. and was not really effective.

In order to change this - here are two scripts that will be able to solve most of your issues - you find them both at the end of this posting:

• SQLBackupDestinationCheck.vbs
• SQL_Database_Full_Backups.sql

SQLBackupDestinationCheck.vbs: This is a VBS script that will return XML content to PRTG in multiple channels while using one sensor.

It expects three parameters:

• go through first level sub folders: 0 (no) or 1 (yes)
• file extension to obey - any other extension will be ignored - in most cases: "bak"
• Path - should mostly be an UNC path

It will return those channels:

• Total file count: count of all files with this extension in all folders checked
• Total folder count: count of all folders checked
• Oldest file found in days: oldest file - value gives back age in days
• Newest file found in days: newest file - value gives back age in days
• Lowest files in folder count found: lowest count of files that have been found in one folder
• Highest files in folder count found: max. files that have been found in one folder

This needs some explanation:
The script checks a path for files with a certain extension. Let's say you do SQL maintenance plans and use the extension .BAK to write those. You do a daily backup and keep them for 3 days to make sure they end up on a tape, further do you use sub-folders per database and you have a total of 5 databases on this system - now you will need to configure error-limits per channel - e.g.:

5 databases x 3 days = 15 files in total - 3 files per folder are expected, 5 folders are expected

• Total file count: lower limit: 10 files - upper limit: 20 files - during the backup you might have up to 20 files
• Total folder count: 5 folders upper and lower limit - more/less then 5 would mean something changed
• Oldest file found in days: lower limit 2 days - upper limit 4 days - older then 4 would mean the cleanup does not work
• Newest file found in days: lower limit 0 days - upper limit 2 days - nothing newer (date issues? and nothing older as well)
• Lowest files in folder count found: lower limit: 2 - there should be always more then 2x .BAK files in any subfolder
• Highest files in folder count found: upper limit: 4 - anything above again would mean some clean up is not working right for one database

So - keep in mind - you can get more fancy with WARNING limits and ERROR limits - the example above will help you understand what to do - this should help you getting started. The script will save you quite a few sensors and still keep a pretty close watch on the file-system side of SQL backups - of course you could abuse it for something else then SQL backups as well - but this was my main intent for this script.
SQL_Database_Full_Backups.sql
This file will request information about backups for SQL itself. It might need a SQL 2005 or newer to work - and yes - I did post this on another PRTG KB thread - but I wanted to have the complete solution in this one post.

The script will be executed against the SQL server instance the databases reside, on the master-database. You need to specify a parameter that will be set as "@MaxHours" - this actually should be something like 26 hours, so your SQL backups are never exciting 26 hours - giving the backup some time to run as well. More for bigger databases might be necessary. If you do multiple full-backups per day, set it to e.g. 2 hours or what ever your limit is.

You will get backup 3x columns:

RecentlyBackupUpCount and NOTRecentlyBackupUpCount should always match up to TotalAmountOfDatabases - but that's not the point. More important is - you might have backed up databases and not backed up databases - set you error-limits for all three columns accordingly - upper and lower limit - and you will see that the alert will fire if you add a database or keep the SQL agent service stopped so it hops over a single backup and misses it...

Folder: C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML
File: SQLBackupDestinationCheck.vbs

'Execute:
'cscript SQLBackupDestinationCheck.vbs <0 or 1> <"bak"> <"path">
'cscript SQLBackupDestinationCheck.vbs 1 "bak" "\\SERVER.DOMAIN.LOCAL\SQLBackups\SubFolder"

Dim bSubFolders
Dim sExtension
Dim sPath

If WScript.Arguments.Count = 3 Then
bSubFolders = WScript.Arguments.Item(0)
sExtension= WScript.Arguments.Item(1)
sPath = WScript.Arguments.Item(2)
Else
Wscript.Echo "Usage: cscript SQLBackupDestinationCheck.vbs 1 ""bak"" ""\\SERVER.DOMAIN.LOCAL\SQLBackups\SubFolder"""
Wscript.Quit
End If

Dim fs
Dim objFolder
Dim objSubFolder
Dim objFile

Dim iReturnFileCount
Dim iReturnFolderCount
Dim iReturnOldestFile
Dim iReturnYoungestFile
Dim iReturnMinFilesPerFolder
Dim iReturnMaxFilesPerFolder

iReturnFileCount = -1
iReturnFolderCount = -1
iReturnOldestFile = -1
iReturnYoungestFile = 9999
iReturnMinFilesPerFolder = 9999
iReturnMaxFilesPerFolder = -1

Dim iFileDateDiff
Dim iFileCount

Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FolderExists(sPath) Then
Set objFolder = fs.GetFolder(sPath)
If bSubFolders = 1 Then
iReturnFileCount = 0
iReturnFolderCount = objFolder.SubFolders.Count
For Each objSubFolder In objFolder.SubFolders
iFileCount = 0
For Each objFile in objSubFolder.Files
If LCase(Right(objFile.Name,(1+Len(sExtension)))) = LCase("." & sExtension) Then
iFileCount = iFileCount + 1
iFileDateDiff = Datediff("d",objFile.DateLastModified,Now)
If iFileDateDiff < iReturnYoungestFile Then iReturnYoungestFile = iFileDateDiff
If iFileDateDiff > iReturnOldestFile Then iReturnOldestFile = iFileDateDiff
End If
Next
iReturnFileCount = iReturnFileCount + iFileCount
If iFileCount < iReturnMinFilesPerFolder Then iReturnMinFilesPerFolder = iFileCount
If iFileCount > iReturnMaxFilesPerFolder Then iReturnMaxFilesPerFolder = iFileCount
Next
Else
iReturnFolderCount = 1
iFileCount = 0
For Each objFile in objFolder.Files
If LCase(Right(objFile.Name,(1+Len(sExtension)))) = LCase("." & sExtension) Then
iFileCount = iFileCount + 1
iFileDateDiff = Datediff("d",objFile.DateLastModified,Now)
If iFileDateDiff < iReturnYoungestFile Then iReturnYoungestFile = iFileDateDiff
If iFileDateDiff > iReturnOldestFile Then iReturnOldestFile = iFileDateDiff
End If
Next
iReturnFileCount = iFileCount
If iFileCount < iReturnMinFilesPerFolder Then iReturnMinFilesPerFolder = iFileCount
If iFileCount > iReturnMaxFilesPerFolder Then iReturnMaxFilesPerFolder = iFileCount
End If
Set objFolder = Nothing
End if
Set fs = Nothing

WScript.echo "<prtg>"
WScript.echo "<result>"
WScript.echo "<channel>Total file count</channel><value>" & iReturnFileCount & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Total folder count</channel><value>" & iReturnFolderCount & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Oldest file found in days</channel><value>" & iReturnOldestFile & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Newest file found in days</channel><value>" & iReturnYoungestFile & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Lowest files in folder count found</channel><value>" & iReturnMinFilesPerFolder & "</value>"
WScript.echo "</result>"
WScript.echo "<result>"
WScript.echo "<channel>Highest files in folder count found</channel><value>" & iReturnMaxFilesPerFolder & "</value>"
WScript.echo "</result>"
WScript.echo "</prtg>"

Folder: C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mssql
File: SQL_Database_Full_Backups.sql

-- custom script by Florian Rossmark
USE master
DECLARE @MaxHours int
SET @MaxHours = @prtg

SELECT
-- how many databases do we have on this server?
(
SELECT COUNT(Name) FROM sys.sysdatabases WHERE name NOT LIKE 'tempdb'
) AS TotalAmountOfDatabases,

(
-- how many databases where backed up within the last n hours?
SELECT COUNT(BackedUpDBs.DatabaseName) FROM  (
SELECT sdb.Name AS DatabaseName
,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpDate
,MAX(bus.backup_finish_date) AS LastBackupDateTime
,(MAX(DATEDIFF(hh, GetDate(), bus.backup_finish_date))*-1) AS HoursAgo

FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

WHERE 1=1 -- 1=1 as dummy placeholder
AND sdb.Name NOT LIKE 'tempdb' --we ignore tempdb
AND bus.type = 'D' -- only obey DATABASE backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
AND bus.is_damaged = 0
--AND bus.recovery_model = 'FULL' -- only obey FULL backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
--no checking recovery model, this can be set in the settings per DB
AND bus.backup_finish_date IS NOT NULL -- since this can be null, we only check for those which aren't

GROUP BY sdb.Name
) AS BackedUpDBs
WHERE HoursAgo <= @MaxHours
) AS RecentlyBackupUpCount,

(
-- how many databases where NOT backed up within the last n hours?
SELECT COUNT(NotBackedUpDBs.DatabaseName) FROM  (
--SELECT NotBackedUpDBs.DatabaseName, * FROM  (
SELECT sdb.Name AS DatabaseName
,COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-') AS LastBackUpDate
,MAX(bus.backup_finish_date) AS LastBackupDateTime
,(MAX(DATEDIFF(hh, GetDate(), bus.backup_finish_date))*-1) AS HoursAgo

FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name

WHERE 1=1 -- 1=1 as dummy placeholder
AND sdb.Name NOT LIKE 'tempdb' --we ignore tempdb
AND bus.type = 'D' -- only obey DATABASE backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
--AND bus.recovery_model = 'FULL' -- only obey FULL backups https://technet.microsoft.com/en-us/library/ms186299(v=sql.110).aspx
--no checking recovery model, this can be set in the settings per DB

GROUP BY sdb.Name
) AS NotBackedUpDBs
WHERE HoursAgo > @MaxHours
) AS NOTRecentlyBackupUpCount

Posted here as well: https://www.it-admins.com/sql-database-backup-monitoring/

Regards

Florian Rossmark

www.it-admins.com

backup files folders prtg sensors sql