The following article was originally posted here by myself: https://kb.paessler.com/en/topic/79665-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 Paessler/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.:
1 | 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:
| TotalAmountOfDatabases | Total 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 |
|---|---|
| RecentlyBackupUpCoun | How many databases have been backed up recently – full backup – in specified time-window |
| NOTRecentlyBackupUpCount | How 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | '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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | -- 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 |
