sql

Windows 11 and SQL (Express) issues

Due to a change on how Windows 11 presents the disk sector size, you can have issues with SQL or SQL Express after your upgrade or even on brand new installations.

SQL might just fail to start after an upgrade, with the Event Viewer Application Log Error 1000 similar to the below one:

Faulting application name: sqlservr.exe, version: 2019.150.2080.9, time stamp: 0x5fa6009b
Faulting module name: sqllang.dll, version: 2019.150.2080.9, time stamp: 0x5fa60189
Exception code: 0xc0000005
Fault offset: 0x0000000000361bf5
Faulting process id: 0x670c
Faulting application start time: 0x01d846287c712ec8
Faulting application path: C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS_2019\MSSQL\Binn\sqlservr.exe
Faulting module path: C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS_2019\MSSQL\Binn\sqllang.dll
Report Id: 10227658-21ca-4bd0-9121-9bf328003574
Faulting package full name:
Faulting package-relative application ID:

Faulting application name: sqlservr.exe, version: 2019.150.2080.9, time stamp: 0x5fa6009b

Faulting module name: sqllang.dll, version: 2019.150.2080.9, time stamp: 0x5fa60189

Exception code: 0xc0000005

Fault offset: 0x0000000000361bf5

Faulting process id: 0x670c

Faulting application start time: 0x01d846287c712ec8

Faulting application path: C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS_2019\MSSQL\Binn\sqlservr.exe

Faulting module path: C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS_2019\MSSQL\Binn\sqllang.dll

Report Id: 10227658-21ca-4bd0-9121-9bf328003574

Faulting package full name:

Faulting package-relative application ID:

This is especially true for Samsung SSD 980 – be aware – the SSD 980 Pro does not have this issue, just the SSD 980. There are other OEM versions of it that have the same issue and actually a bunch of other disks.

The root cause is that the devices report the true sector size, what causes SQL to fail. This is still true with SQL Express 2019 – earlier versions as well.

As described in this Microsoft article, you can add a registry key and reboot to make Windows 11 behave like Windows 10 and earlier Windows versions.

Of course, alternative you can either install SQL on another disk drive or replace the drive with one that does not have these compatibility issues.

It remains unclear if there will be updates to this in the future from either Microsoft of the disk vendors like Samsung in the future. For now, this simple registry adjustment fixes the issue.

New-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" -Name   "ForcedPhysicalSectorSizeInBytes" -PropertyType MultiString -Force -Value "* 4095"

1	New-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" -Name "ForcedPhysicalSectorSizeInBytes" -PropertyType MultiString -Force -Value "* 4095"

Reboot after the registry adjustment for the change to take effect.

Summarize SQL server database file size information as a single row

The SQL script below will show you the summarized database file size and database log file sizes on the current database. This can be helpful to monitor the actual file sizes in a single row / line.

This script was also posted in the Paessler PRTG KB, where the initial request for this came up. Posting it here cause I think it can become useful for some. The initial code used and modified is from Tri Effendi SS of stack exchange.

You will need to adjust the USE statement in the first line to the database you want to gather the information from.

USE [master]

SELECT 
	MYROWS.DATABASENAME, 
	SUM(MYROWS.FILESIZE) AS DATA_FILESIZE, SUM(MYROWS.USEDSPACE) AS DATA_USEDSPACE, SUM(MYROWS.FREESPACE) AS DATA_FREESPACE, SUM(MYROWS.[FREESPACE_%]) AS DATA_FREESPACE_PERCENT,
	SUM(MYLOGS.FILESIZE) AS LOG_FILESIZE, SUM(MYLOGS.USEDSPACE) AS LOG_USEDSPACE, SUM(MYLOGS.FREESPACE) AS LOG_FREESPACE, SUM(MYLOGS.[FREESPACE_%]) AS LOG_FREESPACE_PERCENT 
FROM
	(
		SELECT 
			DB_NAME() AS DATABASENAME
			,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024
			,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024
			,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024
			,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
		FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
		WHERE A.type_desc LIKE 'ROWS'
	) MYROWS 
	LEFT JOIN 
	(
		SELECT 
			DB_NAME() AS DATABASENAME
			,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024
			,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024
			,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024
			,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
		FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
		WHERE A.type_desc LIKE 'LOG'
	) MYLOGS
	ON MYROWS.DATABASENAME = MYROWS.DATABASENAME
GROUP BY 
	MYROWS.DATABASENAME

USE [master]

SELECT

MYROWS.DATABASENAME,

SUM(MYROWS.FILESIZE) AS DATA_FILESIZE, SUM(MYROWS.USEDSPACE) AS DATA_USEDSPACE, SUM(MYROWS.FREESPACE) AS DATA_FREESPACE, SUM(MYROWS.[FREESPACE_%]) AS DATA_FREESPACE_PERCENT,

SUM(MYLOGS.FILESIZE) AS LOG_FILESIZE, SUM(MYLOGS.USEDSPACE) AS LOG_USEDSPACE, SUM(MYLOGS.FREESPACE) AS LOG_FREESPACE, SUM(MYLOGS.[FREESPACE_%]) AS LOG_FREESPACE_PERCENT

FROM

(

SELECT

DB_NAME() AS DATABASENAME

,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024

,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024

,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024

,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)

FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id

WHERE A.type_desc LIKE 'ROWS'

) MYROWS

LEFT JOIN

(

SELECT

DB_NAME() AS DATABASENAME

,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024

,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024

,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024

,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)

FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id

WHERE A.type_desc LIKE 'LOG'

) MYLOGS

ON MYROWS.DATABASENAME = MYROWS.DATABASENAME

GROUP BY

MYROWS.DATABASENAME

May 21, 2019 by Florian Rossmark monitoring prtg server

Raspberry PI and Microsoft SQL databases

Raspberry PI can read and write on a Microsoft SQL server database.

In order to accomplish this you follow the instructions here: http://pymssql.org/en/stable/index.html

To summarize it in a nutshell, here is what you need to do:

apt-get install freetds-dev
pip install pymssql

Update: Above information is for a Raspberry 2 – Raspberry 3 needs the below information as far as I know:

sudo apt-get install freetds-dev
sudo pip3 install cython
sudo pip3 install pymssql

Personally I had issues getting this to work in Python 3.x so I tested it in Python 2.x and it was working fine. The issue was simply that the module “pymssql” could not be found and therefor the IMPORT line already failed in the Python script. It should be a rather easy fix – like copying the files to the Python 3 modules folder, but as of now I did not have the time to investigate this further – as I was fine using Python 2 in my specific situation.

Here is a sample script

import pymssql

server = "mySQLservername"
user = "myuser"
password = "mypassword"
DB = "mydatabase"

conn = pymssql.connect(server, user, password, DB)
cur = conn.cursor()
cur.execute('SELECT * FROM MyTable')
  for row in cur:
    #print(row['id'])
    print(row[0])
conn.close

import pymssql

server = "mySQLservername"

user = "myuser"

password = "mypassword"

DB = "mydatabase"

conn = pymssql.connect(server, user, password, DB)

cur = conn.cursor()

cur.execute('SELECT * FROM MyTable')

for row in cur:

#print(row['id'])

print(row[0])

conn.close

The example I tested used a SQL server user account. The documentation of PyMSSQL talks about the possibility to use Windows Authentication as well.

As for asking Google about this – there is a lot of confusion information out there – the top ranked posts aren’t really helpful, so I thought I just post it again hoping someone finds this helpful.

January 31, 2019 by Florian Rossmark configuration solutions

SQL Express SQLState 08001 and Error 17

One of the challenges especially with SQL Express is that you need to enable some protocols on the network level first in order to connect to it. You might see an error message the one below when you try to connect to SQL – stating SQLState 08001 and Error 17.

In order to resolve this, you need to enable named pipes and TCP in the SQL Server Configuration Manager that was installed by default on your system. See the image below on how it should look like. Please note that you need to restart the SQL service in order for those changes to take effect.

Please note – there might be a need of additional configuration like the Windows Firewall or other parameters, the above just addresses a rather common issue.

September 10, 2018 by Florian Rossmark configuration server solutions

Microsoft RADIUS / NPS SQL logging

An issue or question I see again and again – proper RADIUS logging with Microsoft NPS / Network Policy Server.

Let’s guide you through a few steps

Install a Microsoft SQL or if not available SQL Express
1. be aware – SQL Express has very tight database size limits and no SQL Agent – this might be an issue
Create a new database via SQL Management Studio in the SQL server
1. name it e.g. RADIUSLogging
run the SQL script from this Microsoft website in a new query window against this database (make sure it is not run against any other database by accident)
1. you could add a line like USE RADIUSLogging to prevent this – in the very top…
configure your RADIUS server to log to this SQL server and database
make sure you have fail-over logging to a text-file – to avoid issues in case your SQL DB grew to big or was not reachable for any reason
1. decide in the text-file configuration if you want to deny access if there is an issue or if you still want to proceed with the logon

Now you have RADIUS logging the information to a SQL database – actually a single table – and you can dig around in it. The IT-Assets database provides a front-end example for this – you don’t need to use it – but it might be of help – see here.

To interpret all those columns and values – look at the following links for additional information:

You will face the issue that the database will grow rapidly – depending on how many requests go to your RADIUS system etc… Keep an close eye on it – use a monitoring software like Paessler / PRTG to monitor the size and keep in mind that SQL Express might have size limits like 10 GB. The full version of Microsoft SQL has no such limits and further can you use SQL Agent to execute tasks. The following script can help you purging data from the RADIUS database to keep its size under control. You can use SQL Agent (not in SQL Express) to run it automatically or if you use SQL Express either run it manually or with another solution somehow automatically against the database delete older entries.

The script actually will purge data older then 14 days – you can adjust the days to your liking / needs.

DELETE FROM dbo.accounting_data
where datediff(day,dbo.accounting_data.timestamp,getdate()) >14

1 2	DELETE FROM dbo.accounting_data where datediff(day,dbo.accounting_data.timestamp,getdate()) >14

September 6, 2018 by Florian Rossmark configuration recommendations security server solutions

Script based SQL Express backups

SQL Express is widely used but has huge downside, there is no SQL Agent available. Even Windows internal databases, especially WSUS / Windows Update Services / Microsoft Update Services are in the end SQL Express like databases that do not have a SQL Agent.

Now, you can have central SQL Servers with Agents have them backed up – and I recommend on doing so if possible. But for the many times this is not possible, you will need to find another way to create those nice little .BAK files for SQL internal backups aka. SQL Maintenance Plan Backups. To work around this issue, I once wrote a script that automates this for each database found on a specific SQL server. It creates the backups via SQLCMD commands and even does a clean up of obsolete files (files older than x days), almost like SQL Maintenance Plans do it.

The script is divided in to a .CMD file that executes the actual backup and where you set the configuration/parameters and a .VBS file that is controlled by the actual .CMD script and will perform the backup cleanup. In the end you can have the .CMD send a email report – I used the SMTPSEND program from Michael Kocum (https://www.dataenter.com/download.asp) for this since I already ad it flying around – you could replace the mail send option with another prepared SMTPSEND client, a VBS script or just remove it completely.

Adjusting the settings / parameter:

This is all done in the SQLBACKUP.CMD file – the header section pretty much will explain all you need to know, from SQL Server to SQL-User and Password over Mail-Server to recipients.

If you want to execute the SQLBackups as the Windows-User that is executing the script, you need to exchange the REM (remarks) for the following two lines further down in the scripts. I apologize for the inconvenience, this is a old script I never updated to have those settings in the header (more automated), I always just changed the lines.

REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication
SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" &gt; "%DBList%"
REM the following line uses Windows User authentication - remove the REM if you want to use this instead
REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" &gt; "%DBList%"

REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication

SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"

REM the following line uses Windows User authentication - remove the REM if you want to use this instead

REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"

Everything else should be rather easy. Of course you will need sufficient access rights to the SQL databases and your destination backup folder. The task-scheduler might work best if you execute the script with “cmd /c c:\scripts\sqlbackup.cmd” (change the path as you need it) and set the working directory / startup folder right. It might help to execute the task with elevated rights etc. – all depending on your systems configuration.

Below are the two scripts – I hope this helps some of you. The generated .BAK files can simply be restored in SQL services via the GUI cause they are native SQL backup files.

@ECHO OFF
SETLOCAL

REM Variables - please adjust
REM =============================================================================================
REM Server or Server\Instance
SET SQLServer=SERVER\INSTANANCEifNEEDED
REM local Path or UNC Path (must be accessible under the executing user!)
SET SQLBackupPath=D:\Backup\SubFolderName----\\SERVER\SHARE\FOLDER-----EitherLocalOrUNC
REM max age the files can be in days before they get deleted
SET SQLBackupMaxAge=1 
SET SQLUser=sa
SET SQLPW=
SET MailServer=relay.domain.local
SET MailServerPort=25
SET MailFrom=sqlbackup@domain.com
SET MailTo=backups@domain.com
SET MailSubject=SQL Express Backup (INSTANCENAME/DESCRIPTION)
REM =============================================================================================

REM Script-System-Variables - do not change after this point!
SET ScriptLog=%~dp0\SQLBackup_Script_Log.txt
REM Running Script 
DEL %ScriptLog% 
ECHO Script-Log Date: %Date% / Time: %Time% >> %ScriptLog%
ECHO Script-Parameter: >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%
ECHO SQLServer: %SQLServer% >> %ScriptLog%
ECHO SQLBackupPath: %SQLBackupPath% >> %ScriptLog%
ECHO SQLBackupMaxAge: %SQLBackupMaxAge% >> %ScriptLog%
ECHO SQLUser: %SQLUser% >> %ScriptLog%
ECHO SQLPW: %SQLPW% >> %ScriptLog%
ECHO MailServer: %MailServer% >> %ScriptLog%
ECHO MailServerPort: %MailServerPort% >> %ScriptLog%
ECHO MailFrom: %MailFrom% >> %ScriptLog%
ECHO MailTo: %MailTo% >> %ScriptLog%
ECHO MailSubject: %MailSubject% >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%
ECHO Script executing: >> %ScriptLog%

ECHO Delete old Backup Info File >> %ScriptLog%
DEL %SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ECHO Get date in format YYYY-MM-DD (assumes the locale is the United States) >> %ScriptLog%
FOR /F "tokens=1,2,3,4 delims=. " %%A IN ('Date /T') DO SET NowDate=%%C-%%B-%%A >> %ScriptLog%
FOR /F "tokens=1,2 delims=: " %%A IN ('Time /T') DO SET NowDate=%NowDate%_%%A-%%B >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ECHO Build a list of databases to backup >> %ScriptLog%
SET DBList=%SQLBackupPath%\SQLDBList.txt
REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication
SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"
REM the following line uses Windows User authentication - remove the REM if you want to use this instead
REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"
ECHO =========================================================================== >> %ScriptLog%

ECHO Backup each database, prepending the date to the filename >> %ScriptLog%
FOR /F "tokens=*" %%I IN (%DBList%) DO (
ECHO Backing up database: %%I >> %ScriptLog%
ECHO Backing up database: %%I >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO Destination: %SQLBackupPath%\%NowDate%_%%I.bak >>%SQLBackupPath%\SQLBackupStatus.txt
SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -Q "BACKUP DATABASE [%%I] TO Disk='%SQLBackupPath%\ %NowDate%_%%I.bak'" >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt
)
ECHO =========================================================================== >> %ScriptLog%
ECHO Clean up the temp file >> %ScriptLog%
IF EXIST "%DBList%" DEL /F /Q "%DBList%" >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ECHO Old Backups Clean Up >> %ScriptLog%
CSCRIPT %~dp0\SQLBackup.vbs %SQLBackupMaxAge% "%SQLBackupPath%" >> %ScriptLog%
ECHO SQL Backup CleanUp executed >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO Currently available Backup-Files: >>%SQLBackupPath%\SQLBackupStatus.txt
DIR %SQLBackupPath% >>%SQLBackupPath%\SQLBackupStatus.txt
ECHO =========================================================================== >> %ScriptLog%

ECHO Send Info Mail >> %ScriptLog%
%~dp0\SMTPSEND -f%MailFrom% -t%MailTo% -h%MailServer% -p%MailServerPort% -s%MailSubject% -i%SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ENDLOCAL

@ECHO OFF

SETLOCAL

REM Variables - please adjust

REM =============================================================================================

REM Server or Server\Instance

SET SQLServer=SERVER\INSTANANCEifNEEDED

REM local Path or UNC Path (must be accessible under the executing user!)

SET SQLBackupPath=D:\Backup\SubFolderName----\\SERVER\SHARE\FOLDER-----EitherLocalOrUNC

REM max age the files can be in days before they get deleted

SET SQLBackupMaxAge=1

SET SQLUser=sa

SET SQLPW=

SET MailServer=relay.domain.local

SET MailServerPort=25

SET MailFrom=sqlbackup@domain.com

SET MailTo=backups@domain.com

SET MailSubject=SQL Express Backup (INSTANCENAME/DESCRIPTION)

REM =============================================================================================

REM Script-System-Variables - do not change after this point!

SET ScriptLog=%~dp0\SQLBackup_Script_Log.txt

REM Running Script

DEL %ScriptLog%

ECHO Script-Log Date: %Date% / Time: %Time% >> %ScriptLog%

ECHO Script-Parameter: >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO SQLServer: %SQLServer% >> %ScriptLog%

ECHO SQLBackupPath: %SQLBackupPath% >> %ScriptLog%

ECHO SQLBackupMaxAge: %SQLBackupMaxAge% >> %ScriptLog%

ECHO SQLUser: %SQLUser% >> %ScriptLog%

ECHO SQLPW: %SQLPW% >> %ScriptLog%

ECHO MailServer: %MailServer% >> %ScriptLog%

ECHO MailServerPort: %MailServerPort% >> %ScriptLog%

ECHO MailFrom: %MailFrom% >> %ScriptLog%

ECHO MailTo: %MailTo% >> %ScriptLog%

ECHO MailSubject: %MailSubject% >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO Script executing: >> %ScriptLog%

ECHO Delete old Backup Info File >> %ScriptLog%

DEL %SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO Get date in format YYYY-MM-DD (assumes the locale is the United States) >> %ScriptLog%

FOR /F "tokens=1,2,3,4 delims=. " %%A IN ('Date /T') DO SET NowDate=%%C-%%B-%%A >> %ScriptLog%

FOR /F "tokens=1,2 delims=: " %%A IN ('Time /T') DO SET NowDate=%NowDate%_%%A-%%B >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO Build a list of databases to backup >> %ScriptLog%

SET DBList=%SQLBackupPath%\SQLDBList.txt

REM the following line user SQL User / PW - SQL authentication - REM it if you want to switch to Windows User authentication

SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"

REM the following line uses Windows User authentication - remove the REM if you want to use this instead

REM SqlCmd -S %SQLServer% -h-1 -W -Q "SET NoCount ON; SELECT Name FROM master.dbo.sysDatabases WHERE [Name] NOT IN ('tempdb')" > "%DBList%"

ECHO =========================================================================== >> %ScriptLog%

ECHO Backup each database, prepending the date to the filename >> %ScriptLog%

FOR /F "tokens=*" %%I IN (%DBList%) DO (

ECHO Backing up database: %%I >> %ScriptLog%

ECHO Backing up database: %%I >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO Destination: %SQLBackupPath%\%NowDate%_%%I.bak >>%SQLBackupPath%\SQLBackupStatus.txt

SqlCmd -U %SQLUser% -P %SQLPW% -S %SQLServer% -Q "BACKUP DATABASE [%%I] TO Disk='%SQLBackupPath%\ %NowDate%_%%I.bak'" >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt

)

ECHO =========================================================================== >> %ScriptLog%

ECHO Clean up the temp file >> %ScriptLog%

IF EXIST "%DBList%" DEL /F /Q "%DBList%" >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ECHO Old Backups Clean Up >> %ScriptLog%

CSCRIPT %~dp0\SQLBackup.vbs %SQLBackupMaxAge% "%SQLBackupPath%" >> %ScriptLog%

ECHO SQL Backup CleanUp executed >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO. >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO Currently available Backup-Files: >>%SQLBackupPath%\SQLBackupStatus.txt

DIR %SQLBackupPath% >>%SQLBackupPath%\SQLBackupStatus.txt

ECHO =========================================================================== >> %ScriptLog%

ECHO Send Info Mail >> %ScriptLog%

%~dp0\SMTPSEND -f%MailFrom% -t%MailTo% -h%MailServer% -p%MailServerPort% -s%MailSubject% -i%SQLBackupPath%\SQLBackupStatus.txt >> %ScriptLog%

ECHO =========================================================================== >> %ScriptLog%

ENDLOCAL

Set objArgs = WScript.Arguments

If objArgs.Count > 0 Then

Dim strPath
Dim intAge

intAge = objArgs(0)
strPath = objArgs(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile in objFolder.Files
If DateDiff("d", objFile.DateLastModified, Now()) > Cint(intAge) Then
On Error Resume Next
objFile.Delete
On Error Goto 0
End If
Next

End If

Set objArgs = WScript.Arguments

If objArgs.Count > 0 Then

Dim strPath

Dim intAge

intAge = objArgs(0)

strPath = objArgs(1)

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder(strPath)

For Each objFile in objFolder.Files

If DateDiff("d", objFile.DateLastModified, Now()) > Cint(intAge) Then

On Error Resume Next

objFile.Delete

On Error Goto 0

End If

June 19, 2018 by Florian Rossmark recommendations scripts server

PRTG – Veritas Backup Exec monitoring

This was originally posted by myself here: https://kb.paessler.com/en/topic/58233-symantec-backupexec-monitoring#reply-262024

We monitor backups a little bit more advanced – I thought I should share this knowledge as well…

Single Job Monitoring: Monitor a single job and it’s results – allows you configure the Job-Name with a PRTG filter value in the SQL sensor. The results will include various values – most notable are:

FinalJobStatus (as text)
TotalDataSizeBytes
TotalNumberOfDirectories
TotalNumberOfFiles
TotalRateMBMin

In order to implement this sensor, add a SQLv2 Sensor and configure like this:

Database: BEDB
Instance Name: BKUPEXEC (in most cases)
Use input parameter: specify the exact job name
DBNull = Error

Channels: Most channels are rather simple to configure, they are counters, SpeedDisk or BytesDisk – as PRTG has those channel types integrated already. The special channel is FINALJOBSTATUS – in order to have this working you will need the “backupexec.jobstatus.ovl” file in your %programfilesx86%\PRTG…\Lookups directory – see below for the file.

SQL Script for single job monitoring:

SELECT TOP 1
	J.JobName,
	H.TotalDataSizeBytes, H.TotalRateMBMin, H.TotalNumberOfFiles, H.TotalNumberOfDirectories,
	H.FinalJobStatus,
	H.ActualStartTime, H.EndTime, H.ElapsedTimeSeconds

FROM Jobs J 
	LEFT JOIN TaskDefinition T ON T.JobDefinitionID = J.JobDefinitionID
	LEFT JOIN Schedule S ON S.ScheduleId = T.ScheduleID
	LEFT JOIN JobHistorySummary H ON H.JobID = J.JobID

WHERE S.SystemId IS NOT NULL
	AND H.ActualStartTime > GETDATE()-8 --adjust this value if needed, 8 helps for the last 8 days.. weekly backups (full backups)
	AND J.JobName LIKE @prtg

ORDER BY H.ActualStartTime DESC

SELECT TOP 1

J.JobName,

H.TotalDataSizeBytes, H.TotalRateMBMin, H.TotalNumberOfFiles, H.TotalNumberOfDirectories,

H.FinalJobStatus,

H.ActualStartTime, H.EndTime, H.ElapsedTimeSeconds

FROM Jobs J

LEFT JOIN TaskDefinition T ON T.JobDefinitionID = J.JobDefinitionID

LEFT JOIN Schedule S ON S.ScheduleId = T.ScheduleID

LEFT JOIN JobHistorySummary H ON H.JobID = J.JobID

WHERE S.SystemId IS NOT NULL

AND H.ActualStartTime > GETDATE()-8 --adjust this value if needed, 8 helps for the last 8 days.. weekly backups (full backups)

AND J.JobName LIKE @prtg

ORDER BY H.ActualStartTime DESC

The backupexec.jobstatus.ovl file:

<?xml version="1.0" encoding="UTF-8"?>
<ValueLookup id="backupexec.jobstatus" desiredValue="1" undefinedState="error" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd">
   <Lookups>
	<SingleInt state="warning" value="1">
		Job Cancelled
	</SingleInt>
	<SingleInt state="Ok" value="2">
		Job Completed
	</SingleInt>
	<SingleInt state="warning" value="3">
		Job Exception
	</SingleInt>
	<SingleInt state="warning" value="4">
		Job Dispatched
	</SingleInt>
	<SingleInt state="warning" value="5">
		Job On Hold
	</SingleInt>
	<SingleInt state="error" value="6">
		Job Error
	</SingleInt>
	<SingleInt state="error" value="7">
		Job Invalid Schedule
	</SingleInt>
	<SingleInt state="error" value="8">
		Job Invalid Time Window
	</SingleInt>
	<SingleInt state="error" value="9">
		unknown status code (9)
	</SingleInt>
	<SingleInt state="error" value="10">
		Job Missed
	</SingleInt>
	<SingleInt state="warning" value="11">
		Job Paused
	</SingleInt>
	<SingleInt state="warning" value="12">
		Job Pending
	</SingleInt>
	<SingleInt state="warning" value="13">
		Job Recovered
	</SingleInt>
	<SingleInt state="error" value="14">
		Job Resource Disabled
	</SingleInt>
	<SingleInt state="warning" value="15">
		Job Resumed
	</SingleInt>
	<SingleInt state="Ok" value="16">
		Job Running
	</SingleInt>
	<SingleInt state="Ok" value="17">
		Job Ready
	</SingleInt>
	<SingleInt state="Ok" value="18">
		Job Scheduled
	</SingleInt>
	<SingleInt state="Ok" value="19">
		Job Success
	</SingleInt>
	<SingleInt state="warning" value="20">
		Job Superceded
	</SingleInt>
	<SingleInt state="error" value="21">
		Job Abort (Time)
	</SingleInt>
	<SingleInt state="warning" value="22">
		Job To Be Scheduled
	</SingleInt>
	<SingleInt state="warning" value="23">
		Job Linked to Another Job
	</SingleInt>
	<SingleInt state="error" value="24">
		unknown status value (24)
	</SingleInt>
	<SingleInt state="error" value="25">
		Job Blocked by Template Rule
	</SingleInt>	
   </Lookups>
</ValueLookup>

<?xml version="1.0" encoding="UTF-8"?>

Job Cancelled

</SingleInt>

Job Completed

</SingleInt>

Job Exception

</SingleInt>

Job Dispatched

</SingleInt>

Job On Hold

</SingleInt>

Job Error

</SingleInt>

Job Invalid Schedule

</SingleInt>

Job Invalid Time Window

</SingleInt>

unknown status code (9)

</SingleInt>

Job Missed

</SingleInt>

Job Paused

</SingleInt>

Job Pending

</SingleInt>

Job Recovered

</SingleInt>

Job Resource Disabled

</SingleInt>

Job Resumed

</SingleInt>

Job Running

</SingleInt>

Job Ready

</SingleInt>

Job Scheduled

</SingleInt>

Job Success

</SingleInt>

Job Superceded

</SingleInt>

Job Abort (Time)

</SingleInt>

Job To Be Scheduled

</SingleInt>

Job Linked to Another Job

</SingleInt>

unknown status value (24)

</SingleInt>

Job Blocked by Template Rule

</SingleInt>

</Lookups>

</ValueLookup>

Another SQL script we use is the one below – this actually approaches the whole monitoring more in an overview – it still depends on the JobHistory table, meaning, the job must have been running.. in theory you could work around this and actually get information on the scheduler etc.. the script below is a pure example.

DECLARE @JobFilter AS VARCHAR(500)
DECLARE @TimeWindow AS Integer

SET @JobFilter = '%-FULL' -- we only want to see jobs that with "-FULL" at the end of the job name / use @PRGT if you want to control it in PRTG
SET @TimeWindow = 7 --7 days in each direction for history and scheduled jobs

SELECT 
	COUNT(J.JobName) AS TotalJobs,
	SUM(CASE WHEN H.IsJobActive = 1 THEN 1 ELSE 0 END) AS ActiveJobs,

--	SUM(CASE WHEN H.FinalJobStatus = 1 THEN 1 ELSE 0 END) AS FinalStatus_JobCancelled, 
--	SUM(CASE WHEN H.FinalJobStatus = 2 THEN 1 ELSE 0 END) AS FinalStatus_JobCompleted, 
--	SUM(CASE WHEN H.FinalJobStatus = 3 THEN 1 ELSE 0 END) AS FinalStatus_JobException, 
--	SUM(CASE WHEN H.FinalJobStatus = 4 THEN 1 ELSE 0 END) AS FinalStatus_JobDispatched,
--	SUM(CASE WHEN H.FinalJobStatus = 5 THEN 1 ELSE 0 END) AS FinalStatus_JobOnHold,
	SUM(CASE WHEN H.FinalJobStatus = 6 THEN 1 ELSE 0 END) AS FinalStatus_JobError, 
--	SUM(CASE WHEN H.FinalJobStatus = 7 THEN 1 ELSE 0 END) AS FinalStatus_JobInvalidSchedule,
--	SUM(CASE WHEN H.FinalJobStatus = 8 THEN 1 ELSE 0 END) AS FinalStatus_JobInvalidTimeWindow,
--	SUM(CASE WHEN H.FinalJobStatus = 9 THEN 1 ELSE 0 END) AS FinalStatus_unknownStatusCode9, 
--	SUM(CASE WHEN H.FinalJobStatus = 10 THEN 1 ELSE 0 END) AS FinalStatus_JobMissed, 
--	SUM(CASE WHEN H.FinalJobStatus = 11 THEN 1 ELSE 0 END) AS FinalStatus_JobPaused, 
--	SUM(CASE WHEN H.FinalJobStatus = 12 THEN 1 ELSE 0 END) AS FinalStatus_JobPending,
	--SUM(CASE WHEN H.FinalJobStatus = 13 THEN 1 ELSE 0 END) AS FinalStatus_JobRecovered, 
--	SUM(CASE WHEN H.FinalJobStatus = 14 THEN 1 ELSE 0 END) AS FinalStatus_JobResourceDisabled, 
--	SUM(CASE WHEN H.FinalJobStatus = 15 THEN 1 ELSE 0 END) AS FinalStatus_JobResumed, 
	SUM(CASE WHEN H.FinalJobStatus = 16 THEN 1 ELSE 0 END) AS FinalStatus_JobRunning, 
--	SUM(CASE WHEN H.FinalJobStatus = 17 THEN 1 ELSE 0 END) AS FinalStatus_JobReady, 
--	SUM(CASE WHEN H.FinalJobStatus = 18 THEN 1 ELSE 0 END) AS FinalStatus_JobScheduled, 
	SUM(CASE WHEN H.FinalJobStatus = 19 THEN 1 ELSE 0 END) AS FinalStatus_JobSuccess, 
	SUM(CASE WHEN H.FinalJobStatus = 20 THEN 1 ELSE 0 END) AS FinalStatus_JobSuperceded, 
--	SUM(CASE WHEN H.FinalJobStatus = 21 THEN 1 ELSE 0 END) AS FinalStatus_JobAbort, 
--	SUM(CASE WHEN H.FinalJobStatus = 22 THEN 1 ELSE 0 END) AS FinalStatus_JobToBeScheduled, 
--	SUM(CASE WHEN H.FinalJobStatus = 23 THEN 1 ELSE 0 END) AS FinalStatus_JobLinkedToAnotherJob, 
--	SUM(CASE WHEN H.FinalJobStatus = 24 THEN 1 ELSE 0 END) AS FinalStatus_unknownStatusCode24, 
--	SUM(CASE WHEN H.FinalJobStatus = 25 THEN 1 ELSE 0 END) AS FinalStatus_JobBlockedByTemplateTule, 

	SUM(CASE WHEN H.FinalJobStatus = 3 THEN 1 ELSE 0 END) AS FinalStatus_Exceptions --3 = With Exceptions
--	SUM(CASE WHEN H.FinalJobStatus = 19 THEN 1 ELSE 0 END) AS FinalStatus_SuccessFull --19 = Successful
	
FROM Jobs J 
	LEFT JOIN TaskDefinition T ON T.JobDefinitionID = J.JobDefinitionID
	LEFT JOIN Schedule S ON S.ScheduleId = T.ScheduleID
	LEFT JOIN JobHistorySummary H ON H.JobID = J.JobID

WHERE S.SystemId IS NOT NULL
	AND H.ActualStartTime > GETDATE() - @TimeWindow
	AND J.NextDueDate < GETDATE() + @TimeWindow
	AND J.JobName LIKE @JobFilter

DECLARE @JobFilter AS VARCHAR(500)

DECLARE @TimeWindow AS Integer

SET @JobFilter = '%-FULL' -- we only want to see jobs that with "-FULL" at the end of the job name / use @PRGT if you want to control it in PRTG

SET @TimeWindow = 7 --7 days in each direction for history and scheduled jobs

SELECT

COUNT(J.JobName) AS TotalJobs,

SUM(CASE WHEN H.IsJobActive = 1 THEN 1 ELSE 0 END) AS ActiveJobs,

-- SUM(CASE WHEN H.FinalJobStatus = 1 THEN 1 ELSE 0 END) AS FinalStatus_JobCancelled,

-- SUM(CASE WHEN H.FinalJobStatus = 2 THEN 1 ELSE 0 END) AS FinalStatus_JobCompleted,

-- SUM(CASE WHEN H.FinalJobStatus = 3 THEN 1 ELSE 0 END) AS FinalStatus_JobException,

-- SUM(CASE WHEN H.FinalJobStatus = 4 THEN 1 ELSE 0 END) AS FinalStatus_JobDispatched,

-- SUM(CASE WHEN H.FinalJobStatus = 5 THEN 1 ELSE 0 END) AS FinalStatus_JobOnHold,

SUM(CASE WHEN H.FinalJobStatus = 6 THEN 1 ELSE 0 END) AS FinalStatus_JobError,

-- SUM(CASE WHEN H.FinalJobStatus = 7 THEN 1 ELSE 0 END) AS FinalStatus_JobInvalidSchedule,

-- SUM(CASE WHEN H.FinalJobStatus = 8 THEN 1 ELSE 0 END) AS FinalStatus_JobInvalidTimeWindow,

-- SUM(CASE WHEN H.FinalJobStatus = 9 THEN 1 ELSE 0 END) AS FinalStatus_unknownStatusCode9,

-- SUM(CASE WHEN H.FinalJobStatus = 10 THEN 1 ELSE 0 END) AS FinalStatus_JobMissed,

-- SUM(CASE WHEN H.FinalJobStatus = 11 THEN 1 ELSE 0 END) AS FinalStatus_JobPaused,

-- SUM(CASE WHEN H.FinalJobStatus = 12 THEN 1 ELSE 0 END) AS FinalStatus_JobPending,

--SUM(CASE WHEN H.FinalJobStatus = 13 THEN 1 ELSE 0 END) AS FinalStatus_JobRecovered,

-- SUM(CASE WHEN H.FinalJobStatus = 14 THEN 1 ELSE 0 END) AS FinalStatus_JobResourceDisabled,

-- SUM(CASE WHEN H.FinalJobStatus = 15 THEN 1 ELSE 0 END) AS FinalStatus_JobResumed,

SUM(CASE WHEN H.FinalJobStatus = 16 THEN 1 ELSE 0 END) AS FinalStatus_JobRunning,

-- SUM(CASE WHEN H.FinalJobStatus = 17 THEN 1 ELSE 0 END) AS FinalStatus_JobReady,

-- SUM(CASE WHEN H.FinalJobStatus = 18 THEN 1 ELSE 0 END) AS FinalStatus_JobScheduled,

SUM(CASE WHEN H.FinalJobStatus = 19 THEN 1 ELSE 0 END) AS FinalStatus_JobSuccess,

SUM(CASE WHEN H.FinalJobStatus = 20 THEN 1 ELSE 0 END) AS FinalStatus_JobSuperceded,

-- SUM(CASE WHEN H.FinalJobStatus = 21 THEN 1 ELSE 0 END) AS FinalStatus_JobAbort,

-- SUM(CASE WHEN H.FinalJobStatus = 22 THEN 1 ELSE 0 END) AS FinalStatus_JobToBeScheduled,

-- SUM(CASE WHEN H.FinalJobStatus = 23 THEN 1 ELSE 0 END) AS FinalStatus_JobLinkedToAnotherJob,

-- SUM(CASE WHEN H.FinalJobStatus = 24 THEN 1 ELSE 0 END) AS FinalStatus_unknownStatusCode24,

-- SUM(CASE WHEN H.FinalJobStatus = 25 THEN 1 ELSE 0 END) AS FinalStatus_JobBlockedByTemplateTule,

SUM(CASE WHEN H.FinalJobStatus = 3 THEN 1 ELSE 0 END) AS FinalStatus_Exceptions --3 = With Exceptions

-- SUM(CASE WHEN H.FinalJobStatus = 19 THEN 1 ELSE 0 END) AS FinalStatus_SuccessFull --19 = Successful

FROM Jobs J

LEFT JOIN TaskDefinition T ON T.JobDefinitionID = J.JobDefinitionID

LEFT JOIN Schedule S ON S.ScheduleId = T.ScheduleID

LEFT JOIN JobHistorySummary H ON H.JobID = J.JobID

WHERE S.SystemId IS NOT NULL

AND H.ActualStartTime > GETDATE() - @TimeWindow

AND J.NextDueDate < GETDATE() + @TimeWindow

AND J.JobName LIKE @JobFilter

Finally I wanted to mention what are our real challenges are, and we don’t yet have a really good solution: Our backup runs FULL starting Friday evening… during the week we run incremental backups. Now the incremental backups are not as critical… so let’s focus on the weekends.

What happened every now and then was that e.g. only a few tapes where write able and other might still have been locked or one of our libraries jammed etc..

In the end, it means – we e.g. came in Monday morning and discovered that 50+ % of the backups did not run.

Now, the question is, how do you monitor this. There are about a 150 jobs – they are stacked on each other. In theory I expect let say 5 running jobs, 0 completed and a 145 pending – starting Friday night – over the weekend this number will change constantly.

What I did not yet find is that a good solution that when Backup Exec waits for user interaction like insert tapes, offline library, etc. does wait for user interaction.

As well as the fact that I can’t tell PRTG on Friday I expect e.g. 150 jobs pending, on Saturday 1 PM the number should be more like 75 jobs pending and on Sunday 6 AM is should be down to 50 pending and Sunday 8 PM it should be 0 pending and 150 successful.

This is very granular, making it hard to find a solution. The jobs in our case will not finish – they are within their weekend time-window and will not be auto cancelled and therefor only manually looking in to Backup Exec will tell you if we are making progress or not.

It could be a solution to constantly see if the Total Bytes backed up goes up – but this again is challenging, we would need to compare values over time.. PRTG is as far as I know not directly able to do so and this would mean we would need to have a temp file with values form the last check in some kind of script or database that we would compare too…

So far I did not come up with the ultimate solution – every now and then I think about it a little more.. but well, I am not there yet.

June 1, 2018 by Florian Rossmark monitoring prtg scripts server

SQL Database backup monitoring

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.:

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

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

'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>"

'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

iReturnFileCount = iReturnFileCount + iFileCount

If iFileCount < iReturnMinFilesPerFolder Then iReturnMinFilesPerFolder = iFileCount

If iFileCount > iReturnMaxFilesPerFolder Then iReturnMaxFilesPerFolder = iFileCount

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

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

-- 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

June 1, 2018 by Florian Rossmark monitoring prtg recommendations scripts

sql

Windows 11 and SQL (Express) issues

Summarize SQL server database file size information as a single row

Raspberry PI and Microsoft SQL databases

SQL Express SQLState 08001 and Error 17

Microsoft RADIUS / NPS SQL logging

Script based SQL Express backups

PRTG – Veritas Backup Exec monitoring

SQL Database backup monitoring

Recent blog posts

Blog Archives

sql

Recent blog posts

Blog Archives

Tags