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:
|
1 2 3 4 5 6 |
INSERT INTO [Tools].[dbo].[DB_Backup_Exceptions] ([InstanceName],[DatabaseName],[LastFullHours] ,[LastLogHours],[Description],[BestBefore]) VALUES ('%','%',36,12,'Default delays for all databases',NULL) GO |
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.
|
1 2 3 4 5 6 |
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 |
The previous rule will skip backup reports on SERVER1 until May 12th.
|
1 2 3 4 5 6 |
INSERT INTO [Tools].[dbo].[DB_Backup_Exceptions] ([InstanceName],[DatabaseName],[LastFullHours] ,[LastLogHours],[Description],[BestBefore]) VALUES ('%','Northwind',1000000,1000000,'Don''t care about northwind',NULL) GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 |
And the following will do the same for the transaction logs:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 |
Putting all together
Copy and paste the following to a new Transact-SQL job step in SQLAgent:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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> <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' |
Any comment appreciated!
Previous: SQLServer centralized backup monitoring with PowerShell and TSQL (1/2)
🙂
—
Ludovico
Latest posts by Ludovico (see all)
- When it comes to using Oracle, trust Oracle… - July 14, 2023
- 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
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.
Hi Ludovico,
Thanks for the beautiful work done. It is so helpful.
Hi LeDBA,
what is the content of $dbbck and $database objects right before and after the if blocks?
—
Ludovico
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!!!
Thanks vinod, I’ve fixed it.
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.
2013.05.08: fixed a wrong convert to get the correct hours in the report
Pingback: SQLServer centralized backup monitoring with PowerShell and TSQL (1/2) | DBA survival BLOG