New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


SQL Database backup monitoring

Votes:

1

Your Vote:

Up

Down

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:

TotalAmountOfDatabasesTotal amount of databases of this server - this allows you not only to watch if anyone created/deleted a database on the server, it also gives you a good base-line in general
RecentlyBackupUpCounHow many databases have been backed up recently - full backup - in specified time-window
NOTRecentlyBackupUpCountHow many have not been backed up in the same time window

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

Created on May 29, 2018 4:28:03 PM by  Florian Rossmark (4,189) 3 2

Last change on Jan 15, 2019 3:10:45 PM by  Stephan Linke [Paessler Support]



9 Replies

Votes:

0

Your Vote:

Up

Down

Hi Florian,

Thanks for that! Took the freedom to format the post :)


Kind regards,
Stephan Linke, Tech Support Team

Created on May 29, 2018 6:38:48 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

perfect - sorry - I am to spoiled with WYSIWYG editors already.. in the end I just hope some people find the posting useful :-)

Regards Florian

Created on May 29, 2018 8:01:09 PM by  Florian Rossmark (4,189) 3 2



Votes:

0

Your Vote:

Up

Down

Can you give an example of how settings are setup for the custom Microsoft SQL V2 sensor? I have the script created and sensor built but cannot seem to pull any information from the master server.

Created on Jan 14, 2019 1:31:01 PM by  jhill (0)



Votes:

0

Your Vote:

Up

Down

What specific error do you get and how is the sensor configured?


PRTG Scheduler | PRTGapi | Feature Requests | WMI Issues | SNMP Issues

Kind regards,
Stephan Linke, Tech Support Team

Created on Jan 15, 2019 10:22:04 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

For both files - remove the top and bottom line:

============================= FILE END ==============================

I see that I edit the original post and remove them - it might be that's causing the issue in the first place...

Did you enable logging and check the results in the log? Did you see anything?

Regards

Florian Rossmark

www.it-admins.com

Created on Jan 15, 2019 2:54:46 PM by  Florian Rossmark (4,189) 3 2



Votes:

0

Your Vote:

Up

Down

Can sombodx please explain how to add/configure this properly including wich sensors needed etc and how they should be configured? This would be great because we need some solution exactly like this. Thanks in advance!

Created on Jul 22, 2019 8:30:12 AM by  Julian94 (0)



Votes:

1

Your Vote:

Up

Down

The full setup is actually described under https://www.it-admins.com/sql-database-backup-monitoring/ :)

Created on Jul 22, 2019 9:59:26 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

acutally it is not really described in the article - if you would look up the article it doesnt say what sensor is needed and it doesnt tell how the sensor triggers both files like the .vbs and the .sql from what I can tell you can trigger a .vbs via program sensor and a .sql via microsoft sql v2 sensor but I dont have a clue how i got this working?

Created on Jul 23, 2019 7:32:26 AM by  Julian94 (0)



Votes:

0

Your Vote:

Up

Down

Hi Julia,

It seems like you need a little help here - let me explain a few things to you so you know more about all those sensors and scripts.

There are two files:


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


This statement indicates a script with XML code. There are two script sensors, namely:

Looking at the above - only the EXE/Script Advanced Sensor can execute the VBS script that returns XML code. In other words - the SQLBackupDestinationCheck.vbs script needs to be placed in to this path C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML on your probing server and you add a EXE/Script Advanced Sensor to your probed device in PRTG and chose the previously created script from the drop-down selection.

This script expects parameters - this is explained in the article - to give you a hint - it might just look like this:

1 bak \\SERVERNAME\SHARENAME\SUBDIR1\SUBDIR2

If you path contains spaces - like this:

1 bak "\\SERVERNAME\SHARENAME\SUBDIR1\SUBDIR2 WITH SPACE"

This would cause the script to cycle through 1st level sub-directories and look for files ending in .BAK within the path provided.


The second file: 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.


This statement and the fact it is a .SQL file indicates it has to be a SQL script/sensor:

The original Microsoft SQL Sensor I think is deprecated at this point.

You place the SQL_Database_Full_Backups.sql in to this directory and create a new sensor Microsoft SQL v2 Sensor on the device with the SQL Server instance, selecting the previously created script from the drop-down menu.

You execute the script against the master database, as this is the primary SQL database that will hold the information. Further will you need a parameter that limits the hours - what you should set to 26 for a daily SQL backup check as mentioned in the article.


As described above - this is all you should need to do. There are possibly better solutions then what I did here - but I found it working and it actually really starts alerting me and sure helped me several times to investigate issues that otherwise likely would have gone by undetected until it would have been to late (like a need to restore data).

I want to mention that the blog-entry on my personal web-site clearly states to paths as well - the paths alone indicate as well very clear which sensor type is used.

Paessler / PRTG is not responsible for the scripts I posted - meaning they don't offer real support for them - and as for my part - I provide this information for free cause I once invested time in figuring out a way to monitor the details I wanted and thought it is nice to share it with others that might struggle with the same challenges. Still - it is free and while I always try to help if you contact me - you will use them at your own risk (I do not guarantee any of the data is correct) and I only reply if I have spare time to do so at all.

Having said this - you are welcome to make further suggestions or ask for help - while I hope the above explanation covered it all for this script/solution. There are many more script e.g. on my private web-site or here in the KB as well as in the SCRIPT-WORLD. Many of them are provided by end-users like me, your fellow sys-admins, many just give you the right hints so you can create your own scripts to your specific needs. I highly recommend to learn more about scripts like VBS/WSH and PowerShell, as well as SQL - cause this is will actually help you to go above and beyond with not just PRTG (and many other monitoring solutions), it helps to automate many many tasks as well.

Regards

Florian Rossmark

www.it-admins.com

Created on Jul 23, 2019 1:52:29 PM by  Florian Rossmark (4,189) 3 2



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.