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

SQL Last Backup Time

Votes:

0

I am trying to find a solution to monitor the backup of an SQL database. The database is being backup up by SQL itself. And the maintenance plan can send out an email as well when the backups fails but I would like to age of the backup to be monitored by PRTG. Preferable by checking the value and comparing it to determine if the backup was done more then 60 minutes ago.

The below SQL file works and gives me the output when doing so in the Management Studio.

select max(backup_finish_date) as backup_finish_date from msdb..backupset
where database_name='blablablabla'

The result is a single cell with the last time the backup was made

2017-09-26 16:00:23.000

Running the SQL file in a Microsoft SQL V2 sensor returns a long number that I can't use. 3715430424

Is it possible to get the date and time into PRTG and compare it?

2016 backup sql

Created on Sep 26, 2017 2:32:53 PM



Best Answer

Accepted Answer

Votes:

1

Hi Luciano,

That was exactly the push in the right direction I needed.

Played around with the different options and this is what is working now SELECT DATEDIFF(second,(SELECT max(backup_finish_date) FROM msdb..backupset),(SELECT GETDATE())) as differenceInSeconds;

The SQLV2 Sensor is set to process the data table and TimeSeconds is selected.

Result is a value that is number that is representing the number of seconds ago the last backup was made.

Works like a charm. Thresholds are compared, alerts are send. Briljant.

Thanks...

Created on Sep 27, 2017 1:42:43 PM



3 Replies

Votes:

0

Hello there,
thank you for your inquiry.

This is a very good idea, and can be done with PRTG. As an "introduction" to the SQL Sensors, I encourage you to review the following:

The most important thing to keep in mind, is that the value that goes back to the channel must be numeric. PRTG can't "make sense" of the timestamp (nor can it compare it to the current time), so the query must be changed to something like:

select (now() - backup_finish_date).toSeconds

The actual syntax will depend on your SQL language (on mssql you'll have to use GETDATE() and DATEDIFF, but as soon as you can have the SQL query's result as a numerical value in seconds, you can configure the sensor in PRTG, and define the channel's unit as "TimeSeconds". The outcome/result will be a sensor that measures the "time since the last successful backup". You can define limits (3600 seconds = 60 minutes) so that the sensor goes into warning or error if the time since the last backup is over 60 minutes.

Best Regards,
Luciano Lingnau [Paessler Support]

Created on Sep 27, 2017 10:50:08 AM by Luciano Lingnau [Paessler]



Accepted Answer

Votes:

1

Hi Luciano,

That was exactly the push in the right direction I needed.

Played around with the different options and this is what is working now SELECT DATEDIFF(second,(SELECT max(backup_finish_date) FROM msdb..backupset),(SELECT GETDATE())) as differenceInSeconds;

The SQLV2 Sensor is set to process the data table and TimeSeconds is selected.

Result is a value that is number that is representing the number of seconds ago the last backup was made.

Works like a charm. Thresholds are compared, alerts are send. Briljant.

Thanks...

Created on Sep 27, 2017 1:42:43 PM



Votes:

0

Lovely! Thank you for your feedback.
I've marked your answer as Best Answer, since you've shared the code you've used to get it working. Thanks!

Best Regards,
Luciano Lingnau [Paessler Support]

Created on Sep 28, 2017 7:46:03 AM by Luciano Lingnau [Paessler]




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.