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)

🙂

Ludovico

The following two tabs change content below.

Ludovico

Oracle ACE Director and Senior Consultant at Trivadis
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Senior Database Specialist for Trivadis, Switzerland.

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

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

  2. 2013.05.08: fixed a wrong convert to get the correct hours in the report

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

Leave a Reply

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