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

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

INSERT INTO [Tools].[dbo].[DB_Backup_Exceptions]
           ([InstanceName],[DatabaseName],[LastFullHours]
           ,[LastLogHours],[Description],[BestBefore])
     VALUES
           ('%','%',36,12,'Default delays for all databases',NULL)
GO

INSERT INTO [Tools].[dbo].[DB_Backup_Exceptions]

([InstanceName],[DatabaseName],[LastFullHours]

,[LastLogHours],[Description],[BestBefore])

VALUES

('%','%',36,12,'Default delays for all databases',NULL)

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.

INSERT INTO [Tools].[dbo].[DB_Backup_Exceptions]
           ([InstanceName],[DatabaseName],[LastFullHours]
           ,[LastLogHours],[Description],[BestBefore])
     VALUES
           ('SERVER1','%',1000000,1000000,'Maintenance until 012.05.2013', '2013-05-12 00:00:00')
GO

INSERT INTO [Tools].[dbo].[DB_Backup_Exceptions]

([InstanceName],[DatabaseName],[LastFullHours]

,[LastLogHours],[Description],[BestBefore])

VALUES

('SERVER1','%',1000000,1000000,'Maintenance until 012.05.2013', '2013-05-12 00:00:00')

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

INSERT INTO [Tools].[dbo].[DB_Backup_Exceptions]
           ([InstanceName],[DatabaseName],[LastFullHours]
           ,[LastLogHours],[Description],[BestBefore])
     VALUES
           ('%','Northwind',1000000,1000000,'Don''t care about northwind',NULL)
GO

INSERT INTO [Tools].[dbo].[DB_Backup_Exceptions]

([InstanceName],[DatabaseName],[LastFullHours]

,[LastLogHours],[Description],[BestBefore])

VALUES

('%','Northwind',1000000,1000000,'Don''t care about northwind',NULL)

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:

select     s.InstanceName,    s.DatabaseName,
        s.LastFull,
        floor(convert(float,GETDATE()-LastFull)*24) as LastFullAge
from db_status s, DB_Backup_Exceptions e
    where s.InstanceName like e.InstanceName and s.DatabaseName like e.DatabaseName
    and (e.bestbefore is null or e.bestbefore > GETDATE())
    and s.DatabaseStatus!='OFFLINE'
group by s.InstanceName, s.DatabaseName, s.LastFull
having LastFull < GETDATE()-(max(e.LastFullHours)/24.0)
order by s.InstanceName, s.DatabaseName
GO

select s.InstanceName, s.DatabaseName,

s.LastFull,

floor(convert(float,GETDATE()-LastFull)*24) as LastFullAge

from db_status s, DB_Backup_Exceptions e

where s.InstanceName like e.InstanceName and s.DatabaseName like e.DatabaseName

and (e.bestbefore is null or e.bestbefore > GETDATE())

and s.DatabaseStatus!='OFFLINE'

group by s.InstanceName, s.DatabaseName, s.LastFull

having LastFull < GETDATE()-(max(e.LastFullHours)/24.0)

order by s.InstanceName, s.DatabaseName

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

select     s.InstanceName, s.DatabaseName,
         s.LastLog,
         floor(convert(float,GETDATE()-LastLog)*24)  as LastLogAge
    from db_status s, DB_Backup_Exceptions e
        where s.InstanceName like e.InstanceName and s.DatabaseName like e.DatabaseName
    and (e.bestbefore is null or e.bestbefore > GETDATE())
    and s.DatabaseStatus!='OFFLINE' and s.RecoveryMode='FULL'
    group by s.InstanceName, s.DatabaseName, s.LastLog
    having LastLog < GETDATE()-(max(e.LastLogHours)/24.0)
    order by s.InstanceName, s.DatabaseName
GO

select s.InstanceName, s.DatabaseName,

s.LastLog,

floor(convert(float,GETDATE()-LastLog)*24) as LastLogAge

from db_status s, DB_Backup_Exceptions e

where s.InstanceName like e.InstanceName and s.DatabaseName like e.DatabaseName

and (e.bestbefore is null or e.bestbefore > GETDATE())

and s.DatabaseStatus!='OFFLINE' and s.RecoveryMode='FULL'

group by s.InstanceName, s.DatabaseName, s.LastLog

having LastLog < GETDATE()-(max(e.LastLogHours)/24.0)

order by s.InstanceName, s.DatabaseName

Putting all together

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

declare @tableHTML varchar(max),
        @Email varchar(255),
        @Subject varchar(255);

SET @Email = 'your_mail@yourdomain.com'
SET @Subject = 'Report backup SQL Server'

/*
######################
# REPORT BACKUP FULL #
######################
*/
set @tableHTML =
    N'<html><body><h1>Backup full not completed within the defined threshold</h1>
    <table border="1" width="100%">
      <tr><b><th>Instance</th><th>Database</th><th>LastFull</th><th>Hours since Last Full</th></tr>
'

set @tableHTML = @tableHTML + isnull(CAST((
        select
        td = s.InstanceName, '',
        td = s.DatabaseName,  '',
        td = s.LastFull,  '',
        td = floor(convert(int,convert(float,GETDATE()-LastFull)*24,0)), '
        '
from db_status s, DB_Backup_Exceptions e
    where s.InstanceName like e.InstanceName and s.DatabaseName like e.DatabaseName
    and (e.bestbefore is null or e.bestbefore > GETDATE())
    and s.DatabaseStatus!='OFFLINE'
group by s.InstanceName, s.DatabaseName, s.LastFull
having LastFull < (GETDATE()-max(e.LastFullHours)/24.0)
order by s.InstanceName, s.DatabaseName
for XML path ('tr'), TYPE) AS NVARCHAR(MAX)),'');

/*
######################
# REPORT BACKUP LOG  #
######################
*/
SET @tableHTML = @tableHTML + N'
    </table>
    <BR/><BR/>
    <h1>Backup log not completed within the defined threshold</h1>
    <table border="1" width="100%">
            <tr><b><th>Instance</th><th>Database</th><th>LastLog</th><th>Hours since Last Log</th></tr>
';

set @tableHTML = @tableHTML + isnull(CAST((
    select
        td = s.InstanceName, '',
        td = s.DatabaseName, '',
        td = s.LastLog, '',
        td = floor(convert(int,convert(float,GETDATE()-LastLog)*24,0)), '
        '
    from db_status s, DB_Backup_Exceptions e
        where s.InstanceName like e.InstanceName and s.DatabaseName like e.DatabaseName
    and (e.bestbefore is null or e.bestbefore > GETDATE())
    and s.DatabaseStatus!='OFFLINE' and s.RecoveryMode='FULL'
    group by s.InstanceName, s.DatabaseName, s.LastLog
    having LastLog < (GETDATE()-max(e.LastLogHours)/24.0)
    order by s.InstanceName, s.DatabaseName
for XML path ('tr'), TYPE) AS NVARCHAR(MAX)),'');

/*
###################
# LIST EXEPTIONS  #
###################
*/
SET @tableHTML = @tableHTML + N'
    </table>
    <BR/><BR/>
    <h1>List of exceptions currently defined</h1>
    <table border="1" width="100%">
            <tr><b><th>Instance</th><th>Database</th><th>LastFullHours</th><th>LastLogHours</th><th>Description</th><th>BestBefore</th></tr>
';

set @tableHTML = @tableHTML + CAST((
    select
        td = InstanceName, '',
        td = DatabaseName, '',
        td = LastFullHours, '',
        td = LastLogHours, '',
        td = Description, '',
        td = ISNULL(convert(varchar,bestbefore,127), 'never'), ''
    from DB_Backup_Exceptions
    where (bestbefore is null or bestbefore > GETDATE())
    order by InstanceName, DatabaseName
for XML path ('tr'), TYPE) AS NVARCHAR(MAX));

SET @tableHTML = @tableHTML + N'
    </table>
<br/><br/>
<font size="3">Information message. Please do not reply to this email.</h1></body></html>';

EXEC msdb.dbo.sp_send_dbmail
@recipients        = @Email,
@subject        = @Subject,
@body            = @tableHTML,
@body_format    = 'HTML',
@profile_name    = 'DBA'

100

declare @tableHTML varchar(max),

@Email varchar(255),

@Subject varchar(255);

SET @Email = 'your_mail@yourdomain.com'

SET @Subject = 'Report backup SQL Server'

######################

# REPORT BACKUP FULL #

######################

set @tableHTML =

N'<html><body><h1>Backup full not completed within the defined threshold</h1>

<tr><b><th>Instance</th><th>Database</th><th>LastFull</th><th>Hours since Last Full</th></tr>

set @tableHTML = @tableHTML + isnull(CAST((

select

td = s.InstanceName, '',

td = s.DatabaseName, '',

td = s.LastFull, '',

td = floor(convert(int,convert(float,GETDATE()-LastFull)*24,0)), '

from db_status s, DB_Backup_Exceptions e

where s.InstanceName like e.InstanceName and s.DatabaseName like e.DatabaseName

and (e.bestbefore is null or e.bestbefore > GETDATE())

and s.DatabaseStatus!='OFFLINE'

group by s.InstanceName, s.DatabaseName, s.LastFull

having LastFull < (GETDATE()-max(e.LastFullHours)/24.0)

order by s.InstanceName, s.DatabaseName

for XML path ('tr'), TYPE) AS NVARCHAR(MAX)),'');

######################

# REPORT BACKUP LOG #

######################

SET @tableHTML = @tableHTML + N'

</table>

<h1>Backup log not completed within the defined threshold</h1>

<tr><b><th>Instance</th><th>Database</th><th>LastLog</th><th>Hours since Last Log</th></tr>

set @tableHTML = @tableHTML + isnull(CAST((

select

td = s.InstanceName, '',

td = s.DatabaseName, '',

td = s.LastLog, '',

td = floor(convert(int,convert(float,GETDATE()-LastLog)*24,0)), '

from db_status s, DB_Backup_Exceptions e

where s.InstanceName like e.InstanceName and s.DatabaseName like e.DatabaseName

and (e.bestbefore is null or e.bestbefore > GETDATE())

and s.DatabaseStatus!='OFFLINE' and s.RecoveryMode='FULL'

group by s.InstanceName, s.DatabaseName, s.LastLog

having LastLog < (GETDATE()-max(e.LastLogHours)/24.0)

order by s.InstanceName, s.DatabaseName

for XML path ('tr'), TYPE) AS NVARCHAR(MAX)),'');

###################

# LIST EXEPTIONS #

###################

SET @tableHTML = @tableHTML + N'

</table>

<h1>List of exceptions currently defined</h1>

<tr><b><th>Instance</th><th>Database</th><th>LastFullHours</th><th>LastLogHours</th><th>Description</th><th>BestBefore</th></tr>

set @tableHTML = @tableHTML + CAST((

select

td = InstanceName, '',

td = DatabaseName, '',

td = LastFullHours, '',

td = LastLogHours, '',

td = Description, '',

td = ISNULL(convert(varchar,bestbefore,127), 'never'), ''

from DB_Backup_Exceptions

where (bestbefore is null or bestbefore > GETDATE())

order by InstanceName, DatabaseName

for XML path ('tr'), TYPE) AS NVARCHAR(MAX));

SET @tableHTML = @tableHTML + N'

</table>

<font size="3">Information message. Please do not reply to this email.</h1></body></html>';

EXEC msdb.dbo.sp_send_dbmail

@recipients = @Email,

@subject = @Subject,

@body = @tableHTML,

@body_format = 'HTML',

@profile_name = 'DBA'

Any comment appreciated!

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

🙂

—

Ludovico

Bio
Latest Posts

Ludovico

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.

Latest posts by Ludovico (see all)

Video: Where should I put the Observer in a Fast-Start Failover configuration? - November 29, 2022
Video: The importance of Fast-Start Failover in an Oracle Data Guard configuration - November 29, 2022
Find Ludovico at Oracle Cloud World 2022! - October 14, 2022

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

Pingback: SQLServer centralized backup monitoring with PowerShell and TSQL (1/2) | DBA survival BLOG
Ludovico on May 8, 2013 at 5:16 pm said:

2013.05.08: fixed a wrong convert to get the correct hours in the report
vinod on July 24, 2013 at 4:57 pm said:

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.
Ludovico on August 30, 2013 at 11:30 am said:

Thanks vinod, I’ve fixed it.
LeDBA on October 30, 2013 at 3:53 pm said:

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!!!
Ludovico on November 1, 2013 at 12:00 pm said:

Hi LeDBA,
what is the content of $dbbck and $database objects right before and after the if blocks?
—
Ludovico
sagaya anitta on October 13, 2015 at 3:07 pm said:

Hi Ludovico,

Thanks for the beautiful work done. It is so helpful.
Vyas on October 22, 2021 at 7:30 am said:

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?
Ludovico on November 19, 2021 at 6:27 pm said:

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.