推荐本站淘宝优惠价购买喜欢的宝贝:
link detail the direction for installing SQL Server module 链接中的说明详细说明了安装SQL Server模块的方向
import-csv cmdlets import-csv cmdlet导入CSV文件
Prepare the simple PowerShell script by instantiating SMO class libraries. PowerShell allows leveraging cmdlets and objects through a concept known as piping.
通过实例化SMO类库来准备简单的PowerShell脚本。 PowerShell允许通过称为管道的概念来利用cmdlet和对象。
In the following example, we can see that how the objects are inherited its properties from a database
在下面的示例中，我们可以看到对象如何从数据库继承其属性。
PowerShell脚本 (PowerShell script)
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
输入文件CSV文件包含服务器名称
The output file, HTML type, the string output is converted to HTML format using string concatenation
输出文件，HTML类型，使用字符串连接将字符串输出转换为HTML格式
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
电子邮件列表，此参数包含收件人的电子邮件ID。 您可以有一个或多个，并且每个ID必须用逗号分隔
Use PowerShell cmdlets to verify and install the SQL Server module from PSGallery
使用PowerShell cmdlet从PSGallery验证并安装SQL Server模块
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.
定义包含预定义HTML样式CSS（层叠样式表），HTML数据格式将参考这些样式。 CSS包含许多样式定义。 例如，定义表格样式，背景颜色，边框和标题等等。 构建HTML文档以获取所需的格式化效果和结果非常简单。
Build a PowerShell cmdlet to gather a data set based on the conditions.
生成PowerShell cmdlet以根据条件收集数据集。
The logical condition is defined with an assumption of weekly full, daily differential and hourly t-log backups.
逻辑条件是根据每周完整备份，每日差异备份和每小时t-log备份进行定义的。
The color combination highlights those databases which require immediate measures to be taken as per the defined SLA.
颜色组合突出显示了那些需要根据定义的SLA立即采取措施的数据库。
Define the email notification system
定义电子邮件通知系统
Let’s save the following content Databasbackup.ps1.
让我们保存以下内容Databasbackup.ps1。
#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)
使用T-SQL备份报告 (Backup reports using T-SQL)
Let’s discuss the report generation using T-SQL
让我们讨论使用T-SQL生成报告
This SQL has three sections
该SQL有三个部分
Full backup status
完整备份状态
Differential backup status
差异备份状态
T-log backup status
T-log备份状态
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.
我将讨论脚本的完整备份部分。 同样适用于其他备份类型。 第一部分是关于聚合和转换的所有内容，以从msdb.dbo.backupset获取所有行。 通过从特定备份类型的系统对象中获取最新行来完成聚合。 通过将多行行转换为数据库的列来完成转换。 该列还包括backup_size和完成所需的时间。
If required, you can refer the link backup strategy on how to pull the data into a central repository using T-SQL and Powershell.
如果需要，您可以参考链接备份策略，以了解如何使用T-SQL和Powershell将数据提取到中央存储库中。
准备SQL (Prepare SQL )
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输出 (T-SQL output)
使用XML创建HTML备份报告 (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.
在本节中，我们将讨论使用FOR XML子句生成HTML标记。 它提供了一种将SQL查询的结果转换为XML的方法。 复杂SQL数据被推送到名为#temp的临时表中。 这有助于以一种更简单的方式将SQL数据转换为XML。 您可以参考XML链接以获取更多信息。
Next, define the SQL text fields as data sections using FOR XML PATH clause and define the XML schema.
接下来，使用FOR XML PATH子句将SQL文本字段定义为数据部分，并定义XML模式。
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
配置SQL Server代理邮件以使用数据库邮件并传递XML字符串为HTML类型数据，以将HTML输出发送给预期的收件人
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)
翻译自: https://www.sqlshack.com/sql-server-database-backup-and-restore-reports/
本文链接：https://hqyman.cn/post/6207.html 非本站原创文章欢迎转载，原创文章需保留本站地址！
打赏
微信支付宝扫一扫，打赏作者吧～
休息一下~~
官码2024000195号
萌ICP备20248119号
