Aamir Syed

SQL backup reports with PowerShell

August 15, 2016 by

Let me preface this post by saying that this was a process that evolved over time. It started as a simple sql statement that emailed me a csv file. From there the actual SQL code evolved to display cleaner results. When I learned what powershell could do, I spent some time learning it during my lunch breaks at work. Even when I implemented a powershell script for the first time, it still wasn’t a clean looking report, but it was still better than what we had prior. Even now there is room for improvement but I am happy with the results and hope that it can provide some help to other SQL DBA’s out there.

Also, in the spirit of full disclosure, I do not have a formal programming background. The first half of my career was that of a sys admin. The fact that I can learn powershell means just about anyone can!

So, let us proceed.

We have a mix of backup systems for our SQL environment ranging from NetApp snap manager, Commvault tape backup, and even good old fashioned SQL Native. The reason for this is that the netapp allows for quick FULL backups on even the largest databases every night, as well as transaction log backups. It’s very flexible and does restores very quickly and easily.

The reason for the commvault tape backup is that we are required to have a weekly full backup to tape brought off sight for the sake of DR.

And the SQL native is because we don’t have enough NetApp or Commvault SQL licenses for these particular servers (these are not considered mission critical). Therefore we time the SQL native backup to happen prior to the file system backup window.

Now that we have all that cleared up, the first thing we need to do is write the SQL statement that will display this information on a given server.

Key points in this query are differentiating the backup types and backup systems. My boss and I decided to write a subquery with the union all feature based on the backup type. Then we would further filter it with the use of a subquery telling us to only report backups done by Netapp, Commvault and SQL Native. (Then of course we implemented order and group by for the sake of cleaner looking results). Also notice that we use the “max” command to get the most recent backup dates and times. Then use DATEDIFF to do a little math so we can create a column displaying the days since last full. This made it easy to read as we were going down the list and as you’ll see later, once I implement powershell, I made it so that anything over than 7 days since the last full backup was highlighted in RED. This makes it easy to see if there was a problem or delay in the backups. Obviously we can get quite granular with this, but our DBA team felt that this was enough information to determine whether the backups were consistent.

Next question is, how can I turn this into a powershell report and email me a nice clean html embedded into an email message? Well first I needed to learn some powershell. I used a combination of google and a couple of books (that I will link at the bottom of this post).

I like to do my work in the included Windows PowerShell ISE with the editor on top and console output on the bottom:

Let’s get to the basics, first I have to import the SQL Powershell module.

Well that was easy enough. Next, I learned through lots of reading that I prefer to write the bulk of my script as a function and then call said function within the script. This function will take a list of servers from a file and loop through each server and execute the SQL query that we’ve written, thus gathering all the relevant data in one swoop (or loop….*groan* ok bad joke).

So you can see that I create the function and then, as the comments explain, I place the contents of my servers list (PD.txt in this case) into a variable. I loop through each item in this variable (now an array since it has a bunch of items stored in it). Then with each server name I create what’s known as an SMO object (aka – SQL management Object). This basically allows us to manage SQL server through the powershell programming language.

I place the contents of our beloved SQL statement into a variable called $Query. Then use the Invoke-SQLcmd powershell command to execute the query against the database server. And that’s it for the function. Now we make sure to close parenthesis to encompass the function. And now we can call it.

This is cool because it provides us with the information in powershell console. But now I want to pipe these results into an html file. Now, the regular ConvertTo-HTML file powershell command is fine for basic tasks. But it wasn’t exactly giving me what I was looking for.

I needed this to be readable. So the first thing I did was dig into one of my books and found a way to setup the HTML. I have no background in HTML and wasn’t looking to become an expert in a few hours. I was just looking for what I needed. I found this as a way to set the table borders, titles and the colors for the titles.

In my research to figure out how to make my html reports look nice, I came across how to create html reports

Now this is what I was looking for! Two functions you can add to your powershell scripts are Set-AlternatingRows, and Set-CellColor. His site can give you the nitty gritty details on them. I only utilized them for what I needed.

It might be best to import them into your script rather than just copy and paste them in order to make your script more readable. However, I included them in their entirety in the full version of the script at the end of the post for the sake of completeness.

So as you can see, I call the function and use pipelines to gather my information. Convert the data into html (include the header and precontent information). I then call the surly admin’s custom set-cellcolor to color the “DaysSinceLastFull” field red, only if its value is greater or equal than 7. Then I use the Set-AlternatingRows to color every other row (I used grey in this case) to make it more readable. Finally, I output the results into an HTML file.

Example of output (blurred out server and db names):

Now I wanted this sent in an email.

For this I just used the Powershell send mail message function. Attached the document and then used the “bodyAsHtml” option to fill the body with the contents of the html output file).

Example Embedded in Email (and attachment which is highlighted):

Here is the full script in final form.

References:

Backup and restore, PowerShell, Reports

About Aamir Syed

Aamir is a SQL Server Database Administrator in the NYC/NJ area (and has recently taken a role as a Database Developer). He started his IT career in helpdesk world and eventually moved into the networking/systems administrator side of things. After some time, he developed an affection for working with Databases (mainly SQL Server) and has focused his career on that for the past eight years. He has worked for various industries in both large and small environments all with different needs. SQL Shack has provided him with an opportunity to contribute to a community that has given him so much throughout the years. View all posts by Aamir Syed

168 Views