What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

Does PRTG can support Database backup jobs monitoring?

Votes:

1

Hi,

I successfully setup basic SQL performance using the SQL server sensors available in PRTG. But I have problem in setting up special DB monitors below? Please advise if PRTG can support this?

Databases - Days Since Last Backup
Databases - Unavailable
Databases - Jobs Failed
SQL - Long Running SQL
Clusters - Node Unavailable
Network - SQL Server Packets Error Rate
Monitored Server - Connection Failure

Thanks!

backups database monitoring

Created on May 6, 2011 12:36:14 AM

Last change on May 6, 2011 12:59:35 PM by  Torsten Lindner [Paessler Support]



Best Answer

Accepted Answer

Votes:

1

For the number of days since the last backup of a single database:

Step 1

Create a new stored procedure using the following code

CREATE PROCEDURE [dbo].[spLastBackup]
	@DatabaseName	nvarchar(50)
AS
BEGIN
    SELECT DATEDIFF(d, MAX(b.backup_finish_date), getdate())
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
    WHERE s.Name = @DatabaseName
END

Step 2

Add a new MSQL sensor

On your PRTG server, add a new MSQL sensor. Fill in the required fields and in the "SQL Expression" field enter

exec dbo.spLastBackup YOUR_DATABASE_NAME

In the channels tab select the value channel and enter Day as unit.

For the number of days since the last backup of all your databases:

Step 1

Create a new stored procedure using the following code

CREATE PROCEDURE [dbo].[spLastBackups]
AS
BEGIN
    SELECT  
        s.name AS Channel
	,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
	,1 as IsInt
	,'Days' as Unit
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
    GROUP BY s.name
END

Step 2

Add a new Custom XML sensor

On your PRTG server, add the Custom XML sensor SQLspXML to be downloaded from http://prtgtoolsfamily.com/?page=downloads_sensorsxml

Fill in the required parameters in the parameter section of the sensor. This sensor will produce multiple channels, one for every database on your server

Created on May 7, 2011 2:51:21 PM

Last change on Oct 20, 2014 11:00:41 AM by  Torsten Lindner [Paessler Support]



16 Replies

Votes:

0

Hello,

I'm afraid we don't have specific sensors for those. You could only try using the "normal SQL"-sensors, if you can check these items with SQL Queries.

best regards.

Created on May 6, 2011 12:59:20 PM by  Torsten Lindner [Paessler Support]



Accepted Answer

Votes:

1

For the number of days since the last backup of a single database:

Step 1

Create a new stored procedure using the following code

CREATE PROCEDURE [dbo].[spLastBackup]
	@DatabaseName	nvarchar(50)
AS
BEGIN
    SELECT DATEDIFF(d, MAX(b.backup_finish_date), getdate())
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
    WHERE s.Name = @DatabaseName
END

Step 2

Add a new MSQL sensor

On your PRTG server, add a new MSQL sensor. Fill in the required fields and in the "SQL Expression" field enter

exec dbo.spLastBackup YOUR_DATABASE_NAME

In the channels tab select the value channel and enter Day as unit.

For the number of days since the last backup of all your databases:

Step 1

Create a new stored procedure using the following code

CREATE PROCEDURE [dbo].[spLastBackups]
AS
BEGIN
    SELECT  
        s.name AS Channel
	,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
	,1 as IsInt
	,'Days' as Unit
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
    GROUP BY s.name
END

Step 2

Add a new Custom XML sensor

On your PRTG server, add the Custom XML sensor SQLspXML to be downloaded from http://prtgtoolsfamily.com/?page=downloads_sensorsxml

Fill in the required parameters in the parameter section of the sensor. This sensor will produce multiple channels, one for every database on your server

Created on May 7, 2011 2:51:21 PM

Last change on Oct 20, 2014 11:00:41 AM by  Torsten Lindner [Paessler Support]



Votes:

1

TYPO

It's GROUP BY at the end and not "GROUP BT". For all those who have no knowledge about SQL Query's and the Code didn't worked ;)

Created on Oct 20, 2014 9:27:08 AM



Votes:

0

Thanks for the note. I did change it.

Created on Oct 20, 2014 9:30:05 AM by  Torsten Lindner [Paessler Support]



Votes:

0

When I filled the required parameters, need I put entire -d -sp -s -u -p ?

Because I would monitor all databases on my MS SQL Server and I would know if is -d necessary ? And I would know if -u and -p too ?

Thank you in advance

Created on Sep 27, 2016 2:10:52 PM



Votes:

0

Hello,

this sensors is not supported by the Paessler technical support, please contact teh vendor to the sensor directly ([email protected]).

Created on Sep 28, 2016 3:31:58 PM by  [email protected]



Votes:

6

Created a SQL script that actually simply reports the amount of databases backed up with a parameter (number - hours since last backup), how many databases haven't been backed up and how many databases exist on the system.

Simply create the .SQL file for PRTG sensor and execute on the MASTER database.

Set the parameter value in the sensor to e.g. 26 hours (every night backups, plus 2 hours to finish).

Works pretty well with several SQL servers and gives us the necessary information. On top of that we have some file-sensor on the most important DBs running.

The amount of backed up databases vs. all databases vs. not recently backed up depends all on your SQL configuration. We use e.g. LiteSpeed as well and this still works pretty good. Just find out how many databases should be backed up and fix the number in the sensor with minimum and maximum limits so alerts get send out (upper and lower limit should be the same and mirror the amount of DBs expected to be backed up).

Set "RecentlyBackupUpCount" as default channel for better visibility.

Hope this example script helps some of you! I left all my notes in the script as well...

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

Regards Florian Rossmark

Created on Mar 30, 2017 11:50:57 PM



Votes:

0

And what about a PowerShell script ?

https://blogs.technet.microsoft.com/heyscriptingguy/2011/05/02/use-powershell-to-report-sql-server-backup-status/

it looks pretty easy: dir SQLSERVER:\SQL\localhost\DEFAULT\Databases | Select Name, LastBackupDate

Or with Event View =>entries 12288, 12289, 18264, 208

Seems really simple

Created on Mar 9, 2018 12:28:57 PM



Votes:

0

Hello

the correct log entries are:

Event ID Symbolic Name Text Notes 12288 DTS_MSG_PACKAGESTART Package "" started. The package has started to run. 12289 DTS_MSG_PACKAGESUCCESS Package "" finished successfully. The package successfully ran and is no longer running. 12290 DTS_MSG_PACKAGECANCEL Package "%1!s!" has been cancelled. The package is no longer running because the package was canceled. 12291 DTS_MSG_PACKAGEFAILURE Package "" failed. The package could not run successfully and stopped running.

Top MS link => https://docs.microsoft.com/en-us/sql/integration-services/performance/events-logged-by-an-integration-services-package

Simply check for an 12291 event.

Created on Mar 13, 2018 8:53:14 AM



Votes:

0

Hey,

I've modified the script above to exclude system databases as per below:

SELECT s.name AS Channel ,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value ,1 as IsInt ,'Days' as Unit FROM sys.sysdatabases s LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = s.name WHERE s.name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource') GROUP BY s.name

Bit of a dirty way to exclude system DBs, but it works fine and parses all the channels into a single PRTG sensor. Great, nice and efficient so far.

However, my question is, how do I get the sensor to go into warning or alarm state when the 'Days' is greater than 1 (or any other number I might choose).

I can configure notifications for each channel individually, but this is impractical as I have anywhere up to 50 user databases I need to keep an eye on. It also doesn't show the sensor in warning or alarm state on the map/interface.

Any ideas?

Created on Jan 9, 2019 4:11:16 AM



Votes:

1

Hello Cloud_0013,

The way to go is to enable limits for each channel and set up a max warning or max error limit at 1 day. Next you will need to add a notification trigger to the sensor.

How to Set Channel Limits
PRTG Manual: Sensor Notification Triggers Settings

Setting multiple limits in one batch, the Channel Limits tool might be of help.


Sensors | Multi Channel Sensors | Tools | Notifications

Kind regards,

[[http://prtgtoolsfamily.com]] PRTGToolsFamily

Created on Jan 9, 2019 12:42:08 PM



Votes:

1

With a little help from PRTGToolsFamily (thanks, you're a legend) I've modified the SQL stored proc as follows:

SELECT s.name AS Channel ,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value ,1 as IsInt ,'Days' as Unit ,1 as limitMode ,0 as limitMinWarning ,0 as limitMinError ,1 as limitMaxWarning ,2 as limitMaxError ,'Warning! Backup(s) have not completed for more than 1 consective days' as LimitWarningMsg ,'ERROR! Backup(s) have not completed for more than 2 consective days' as LimitErrorMsg FROM sys.sysdatabases s LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = s.name WHERE s.name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource') GROUP BY s.name

This enables error and warning alarm limits and messages by default on all channels that come through on the results.

The trick is the limit settings are only loaded the first time the sensor scans, so you'd have to delete and re-add the sensor if you we're altering an existing one.

Another tip is that you after you create the Stored Proc, you have to add the user to it (will be in the 'Master' Database if you use the script above) with EXECUTE permissions, even if it's the sa user.

Created on Jan 11, 2019 1:03:44 AM

Last change on Jan 14, 2019 5:26:29 AM by  Sven Roggenhofer [Paessler Technical Support]



Votes:

1

To add a few more tips:

  • If you do not want to use all limits (like the limitMinWarning and limitMinError) you can simply omit then, there is no need to set them to 0.
  • Altering the limit settings afterwards can also be done manual if you do not want to create the sensor anew, manual alterations will overrule the ones at creation time.
  • After sensor creation it is even possible to remove the limit settings from your SQL result set. This will reduce the overhead but will also require a heads-up when you recreate the sensor.

Sensors | Multi Channel Sensors | Tools | Notifications

Kind regards,

[[http://prtgtoolsfamily.com]] PRTGToolsFamily

Created on Jan 11, 2019 7:43:53 AM



Votes:

0

As an FYI I have made further refinements to the stored proc.

In my environment we occasional decommission databases as clients cancels services with us, in this scenario we take the database offline for a 90 day grace period before we delete and purge the data completely. These offline databases were triggering 'false errors' on this sensor, as our backup job is set to exclude offline databases.

The below refined SQL script will exclude offline databases from the results, removing false triggers for databases that are offline:

    SELECT  
        s.name AS Channel
       ,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
       ,1 as IsInt
       ,'Days' as Unit
	   ,1 as limitMode
	   ,0 as limitMinWarning
	   ,0 as limitMinError
	   ,1 as limitMaxWarning
	   ,2 as limitMaxError
	   ,'Warning! Backup(s) have not completed for more than 1 consective days' as LimitWarningMsg
	   ,'ERROR! Backup(s) have not completed for more than 2 consective days' as LimitErrorMsg
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
	WHERE s.name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource', 'distribution', 'ReportServer', 'ReportServerTempDB') 
	AND status & 512 <> 512
    GROUP BY s.name

The offline databases then display in the PRTG interface as greyed out with 'No Data', and will not trigger Warnings/Alarms. The only way I can figure out to remove them completely is to delete and recreate the sensor from scratch, which is potentially undesirable as you lose all the history.

Cheers!

Created on Jun 21, 2019 12:20:31 AM



Votes:

0

Hello, I used the script above using hours instead of days, I see the correct results on PRTG interface but the limit seems not working for me.

For example now I see 19 on interface but there isn't any warning or errors.

SELECT  
        s.name AS Channel
       ,DATEDIFF(hour, MAX(b.backup_finish_date), getdate()) as Value
       ,1 as IsInt
       ,'Hours' as Unit
	   ,1 as limitMode
	   ,0 as limitMinWarning
	   ,0 as limitMinError
	   ,12 as limitMaxWarning
	   ,18 as limitMaxError
	   ,'Warning! Backup(s) have not completed for more than 1 consective days' as LimitWarningMsg
	   ,'ERROR! Backup(s) have not completed for more than 2 consective days' as LimitErrorMsg
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
        ON b.database_name = s.name
	WHERE s.name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource', 'distribution', 'ReportServer', 'ReportServerTempDB') 
	AND status & 512 <> 512
    GROUP BY s.name

PRTG wrote : Altering the limit settings afterwards can also be done manual if you do not want to create the sensor anew, manual alterations will overrule the ones at creation time.

How do it ?

Thanks

Created on Dec 30, 2020 3:12:08 PM



Votes:

0

Hello

The stored procedure i'm using works fine with SQL studio:

############################################################
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spLastBackups]    Script Date: 15.09.2023 10:35:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spLastBackups]
AS
BEGIN
    SELECT  
        s.name AS Channel
	,DATEDIFF(d, MAX(b.backup_finish_date), getdate()) as Value
	,1 as IsInt
	,'Days' as Unit
	,1 as limitMode
	,0 as limitMinWarning
	,0 as limitMinError
	,1 as limitMaxWarning
	,2 as limitMaxError
	,'Warning! Backup(s) have not completed for more than 1 consective days' as LimitWarningMsg
	,'ERROR! Backup(s) have not completed for more than 2 consective days' as LimitErrorMsg
    FROM sys.sysdatabases s 
    LEFT OUTER JOIN msdb.dbo.backupset b
    ON b.database_name = s.name
 	WHERE s.name NOT IN ('model', 'tempdb', 'msdb', 'Resource')
	GROUP BY s.name
END
###################################################################

I am trying to geet this working, but when i run (h****a\**_prtg is the service user for PRTG)

C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\EXEXML>SQLspXML.exe -s=s*****1.h***a.**** -d=master -sp=spLastBackups -u=h****a\**_prtg -p=***
20.4.1.9
Checking .NET Framework version.
Minmal required is... 4.7
Installed version is... 4.7.2 Ok
Impersonating user h****a\*****_prtg

and it stays blocked here...

Any idea or suggestion?

Thanks a lot for your help =)

Created on Sep 15, 2023 8:44:18 AM

Last change on Sep 18, 2023 7:18:18 AM by  Felix Wiesneth [Paessler Support]




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.