Prashanth Jayaram

SQL Server Database Backup and Restore reports

June 14, 2018 by

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:

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

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

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

  1. Install SQL Server PowerShell module from the PowerShell repository hub PSGallery. The instructions in the link detail the direction for installing SQL Server module
  2. Prepare the servers list in a CSV file and import the CSV file using import-csv cmdlets
  3. Prepare the simple PowerShell script by instantiating SMO class libraries. PowerShell allows leveraging cmdlets and objects through a concept known as piping.
  4. In the following example, we can see that how the objects are inherited its properties from a database

PowerShell script


Let’s dissect the script

  1. The input file, CSV file, contains the server names
  2. The output file, HTML type, the string output is converted to HTML format using string concatenation
  3. 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
  4. Use PowerShell cmdlets to verify and install the SQL Server module from PSGallery
  5. 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.
  6. Build a PowerShell cmdlet to gather a data set based on the conditions.
  7. The logical condition is defined with an assumption of weekly full, daily differential and hourly t-log backups.
  8. The color combination highlights those databases which require immediate measures to be taken as per the defined SLA.
  9. Define the email notification system

Let’s save the following content Databasbackup.ps1.

Output

Backup reports using T-SQL

Let’s discuss the report generation using T-SQL

This SQL has three sections

  1. Full backup status
  2. Differential backup status
  3. 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

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

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

Database Backup and Restore process in SQL Server – series intro
An overview of the process of SQL Server backup-and-restore
Understanding the SQL Server Data Management Life Cycle
Understanding SQL Server database recovery models
Understanding SQL Server Backup Types
Backup and Restore (or Recovery) strategies for SQL Server database
Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
Understanding Database snapshots vs Database backups in SQL Server
SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
Smart database backup in SQL Server 2017
How to perform a Page Level Restore in SQL Server
Backup Linux SQL Server databases using PowerShell and Windows task scheduler
SQL Server Database backup and restore operations using the Cloud
Tail-Log Backup and Restore in SQL Server
SQL Server Database Backup and Restore reports
Database Filegroup(s) and Piecemeal restores in SQL Server
In-Memory Optimized database backup and restore in SQL Server
Understanding Backup and Restore operations in SQL Server Docker Containers
Backup and Restore operations with SQL Server 2017 on Docker containers using Azure Data Studio
Interview questions on SQL Server database backups, restores and recovery – Part I
Interview questions on SQL Server database backups, restores and recovery – Part II
Interview questions on SQL Server database backups, restores and recovery – Part III
Interview questions on SQL Server database backups, restores and recovery – Part IV

References


Prashanth Jayaram
Backup and restore, Reports

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. 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

168 Views