In the previous articles, we discussed several ways of taking backup and testing the restore process to ensure the integrity of the backup file.
In this article, we’re going to discuss the importance of the database backup reporting. This kind of reporting will be designed to eliminate any unnecessary risk that may arise with the safety and security of information. This is the reason, the high-level report; Daily Health Check report will be generated and sent to the SME or to the DBA responsible for IT Infra management.
Database backups are a vital component for most of database administrators regardless what backup tool is in place and how the data backup process is all about backing up the data to the disk, to the tape or to the cloud.
In general, administrators are very much concerned with getting the report daily and also any alerts as per the defined SLAs. Administrators rely on backup report to understand and how the backups are doing and always wanted to safeguard the data
The other area, we will cover, is the online backup; it is increasingly becoming the default choice for many small businesses databases. A database backup report is an important document which reveals the specific piece of information about the status of the data. In general, the generation reports have become a standard practice for any business who values their data.
A backup report, should be produced, after each backup job has run. The report provides detailed information including what was backed up, what can be restored and information about the backup media. Some of the information provided is specific to each backup type.
The following are the three important parameters that need to be reviewed at regular intervals of time are:
-
Backup failure jobs
This is the most important metric to be measured and in most cases, it requires immediate attention and action. For test or dev environments, this could wait for being taking an action based on the SLA
-
Capacity planning and forecasting
The other report generally circulated every day is the disk space utilization report. Proactive monitoring of storage would prevent most of the backup failures and it helps to forecast the data growth. In turn this may reduce many unforeseen disk space related issues.
-
Performance
Backup performance is the important metric to measure the overall health of the system. It gives a heads-up for many hidden issues such as hardware resource problem, device driver issues, network throughput problem, software problem etc:-
The importance of backup reports needs to measured in all the aforementioned parameters. In some cases, we intend to know how the backup ran the previous night or we need to compare the time it took for successful execution of the backup. These are some of the metrics one has to consider for database backup reports.
Obviously, the backup age, completion status of a backup job and alerting and periodic notifications are considered the most basic function of any backup reports should provide. In fact, backup tools provide some forms of a daily backup reports. Being said, most of the backup tools do a great job of backing up the data and in some cases, the reports aren’t user-friendly and that requires the customization.
Generating backup reports using PowerShell SMO libraries
You can customize the PowerShell script and/or T-SQL schedule the job as per the requirement.
Prepare the script
- Install SQL Server PowerShell module from the PowerShell repository hub PSGallery. The instructions in the link detail the direction for installing SQL Server module
- Prepare the servers list in a CSV file and import the CSV file using import-csv cmdlets
- Prepare the simple PowerShell script by instantiating SMO class libraries. PowerShell allows leveraging cmdlets and objects through a concept known as piping.
- In the following example, we can see that how the objects are inherited its properties from a database
PowerShell script
|
1 2 3 4 5 6 7 8 9 |
Import-module SQLServer import-csv 'C:\server_test.txt' | ForEach-Object {New-Object 'Microsoft.SqlServer.Management.Smo.Server' $_.ServerName}| Select-Object -Expand Databases | Select-Object Name, RecoveryModel, @{n='LastFULLBackupDate';e={if ($_.RecoveryModel -eq 'Simple' -or $_.LastBackupDate -eq '01/01/0001 00:00:00') {'NA'} else {$_.LastBackupDate}}}, @{n='LastDifferentialBackupDate';e={if ($_.RecoveryModel -eq 'Simple' -or $_.LastDifferentialBackupDate -eq '01/01/0001 00:00:00') {'NA'} else {$_.LastDifferentialBackupDate}}}, @{n='LastLogBackupDate';e={if ($_.RecoveryModel -eq 'Simple' -or $_.LastLogBackupDate -eq '01/01/0001 00:00:00') {'NA'} else {$_.LastLogBackupDate}}}|ft -AutoSize |
Let’s dissect the script
- The input file, CSV file, contains the server names
- The output file, HTML type, the string output is converted to HTML format using string concatenation
- Email list, this parameter contains the recipient’s email ids. You can have one or more and each id must be separated by a comma
- Use PowerShell cmdlets to verify and install the SQL Server module from PSGallery
- Define the CSS (Cascading Style Sheet) that contains predefined HTML styles that are going to be referred for HTML data formatting. A CSS contains many style definitions. For example, define table styles, background color, border and heading and many more. It is that simple to build an HTML document to get the desired formatting effect and results.
- Build a PowerShell cmdlet to gather a data set based on the conditions.
- The logical condition is defined with an assumption of weekly full, daily differential and hourly t-log backups.
- The color combination highlights those databases which require immediate measures to be taken as per the defined SLA.
- Define the email notification system
Let’s save the following content Databasbackup.ps1.
|
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 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 |
#Change value of following variables as needed $ServerList = "C:\server_test.txt" $OutputFile = "C:\output.htm" If (Test-Path $OutputFile){ Remove-Item $OutputFile } $emlist="pjayaram@appvion.com,prashanth@abc.com" $MailServer='sqlshackmail.mail.com' $HTML = '<style type="text/css"> #Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;} #Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;} #Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A23942;color:#fff;} #Header tr.alt td {color:#000;background-color:#EAF2D3;} </Style>' $HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header> <TR> <TH><B>ServerName Name</B></TH> <TH><B>Database Name</B></TH> <TH><B>RecoveryModel</B></TD> <TH><B>Last Full Backup Date</B></TH> <TH><B>Last Differential Backup Date</B></TH> <TH><B>Last Log Backup Date</B></TH> </TR>" try { If (Get-Module SQLServer -ListAvailable) { Write-Verbose "Preferred SQLServer module found" } else { Install-Module -Name SqlServer } } catch { Write-Host "Check the Module and version" } Import-Csv $ServerList |ForEach-Object { $ServerName=$_.ServerName $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName Foreach($Database in $SQLServer.Databases) { $DaysSince = ((Get-Date) - $Database.LastBackupDate).Days $DaysSinceDiff = ((Get-Date) - $Database.LastDifferentialBackupDate).Days $DaysSinceLog = ((Get-Date) - $Database.LastLogBackupDate).TotalHours IF(($Database.Name) -ne 'tempdb' -and ($Database.Name) -ne 'model') { if ($Database.RecoveryModel -like "simple" ) { $HTML += "<TR > <TD>$($SQLServer)</TD> <TD>$($Database.Name)</TD> <TD>$($Database.RecoveryModel)</TD>" if ($DaysSince -gt 7) { $HTML += "<TD bgcolor='RED'>$($Database.LastBackupDate)</TD>" } else { $HTML += "<TD>$($Database.LastBackupDate)</TD>" } if ($DaysSinceDiff -gt 1) { $HTML += "<TD bgcolor='CYAN'>$($Database.LastDifferentialBackupDate)</TD>" } else { $HTML += "<TD>$($Database.LastDifferentialBackupDate)</TD>" } $HTML += "<TD>NA</TD></TR>" } } if ($Database.RecoveryModel -like "full" ) { $HTML += "<TR > <TD>$($SQLServer)</TD> <TD>$($Database.Name)</TD> <TD>$($Database.RecoveryModel)</TD>" if ($DaysSince -gt 7) { $HTML += "<TD bgcolor='RED'>$($Database.LastBackupDate)</TD>" } else { $HTML += "<TD>$($Database.LastBackupDate)</TD>" } if ($DaysSinceDiff -gt 1) { $HTML +="<TD bgcolor='CYAN'>$($Database.LastDifferentialBackupDate)</TD>" } else { $HTML += "<TD>$($Database.LastDifferentialBackupDate)</TD>" } if($DaysSinceLog -gt 1) { $HTML +="<TD bgcolor='Yellow'>$($Database.LastLogBackupDate)</TD>" } else { $HTML += "<TD>$($Database.LastLogBackupDate)</TD>" } } } } $HTML += "</Table></BODY></HTML>" $HTML | Out-File $OutputFile Function sendEmail { param($from,$to,$subject,$smtphost,$htmlFileName) $body = Get-Content $htmlFileName $body = New-Object System.Net.Mail.MailMessage $from, "$to", $subject, $body $body.isBodyhtml = $true $smtpServer = $MailServer $smtp = new-object Net.Mail.SmtpClient($smtpServer) $smtp.Send($body) } $date = ( get-date ).ToString('yyyy/MM/dd') sendEmail pjayaram@appletonideas.com $emlist "Database Backup Report - $Date" $MailServer $OutputFile |
Output
Backup reports using T-SQL
Let’s discuss the report generation using T-SQL
This SQL has three sections
- Full backup status
- Differential backup status
- T-log backup status
I will discuss the full backup part of the script. The same would be applicable to other backup types as well. The first part is all about aggregation and transformation to get all the rows from the msdb.dbo.backupset. The aggregation is done by fetching the most recent rows from the system objects for specific backup types. Transformation is done by converting the multi-line rows into columns for the databases. The column also includes the backup_size and duration it took for its completion.
If required, you can refer the link backup strategy on how to pull the data into a central repository using T-SQL and Powershell.
Prepare SQL
|
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 |
WITH backupsetSummary AS ( SELECT bs.database_name , bs.type bstype , MAX(backup_finish_date) MAXbackup_finish_date FROM msdb.dbo.backupset bs GROUP BY bs.database_name , bs.type ), MainBigSet AS ( SELECT @@SERVERNAME servername, db.name , db.state_desc , db.recovery_model_desc , bs.type , convert(decimal(10,2),bs.backup_size/1024.00/1024) backup_sizeinMB, bs.backup_start_date, bs.backup_finish_date, physical_device_name, DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS DurationMins FROM master.sys.databases db LEFT OUTER JOIN backupsetSummary bss ON bss.database_name = db.name LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name AND bss.bstype = bs.type AND bss.MAXbackup_finish_date = bs.backup_finish_date JOIN msdb.dbo.backupmediafamily m ON bs.media_set_id = m.media_set_id where db.database_id>4 ) -- select * from MainBigSet SELECT servername, name, state_desc, recovery_model_desc, Last_Backup = MAX(a.backup_finish_date), Last_Full_Backup_start_Date = MAX(CASE WHEN A.type='D' THEN a.backup_start_date ELSE NULL END), Last_Full_Backup_end_date = MAX(CASE WHEN A.type='D' THEN a.backup_finish_date ELSE NULL END), Last_Full_BackupSize_MB= MAX(CASE WHEN A.type='D' THEN backup_sizeinMB ELSE NULL END), DurationSeocnds = MAX(CASE WHEN A.type='D' THEN DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) ELSE NULL END), Last_Full_Backup_path = MAX(CASE WHEN A.type='D' THEN a.physical_Device_name ELSE NULL END), Last_Diff_Backup_start_Date = MAX(CASE WHEN A.type='I' THEN a.backup_start_date ELSE NULL END), Last_Diff_Backup_end_date = MAX(CASE WHEN A.type='I' THEN a.backup_finish_date ELSE NULL END), Last_Diff_BackupSize_MB= MAX(CASE WHEN A.type='I' THEN backup_sizeinMB ELSE NULL END), DurationSeocnds = MAX(CASE WHEN A.type='I' THEN DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) ELSE NULL END), Last_Log_Backup_start_Date = MAX(CASE WHEN A.type='L' THEN a.backup_start_date ELSE NULL END), Last_Log_Backup_end_date = MAX(CASE WHEN A.type='L' THEN a.backup_finish_date ELSE NULL END), Last_Log_BackupSize_MB= MAX(CASE WHEN A.type='L' THEN backup_sizeinMB ELSE NULL END), DurationSeocnds = MAX(CASE WHEN A.type='L' THEN DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) ELSE NULL END), Last_Log_Backup_path = MAX(CASE WHEN A.type='L' THEN a.physical_Device_name ELSE NULL END), [Days_Since_Last_Backup] = DATEDIFF(d,(max(a.backup_finish_Date)),GETDATE()) FROM MainBigSet a group by servername, name, state_desc, recovery_model_desc -- order by name,backup_start_date desc |
T-SQL output
Creating a HTML backup report using XML
In this section, we’re going to discuss the generation of the HTML tags using FOR XML clause. It provides a way to convert the results of an SQL query to XML. The complex SQL data is pushed to a temp table named #temp. This facilitates the conversion of an SQL data into XML in a much a simpler way. You can refer the XML link for more information.
Next, define the SQL text fields as data sections using FOR XML PATH clause and define the XML schema.
Configure SQL Server Agent Mail to Use Database Mail and pass the XML string is HTML type data to send HTML output to intended recipients
|
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 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 |
Declare @tableHTML NVARCHAR(MAX) ; WITH backupsetSummary AS ( SELECT bs.database_name , bs.type bstype , MAX(backup_finish_date) MAXbackup_finish_date FROM msdb.dbo.backupset bs GROUP BY bs.database_name , bs.type ), MainBigSet AS ( SELECT @@SERVERNAME servername, db.name , db.state_desc , db.recovery_model_desc , bs.type , convert(decimal(10,2),bs.backup_size/1024.00/1024) backup_sizeinMB, bs.backup_start_date, bs.backup_finish_date, physical_device_name, DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date) AS DurationMins FROM master.sys.databases db LEFT OUTER JOIN backupsetSummary bss ON bss.database_name = db.name LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name AND bss.bstype = bs.type AND bss.MAXbackup_finish_date = bs.backup_finish_date JOIN msdb.dbo.backupmediafamily m ON bs.media_set_id = m.media_set_id where db.database_id>4 ) SELECT servername, name, state_desc, recovery_model_desc, Last_Backup = MAX(a.backup_finish_date), Last_Full_Backup_start_Date = MAX(CASE WHEN A.type='D' THEN a.backup_start_date ELSE NULL END), Last_Full_Backup_end_date = MAX(CASE WHEN A.type='D' THEN a.backup_finish_date ELSE NULL END), Last_Full_BackupSize_MB= MAX(CASE WHEN A.type='D' THEN backup_sizeinMB ELSE NULL END), FULLDurationSeocnds = MAX(CASE WHEN A.type='D' THEN DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) ELSE NULL END), Last_Full_Backup_path = MAX(CASE WHEN A.type='D' THEN a.physical_Device_name ELSE NULL END), Last_Diff_Backup_start_Date = MAX(CASE WHEN A.type='I' THEN a.backup_start_date ELSE NULL END), Last_Diff_Backup_end_date = MAX(CASE WHEN A.type='I' THEN a.backup_finish_date ELSE NULL END), Last_Diff_BackupSize_MB= MAX(CASE WHEN A.type='I' THEN backup_sizeinMB ELSE NULL END), DIFFDurationSeocnds = MAX(CASE WHEN A.type='I' THEN DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) ELSE NULL END), Last_Diff_Backup_path = MAX(CASE WHEN A.type='I' THEN a.physical_Device_name ELSE NULL END), Last_Log_Backup_start_Date = MAX(CASE WHEN A.type='L' THEN a.backup_start_date ELSE NULL END), Last_Log_Backup_end_date = MAX(CASE WHEN A.type='L' THEN a.backup_finish_date ELSE NULL END), Last_Log_BackupSize_MB= MAX(CASE WHEN A.type='L' THEN backup_sizeinMB ELSE NULL END), LOGDurationSeocnds = MAX(CASE WHEN A.type='L' THEN DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) ELSE NULL END), Last_Log_Backup_path = MAX(CASE WHEN A.type='L' THEN a.physical_Device_name ELSE NULL END), [Days_Since_Last_Backup] = DATEDIFF(d,(max(a.backup_finish_Date)),GETDATE()) into #temp FROM MainBigSet a group by servername, name, state_desc, recovery_model_desc -- order by name,backup_start_date desc SET @tableHTML = N'<H1>Databases Backup Report</H1>' + N'<table border="1">' + N'<tr> <th>Server Name</th> <th>DatabaseName</th> <th>state_desc</th> <th>recovery_model_desc</th> <th>last_backup_rundatetime</th> <th>FULL_backup_start_date</th> <th>FULL_backup_end_date</th> <th>FULL_backup_size_MB</th> <th>FULL_durationInSeconds</th> <th>FULL_backup_path</th> <th>DIFF_backup_start_date</th> <th>DIFF_backup_end_date</th> <th>DIFF_backup_size_MB</th> <th>DIFF_durationInSeconds</th> <th>DIFF_backup_path</th> <th>LOG_backup_start_date</th> <th>LOG_backup_end_date</th> <th>LOG_backup_size_MB</th> <th>LOG_durationInSeconds</th> <th>LOG_backup_path</th> <th>DaysSinceLastBackup</th> </tr>' + CAST ( ( SELECT td=servername,' ', td=name,' ', td=state_desc,' ', td=recovery_model_desc,' ', td=Last_Backup,' ', td=Last_Full_Backup_start_Date,' ', td=Last_Full_Backup_end_date, ' ', td=Last_Full_BackupSize_MB, ' ', td=FULLDurationSeocnds, ' ', td=Last_Full_Backup_path, ' ', td=Last_Diff_Backup_start_Date, ' ', td= Last_Diff_Backup_end_date, ' ', td= Last_Diff_BackupSize_MB,' ', td=DiffDurationSeocnds,' ', td=Last_Diff_Backup_path, ' ', td=Last_Log_Backup_start_Date,' ', td=Last_Log_Backup_end_date,' ', td=Last_Log_BackupSize_MB,' ', td=LogDurationSeocnds,' ', td=Last_Log_Backup_path,' ', td=Days_Since_Last_Backup, ' ' FROM #temp a FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; -- order by name,backup_start_date desc EXEC msdb.dbo.sp_send_dbmail @profile_name='PowerSQL', @recipients='pjayaram@appvion.com', @subject = 'Database Backup', @body = @tableHTML, @body_format = 'HTML' ; drop table #temp |
That’s all for now…
Wrapping up
So far we’ve seen how to manage the handle the backup reports. We also raised the importance of reports and its implications on the existing system. We can set up monitoring that allows administrators to take immediate corrective action rather than finding out later when reports are read. We can also store the history in a centralized repository and it will help to identify the backup performance over time.
Growth trends and reports give the ability to forecast the required storage and make capacity decisions before they become issues.
Before selecting a reporting tool, it’s a good practice to establish a complete list of reporting needs and also, some “nice to have” features.
Table of contents
References
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021