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.

在本文中,我们将讨论数据库备份报告的重要性。 这种报告的目的是消除信息安全性可能引起的任何不必要的风险。 这就是原因,高级别报告; 每日健康检查报告将生成并发送给SME或负责IT Infra管理的DBA。

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

通常,管理员非常关注每天获取报告以及根据定义的SLA发出的任何警报。 管理员依靠备份报告来了解备份以及备份的工作方式,并且一直希望保护数据

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

    这是最重要的度量标准,在大多数情况下,需要立即注意并采取行动。 对于测试或开发环境,这可能需要等待基于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.

显然,备份期限,备份作业的完成状态以及警报和定期通知被认为是任何备份报告应提供的最基本功能。 实际上,备份工具提供了某些形式的每日备份报告。 话虽这么说,大多数备份工具在备份数据方面做得非常出色,并且在某些情况下,报告不是用户友好的,因此需要进行自定义。

使用PowerShell SMO库生成备份报告 (Generating backup reports using PowerShell SMO libraries)

You can customize the PowerShell script and/or T-SQL schedule the job as per the requirement.

您可以根据需要自定义PowerShell脚本和/或T-SQL安排作业。

准备脚本 (Prepare the script)

  1. link detail the direction for installing SQL Server module 链接中的说明详细说明了安装SQL Server模块的方向
  2. import-csv cmdlets import-csv cmdlet导入CSV文件
  3. 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和对象。
  4. 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

我们来剖析脚本

  1. The input file, CSV file, contains the server names

    输入文件CSV文件包含服务器名称
  2. The output file, HTML type, the string output is converted to HTML format using string concatenation

    输出文件,HTML类型,使用字符串连接将字符串输出转换为HTML格式
  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

    电子邮件列表,此参数包含收件人的电子邮件ID。 您可以有一个或多个,并且每个ID必须用逗号分隔
  4. Use PowerShell cmdlets to verify and install the SQL Server module from PSGallery

    使用PowerShell cmdlet从PSGallery验证并安装SQL Server模块
  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.

    定义包含预定义HTML样式CSS(层叠样式表),HTML数据格式将参考这些样式。 CSS包含许多样式定义。 例如,定义表格样式,背景颜色,边框和标题等等。 构建HTML文档以获取所需的格式化效果和结果非常简单。
  6. Build a PowerShell cmdlet to gather a data set based on the conditions.

    生成PowerShell cmdlet以根据条件收集数据集。
  7. The logical condition is defined with an assumption of weekly full, daily differential and hourly t-log backups.

    逻辑条件是根据每周完整备份,每日差异备份和每小时t-log备份进行定义的。
  8. The color combination highlights those databases which require immediate measures to be taken as per the defined SLA.

    颜色组合突出显示了那些需要根据定义的SLA立即采取措施的数据库。
  9. 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有三个部分

  1. Full backup status

    完整备份状态
  2. Differential backup status

    差异备份状态
  3. 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)

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
SQL Server中的数据库备份和还原过程–系列简介
SQL Server备份和还原过程概述
了解SQL Server数据管理生命周期
了解SQL Server数据库恢复模型
了解SQL Server备份类型
SQL Server数据库的备份和还原(或恢复)策略
讨论使用SQLCMD和SQL Server代理进行备份和还原自动化
了解SQL Server中的数据库快照与数据库备份
SqlPackage.exe –使用bacpac和PowerShell或Batch技术自动执行SQL Server数据库还原
SQL Server 2017中的智能数据库备份
如何在SQL Server中执行页面级还原
使用PowerShell和Windows任务计划程序备份Linux SQL Server数据库
使用CloudSQL Server数据库备份和还原操作
SQL Server中的尾日志备份和还原
SQL Server数据库备份和还原报告
SQL Server中的数据库文件组和零碎还原
在SQL Server中进行内存优化的数据库备份和还原
了解SQL Server Docker容器中的备份和还原操作
使用Azure Data Studio在Docker容器上使用SQL Server 2017进行备份和还原操作
有关SQL Server数据库备份,还原和恢复的面试问题–第一部分
有关SQL Server数据库备份,还原和恢复的面试问题–第二部分
有关SQL Server数据库备份,还原和恢复的面试问题–第三部分
有关SQL Server数据库备份,还原和恢复的面试问题–第IV部分

参考资料 (References)

翻译自: https://www.sqlshack.com/sql-server-database-backup-and-restore-reports/

Logo

腾讯云面向开发者汇聚海量精品云计算使用和开发经验,营造开放的云计算技术生态圈。

更多推荐