backup

How to create an independent backup network

Today we look at independent backup networks especially in regards to LTO 7 and VMware ESX hosts. Be aware – this very example also applies to any backup to disk (B2D / Backup-2-Disk) solution. But a good reseller / vendor would inform you about this right away anyways.

LTO 7 and later like LTO 8 drives have a write speed faster then a 1 GBit network can handle, making it now really necessary to think about options. On top of it, you do not want to over utilize the LAN side of your servers so that the impact on the user / application facing side stays minimal. This leaves you with two options, you can group switch ports assuming you have enough 1 GB ports and use them, you will need at least 3 ports combined, or you create a whole backup network on a 10 GB basis.

Let’s run some numbers:

LTO7 has a write speed of about 300 MB/s uncrompressed and up to 750 MB/s compressed
LTO8 (L8) has a write speed of about 360 MB/s uncrompressed and up to 900 MB/s compressed

Now – your network connection is meassured in MBit/s not MByte/s. Byte to bit is 8 bit are one byte, so we need to multiply those speeds in byte with 8 bit too see the network speed numbers.

LTO7 uncrompressed = 300MB/s * 8 = 2400 MBit/s
LTO7 compressed = 750MB/s * 8 = 6000 MBit/s

LTO8 uncrompressed = 360MB/s * 8 = 2880 MBit/s
LTO8 compressed = 900MB/s * 8 = 7200 MBit/s

Assuming you want to go with grouped ports, you see that with LTO7 you would need 6 ports and LTO8 7 to 8 ports to fully utilize the speed and minimize your backup window. Additionally think about the read speed that might affect you as well – not just for recovery but for the verify of your backup.

Now – this means – add at least one 10 GB switch and one 10 GB NIC to each server – let’s do this with an example:

3x VMware ESX hosts – LAN side and management is configured 1 GB – we assume there is some kind of storage behind them that has the iOPS and speed we need like an SSD based storage
1x Backup media server that has an LTO7 or LTO8 drive connected – 1 GB on the LAN side

What we need – minimal:

4x 10 GB NICs
1x 10 GB switch
4x CAT6e or CAT7 cables

What I would recommend – nice to have:

4x 10 GB NICs – dual port
2x 10 GB switches
10x CAT7 cables – 2x to stack/trunk the switches if not stacked otherwise

This is a nice to have – a fail-over, but the minimal configuration is sufficient as well.

Cable this all in – create a new IP-scope / VLAN on the backup side – you do not need any default Gateway etc. on the Backup-Network side (10 GB). Just an independent IP scope and have every host assigned a static address.

This keeps the regular network traffic and any broadcasts away from this network and your backup will run totally independent. You might need to disable your anti-virus solution on this NIC / IP-scope on the backup media server as well, cause it might actually influence the speed quite drastically. Having it separated and independent helps keeping the security up.

On the VMware hosts – I like to even allow VMware to vMotion on this backup-LAN – simply because it is extremely efficient there – independent from your LAN and if you have it from your iSCSI network as well. But that’s just an idea.

Now – the backup – how will it grab the data from the 10 GB side of your VMware hosts – especially if you have a vSphere cluster and grab the backup through the cluster?

Simple – you adjust the hosts file on your media server. Each and every VMware host needs to be listed in the hosts-file on the media server with the IP that it has in your 10 GB backup network. This way DNS and everything will act normal in your environment, only the backup-media server will reach out to those hosts on the 10 GB network due to the IP resolution of those hosts. This is the easiest way to accomplish this.

You will not to add a 10 GB connection, backup-network IP address etc. to your VMware vSphere controller – it can stay on your LAN or server-management network as is. This also means there is no reason to mention him in the hosts file on the media server.

How this works:
your backup will contact the vSphere controller on the LAN side
it will then be redirected to the host that currently holds the VM you want to backup
the media server now will contact the VMware host directly – due to the hosts-file entry on the 10 GB backup-network
backup will process…

This of course would work with a physical server as well – like a physical file-server etc. – though, today this is rather rare and especially VMware backups are actually large files that benefit most from the LTO7 write speed so the above makes sense there most. It wouldn’t matter if you do the same to an Hyper-V environment or any other VM host/guest solution. In theory it should always work the same.

What real world write speeds can you expect?
This is the big question – here are some real world examples of this – those are single jobs on per VM basis, meaning it includes even the tape-load and tape-unload processing time and udpating the catalogs while using Veritas Backup Exec.

Backup size (VM size)	elapsed time in minutes	job rate write/overall	job rate verify
4 TB	6:47	17,227 MB/min	26,404 MB/min
2 TB	6:47	6,822 MB/min	22,233 MB/min
1.21 TB	3:49	8,271 MB/min	20,235 MB/min
147 GB	0:33	6,491 MB/min	22,655 MB/min
138 GB	0:17	18,403 MB/min	27,726 MB/min
25 GB	0:10	9,172 MB/min	20,700 MB/min

The above list is just an example – realistically we see speeds between about 3,000 MB/min to 18,000 MB/min as for the overall speed. This is due to the VM itself for some part – thin or thick provisioned, what kind of data is it holding, how busy is the host cause we might double trouble him due to multiple drives doing backups at the same time to the same host etc… In average we see around 8,000 to 9,000 MB/min in speed, what is still great – and I wanted to show as well that it can vary quite a bit so don’t be scared. We still did improve the time the backup took from going from an LTO4 LAN based backup scenario to an LTO7 independent backup network while cutting the time in half, actually, even less then half. The slowest speeds we see today are due to systems that can only be backed up on the LAN side, while the ports are grouped there but we still don’t have the same speed as we see on the backup-network side. Many factors come in play but that all depends on the individual situation.

Hoping the information above helps some of you out there – keep in mind that your situation might be different, run some examples and ideas and if you have questions, reach out – this remains an example of what I really implemented at a company and how it affected the backup configuration and management.

Print Server backup script

Print servers need to backed up. This is because of two main reasons. One is that users heavily depend on printers and a not properly working print server will cause imediate helpdesk tickets and unhappy users. The other one is that installing a new driver, might it be a new version, a new model or even an additional manufacturer, can cause other print drivers to act up or even stop working – many administrators know and fear that.

Windows server actually allows you to backup the current print drivers, installed printers and their configuration. You can use this to migrate your printers or to back them up. Of course, you can simply depend on e.g. VMware snapshots, storage level snapshots or other backups of your server. But you also could just export the whole print server configuration while using the scripts below. Those will actually call the Windows API to back up the printers and store it all in a file that you can keep centrally. You don’t just rely on snapshots or a full server backup for e.g. your SQL databases as well, do you?

The script uses a .CMD file that will execute the actual backup and send a email report, while using 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. Additionally there is a .VBS script that will do a clean up of the target backup files depending on the age of the files in the specified directory.

All the parameters are explained and set in the top part of the .CMD file – I therefor will not explain them here again – you should not need to modify the scripts by default – but feel free to do so. Of course, you should create a scheduled task and execute the .CMD periodically. This can save you time and headache in case you have a malfunctioning print server system. The restore can be easily done through the Print Management MMC that Windows provides you, cause the actual backup files are createed using the same Windows APIs. Your end users will be happy that their printers got back to work in no time, hopefully.

@ECHO OFF
SETLOCAL
REM Printer Backup Script
REM This script creates a Printer Backup on a Windows 2008 or newer Server System
REM (might work as well on Windows Vista or newer)
REM The Script uses Standard Windows Procedures to create a Standard Windows Printer Backup File
REM The Backup will include Printer Settings and Drivers
REM Use "Print Management" to restore the backup 
REM 
REM WARNING
REM -------
REM The Script will automatically search and delete all Files older than intAge in the destination Folder!
REM Always set a destination Folder just for the System you are going to Backup.
REM SubFolders will be ignored.
REM 
REM CONFIG Values
REM -------------
REM strPrintServer = string value / PrintServer to backup - localhost or hostname
REM intAge = integer value / max Age in Days for the Backup Files, the script will automatically delete older Backup Files
REM strPath = string value / SMB or UNC Path to the Backup-Folder, the executing User needs Read/Write access to this path
REM strMailServer = string value / SMTP Server
REM intMailServerPort = integer value / SMTP Server Port
REM strMailFrom = string value / from address, must be able to send Mail over the SMTP
REM strMailTo = string value / mail recipient
REM strMailSuject = string value / mail subject line


REM CONFIG Values
REM =============================================================================================
SET strPrintServer=server.domain.local
SET intAge=3
SET strPath=\\server.domain.local\backup$\Printer\SERVER
SET strMailServer=smtp.domain.com
SET intMailServerPort=25
SET strMailFrom=admin@domain.local
SET strMailTo=admin@domain.local
SET strMailSubject=Printer Backup - SERVERNAME
REM =============================================================================================
REM End CONFIG Values

REM Script-System-Variables, do not change from here on!
SET ScriptLog=%~dp0\Printer_Backup_Log.txt

DEL %ScriptLog% 
ECHO Script-Log Start Date/Time: %Date% / Time: %Time% >> %ScriptLog%
ECHO Script-Parameter: >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%
ECHO strPrintServer: %strPrintServer% >> %ScriptLog%
ECHO intAge: %intAge% >> %ScriptLog%
ECHO strPath: %strPath% >> %ScriptLog%
ECHO strMailServer: %strMailServer% >> %ScriptLog%
ECHO intMailServerPort: %intMailServerPort% >> %ScriptLog%
ECHO strMailFrom: %strMailFrom% >> %ScriptLog%
ECHO strMailTo: %strMailTo% >> %ScriptLog%
ECHO strMailSubject: %strMailSubject% >> %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=%%D%%C%%B>> %ScriptLog%
FOR /F "tokens=1,2 delims=: " %%A IN ('Time /T') DO SET NowDate=%NowDate%_%%A%%B>> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ECHO Starting Printer Backup: >> %ScriptLog% 
%windir%\System32\Spool\Tools\PrintBRM.EXE -B -S %strPrintServer% -F %strPath%\%NowDate%_%strPrintServer%.bak >>"%ScriptLog%"
ECHO =========================================================================== >> %ScriptLog%

ECHO Backup CleanUp >> %ScriptLog%
CSCRIPT %~dp0\Printer_Backup_CleanUp.vbs %intAge% "%strPath%" >> %ScriptLog%
ECHO Backup CleanUp finished >>%ScriptLog%
ECHO =========================================================================== >> %ScriptLog%
ECHO Current Files in Backup Path: >>%ScriptLog%
DIR %strPath% >>%ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ECHO Script-Log End Date/Time: %Date% / Time: %Time% >> %ScriptLog%
ECHO Sending Info Mail >> %ScriptLog%
%~dp0\SMTPSEND.EXE -f%strMailFrom% -t%strMailTo% -h%strMailServer% -p%intMailServerPort% -s%strMailSubject% -i%ScriptLog% >> %ScriptLog%
ECHO =========================================================================== >> %ScriptLog%

ENDLOCAL

@ECHO OFF

SETLOCAL

REM Printer Backup Script

REM This script creates a Printer Backup on a Windows 2008 or newer Server System

REM (might work as well on Windows Vista or newer)

REM The Script uses Standard Windows Procedures to create a Standard Windows Printer Backup File

REM The Backup will include Printer Settings and Drivers

REM Use "Print Management" to restore the backup

REM

REM WARNING

REM -------

REM The Script will automatically search and delete all Files older than intAge in the destination Folder!

REM Always set a destination Folder just for the System you are going to Backup.

REM SubFolders will be ignored.

REM

REM CONFIG Values

REM -------------

REM strPrintServer = string value / PrintServer to backup - localhost or hostname

REM intAge = integer value / max Age in Days for the Backup Files, the script will automatically delete older Backup Files

REM strPath = string value / SMB or UNC Path to the Backup-Folder, the executing User needs Read/Write access to this path

REM strMailServer = string value / SMTP Server

REM intMailServerPort = integer value / SMTP Server Port

REM strMailFrom = string value / from address, must be able to send Mail over the SMTP

REM strMailTo = string value / mail recipient

REM strMailSuject = string value / mail subject line

REM CONFIG Values

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

SET strPrintServer=server.domain.local

SET intAge=3

SET strPath=\\server.domain.local\backup$\Printer\SERVER

SET strMailServer=smtp.domain.com

SET intMailServerPort=25

SET strMailFrom=admin@domain.local

SET strMailTo=admin@domain.local

SET strMailSubject=Printer Backup - SERVERNAME

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

REM End CONFIG Values

REM Script-System-Variables, do not change from here on!

SET ScriptLog=%~dp0\Printer_Backup_Log.txt

DEL %ScriptLog%

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

ECHO Script-Parameter: >> %ScriptLog%

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

ECHO strPrintServer: %strPrintServer% >> %ScriptLog%

ECHO intAge: %intAge% >> %ScriptLog%

ECHO strPath: %strPath% >> %ScriptLog%

ECHO strMailServer: %strMailServer% >> %ScriptLog%

ECHO intMailServerPort: %intMailServerPort% >> %ScriptLog%

ECHO strMailFrom: %strMailFrom% >> %ScriptLog%

ECHO strMailTo: %strMailTo% >> %ScriptLog%

ECHO strMailSubject: %strMailSubject% >> %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=%%D%%C%%B>> %ScriptLog%

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

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

ECHO Starting Printer Backup: >> %ScriptLog%

%windir%\System32\Spool\Tools\PrintBRM.EXE -B -S %strPrintServer% -F %strPath%\%NowDate%_%strPrintServer%.bak >>"%ScriptLog%"

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

ECHO Backup CleanUp >> %ScriptLog%

CSCRIPT %~dp0\Printer_Backup_CleanUp.vbs %intAge% "%strPath%" >> %ScriptLog%

ECHO Backup CleanUp finished >>%ScriptLog%

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

ECHO Current Files in Backup Path: >>%ScriptLog%

DIR %strPath% >>%ScriptLog%

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

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

ECHO Sending Info Mail >> %ScriptLog%

%~dp0\SMTPSEND.EXE -f%strMailFrom% -t%strMailTo% -h%strMailServer% -p%intMailServerPort% -s%strMailSubject% -i%ScriptLog% >> %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 27, 2018 by Florian Rossmark recommendations scripts 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

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

backup

How to create an independent backup network

Print Server backup script

Script based SQL Express backups

SQL Database backup monitoring

Recent blog posts

Blog Archives

backup

Recent blog posts

Blog Archives

Tags