使用PowerShellSQL备份报告

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.

首先，我要说这是一个随着时间而发展的过程。 它以简单的sql语句开始，通过电子邮件将csv文件发送给我。 从那里开始，实际SQL代码演变为显示更清晰的结果。 当我了解了powershell可以做什么时，我花了一些时间在工作中的午餐时间学习它。 即使当我第一次实现Powershell脚本时，它仍然不是一个干净的报告，但是它比我们以前的报告要好。 即使现在仍有改进的空间，但是我对结果感到满意，并希望它可以为其他SQL DBA提供帮助。

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!

同样，本着完全公开的精神，我没有正式的编程背景。 我的职业生涯的前半段是系统管理员。 我可以学习Powershell的事实意味着几乎任何人都可以！

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.

我们为SQL环境提供了多种备份系统，包括NetApp快照管理器，Commvault磁带备份，甚至是老式SQL Native。 原因是netapp允许每晚在最大的数据库上进行快速的FULL备份，以及事务日志备份。 它非常灵活，确实可以非常快速，轻松地进行还原。

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.

进行Commvault磁带备份的原因是，为了进行灾难恢复，我们需要每周对磁带进行一次完整备份。

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.

而SQL本机是因为我们没有足够的NetApp或Commvault SQL许可用于这些特定服务器（这些服务器不视为任务关键）。 因此，我们将SQL本机备份的时间安排在文件系统备份窗口之前。

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.

现在，我们已经清理了所有内容，我们要做的第一件事是编写将在给定服务器上显示此信息的SQL语句 。

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.

此查询的关键点在于区分备份类型和备份系统。 我的老板和我决定根据备份类型编写一个具有union all功能的子查询。 然后，我们将使用子查询进一步过滤它，该子查询告诉我们仅报告由Netapp，Commvault和SQL Native完成的备份。 （然后，我们当然是为了更清晰地查看结果而实施了排序和分组）。 还要注意，我们使用“ max”命令来获取最新的备份日期和时间。 然后使用DATEDIFF进行一些数学运算，以便我们可以创建一列以显示自上次满满以来的天数。 这使我们很容易阅读，就像我们将要列出的那样，稍后您将看到，一旦实现了powershell，我就可以做到，使得自上次完整备份以来超过7天的所有内容都以RED突出显示。 这样可以轻松查看备份是否存在问题或延迟。 显然，我们可以对此进行细化，但是我们的DBA团队认为这是足以确定备份是否一致的信息。

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

下一个问题是，如何将其转换为Powershell报告并通过电子邮件将嵌入到电子邮件中的漂亮的html发送给我？ 首先，我需要学习一些功能。 我使用了google和几本书的组合（我将在本文的底部链接）。

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

我喜欢在随附的Windows PowerShell ISE中进行工作，顶部是编辑器，底部是控制台输出：

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

让我们开始做基础，首先我必须导入SQL Powershell模块。

 
  #import SQL Server module
  Import-Module SQLPS -DisableNameChecking 
 

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

好吧，这很容易。 接下来，我通过大量阅读了解到，我更喜欢将脚本的大部分内容编写为一个函数，然后在脚本中调用该函数。 此功能将从文件中获取服务器列表，并循环遍历每台服务器，并执行我们编写SQL查询，从而一口气收集所有相关数据（或循环……。

 
 function Get-DBBackup-Type{
 
    #List of servers from text file
    $serverlist = Get-Content -Path C:\PowerShell\PD.txt
 
    #Loop through each server and create SMO object)
foreach ($serverName in $serverlist){
 
        #create smo object
        $SQLServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $ServerName 
 

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.

因此，您可以看到我创建了函数，然后，如注释所解释的，我将服务器列表（在本例中为PD.txt）的内容放入了变量。 我循环遍历此变量中的每个项目（现在是数组，因为其中存储了一堆项目）。 然后，使用每个服务器名称创建一个称为SMO对象（又称SQL管理对象）的对象。 基本上，这使我们可以通过Powershell编程语言来管理SQL Server。

 
 $Query = “Place contents of Query in here”
 
#Use SQLCmd to execute the query on the server
Invoke-Sqlcmd -ServerInstance $serverName -Query $Query 
 

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.

我将我们钟爱SQL语句的内容放入一个名为$ Query的变量中。 然后，使用Invoke-SQLcmd powershell命令对数据库服务器执行查询。 这就是功能。 现在，我们确保关闭括号以包含该函数。 现在我们可以称之为。

 
  Get-DBBackup-Type | Select ServerName, DatabaseName, BackupSystem, FullBackup, DifferentialBackup, LogBackup, DaysSinceLastFull 
 

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.

这很酷，因为它为我们提供了Powershell控制台中的信息。 但是现在我想将这些结果通过管道传输到html文件中。 现在，常规的ConvertTo-HTML文件powershell命令适合基本任务。 但这并不能完全满足我的需求。

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.

我需要这是可读的。 因此，我要做的第一件事就是深入研究一本书，并找到一种设置HTML的方法。 我没有HTML背景，也不想在几个小时内成为专家。 我只是在寻找我需要的东西。 我发现这是一种设置表格边框，标题和标题颜色的方法。

 
 #Setup HTML
$Header = @"
		<style>
		TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
		TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: LightBlue;}
		TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}
		.odd  { background-color:#ffffff; }
		.even { background-color:#dddddd; }
		</style>
		
<title>
DV Backup Reports
</title>
"@
$Pre = "<h1>PD BACKUP REPORTS</h1>" 
 

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

为了弄清楚如何使html报告看起来更好，我进行了研究，发现了如何创建html报告

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.

现在这就是我想要的！ 您可以添加到Powershell脚本中的两个函数是Set-AlternatingRows和Set-CellColor。 他的网站可以为您提供有关这些细节的详细信息。 我只是利用它们来满足需要。

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.

最好将它们导入到脚本中，而不是仅复制并粘贴它们以使脚本更具可读性。 但是，为了完整起见，我在文章末尾将它们完整地包含在脚本的完整版本中。

 
  Get-DBBackup-Type | Select ServerName, DatabaseName, BackupSystem, FullBackup, DifferentialBackup, LogBackup, DaysSinceLastFull | ConvertTo-Html -Head $Header -PreContent $Pre | Set-CellColor -Property DaysSinceLastFull -Color red -Filter "DaysSinceLastFull -ge 7" | Set-AlternatingRows -CSSEvenClass even -CssOddClass Odd | Out-File C:\PowerShell\PD_BACKUP_REPORT.Html
 

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.

如您所见，我调用该函数并使用管道来收集我的信息。 将数据转换为html（包括标题和内容前信息）。 然后，仅当其值大于或等于7时，才调用高级管理员的自定义set-cellcolor将“ DaysSinceLastFull”字段着色为红色。然后，我使用Set-AlternatingRows为其他每行着色（在这种情况下，我使用了灰色）使其更具可读性。 最后，我将结果输出到HTML文件中。

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

输出示例（模糊服务器名称和数据库名称）：

Now I wanted this sent in an email.

现在，我希望通过电子邮件发送此邮件。

 
  Send-MailMessage -to "email <email@domain.com" -from "SQLAdmin <sqladmin@domain.com>" -Subject "PD Database Backup Report" -SmtpServer mail.smtpserver.com -Attachments "C:\PowerShell\PD_BACKUP_REPORT.Html" -BodyAsHtml (Get-Content C:\PowerShell\PD_BACKUP_REPORT.Html | Out-String) 
 

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

为此，我只使用了Powershell发送邮件功能。 附加文档，然后使用“ bodyAsHtml”选项用html输出文件的内容填充正文。

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

嵌入电子邮件的示例（突出显示的附件）：

Here is the full script in final form.

这是最终形式的完整脚本。

参考文献： ( References: )

• Learn Windows Powershell in a Month of Lunches 在一个月的午餐中学习Windows Powershell
• Sql server 2012 with powershell v3 cookbook 带有Powershell V3食谱SQL Server 2012
• The Surly Admin (to get the Set-color and Set-AlternatingRows functions among lots of other useful content.) Surly Admin （用于获取Set-color和Set-AlternatingRows函数以及其他许多有用的内容。）
• Send-MailMessage on Technet Technet上的Send-MailMessage
• 
  
  

翻译自: https://www.sqlshack.com/sql-backup-reports-powershell/