SQLServer centralized backup monitoring with PowerShell and TSQL (2/2)

From commons.wikipedia.org. Author: włodiIn my previous post  I’ve shown how to collect data and insert it into a database table using PowerShell. Now it’s time to get some information from that data, and I’ve used TSQL for this purpose.

The backup exceptions

Every environment has some backup rules and backup exceptions. For example, you don’t want to check for failures on the model, northwind, adventureworks or distribution databases.

I’ve got the rid of this problem by using the “exception table” created in the previous post. The rules are defined by pattern matching. First we need to define a generic rule for our default backup schedules:

In the previous example, we’ll check that all databases (‘%’) on all instances (again ‘%’)  have been backed up at least every 36 hours, and a backup log have occurred in the last 12 hours. The description is useful to remember why such rule exists.

The “BestBefore” column allows to define the time limit of the rule. For example, if you do some maintenance and you are skipping some schedules, you can safely insert a rule that expires after X days, so you can avoid some alerts while avoiding also to forget to delete the rule.

The previous rule will skip backup reports on SERVER1 until May 12th.

The previous rule will skip all reports on all Northwind databases.

Important: If multiple rules apply to the same database, the rule with a higher time threshold wins.

The queries

The following query lists the databases with the last backup full older than the defined threshold:

And the following will do the same for the transaction logs:

Putting all together

Copy and paste the following to a new Transact-SQL job step in SQLAgent:

Any comment appreciated!

Previous: SQLServer centralized backup monitoring with PowerShell and TSQL (1/2)



The following two tabs change content below.


Principal Product Manager at Oracle
Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

9 thoughts on “SQLServer centralized backup monitoring with PowerShell and TSQL (2/2)

  1. Hi Good morning and Good day.

    I am not able to configure this script with SQL Server Job schedule and the script getting failed. Can you please help me in this regards if i post the errors?

    • Hi Vyas,
      I’m not working on SQL Server anymore. I leave the posts there in case they are helpful, but I don’t plan to do any follow-up with them.
      The script is not that complex, I suggest you run the selects individually to check if they work in the first instance (that was working on SQL 2012 but things might have changed) , and then either do some troubleshooting or build your script based on the queries.

  2. I’m having a hard time to deal with the nulls, whenever a database has no backups at all, the LastTran, LastFull columns are NULL.

    In the PS “Collect_Backup_Data.ps1” you have a validation to address the “NULL” problem:

    if ( -not ( $database.IsNull(“LastFull”) ) ) {
    $dbbck.lastFull = $database.LastFull
    } else {
    $dbbck.lastFull = “01.01.1900 00:00:00”
    if ( -not ( $database.IsNull(“LastTran”) ) ) {
    $dbbck.lastLog = $database.LastTran
    } else {
    $dbbck.lastLog = “01.01.1900 00:00:00”

    but for some odd reason is not working, I tried adding an ISNULL validation of the PS “queries.ps1” as part of the query but it’s not working either way.

    Exception calling “ExecuteNonQuery” with “0” argument(s): “The conversion of a varchar data type to a datetime data typ
    e resulted in an out-of-range value.
    The statement has been terminated.”
    At D:\DBA\BackupChecker\Collect_Backup_Data.ps1:83 char:29
    + $Command.ExecuteNonQuery <<<< () | out-null
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

    If you could please give me a hand will be great!!!

  3. Good work, appreciated. i get following error, when running the sql agent job.

    Incorrect syntax near ‘ ‘. [SQLSTATE 42000] (Error 102) Incorrect syntax near ‘ ‘. [SQLSTATE 42000] (Error 102) Incorrect syntax near ‘t’. [SQLSTATE 42000] (Error 102) Unclosed quotation mark after the character string ‘ ‘. [SQLSTATE 42000] (Error 105). The step failed.

    would you mind sending the corrected code.

  4. Pingback: SQLServer centralized backup monitoring with PowerShell and TSQL (1/2) | DBA survival BLOG

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.