使用PowerShell进行备份测试–第2部分：报告结果

Now that Karla’s restore process is mature, it is time to pre-stage the means of reporting on these events. Two major actions have been taken for each database tested. First a restore operation occurred. The restore operation validates that the backup file is well formed and that there is nothing wrong with the data which would cause a failure. Next CheckTables was executed to thoroughly inspect for data corruption and any consistency errors. Each of these two actions will have their own methods of providing evidence of successful completion. Recall the requirements set by Karla, in part 1 of this series, for the reporting piece.

现在，Karla的还原过程已经成熟，现在是时候准备报告这些事件的方法了。 对于每个测试的数据库，已采取了两项主要措施。 首先发生还原操作。 还原操作将验证备份文件的格式正确，并且数据没有问题，不会导致故障。 接下来执行CheckTables以彻底检查数据损坏和任何一致性错误。 这两个动作中的每个动作都有其自己的方法来提供成功完成的证据。 回想一下Karla在本系列的第1部分中对报告的要求。

• Evidence of test and results must be logged as part of the automated system.
  测试和结果的证据必须作为自动化系统的一部分进行记录。
• • Queries must be pre-written and available to be run by unprivileged users such as via SQL Server Reporting Services.
    查询必须是预先编写的并且可以由非特权用户运行，例如通过SQL Server Reporting Services运行。

The first requirement is that the logging must be part of the automated system. This means that we need to create a database and a couple of tables to record the results, in addition to some tweaks to the Restore-Database function. Before we walk down that path, however, let us take a look at what is available directly from SQL Server system tables and commands so it becomes clear why this work is necessary.

第一个要求是日志记录必须是自动化系统的一部分。 这意味着，除了对Restore-Database函数进行一些调整之外，我们还需要创建一个数据库和几个表来记录结果。 但是，在走这条路之前，让我们看一下可直接从SQL Server系统表和命令获得的内容，从而清楚了为什么需要这项工作。

内置命令用于完整性检查证据 (Built-in command for integrity check evidence)

 
DBCC DBINFO ('Test11') WITH TABLERESULTS
 

DBCC DBINFO is an undocumented DBCC command. DBINFO returns a wealth of information but what is important to integrity checking is the dbi_dbccLastKnownGood field. This field has a DATETIME value of when the last successful integrity check were conducted.

DBCC DBINFO是未记录的DBCC命令。 DBINFO返回了大量信息，但是对于完整性检查而言重要的是dbi_dbccLastKnownGood字段。 该字段的DATETIME值为上一次成功执行完整性检查的时间。

There are two problems with this method, however. First, it is undocumented. I would not fault someone for using an undocumented command, but when there is another way, it should usually be considered because the syntax or operations can change without notice with version upgrades. In addition, they are not officially supported so bugs may occur without fix.

但是，这种方法有两个问题。 首先，它是无证的。 我不会指责某人使用未记录的命令，但是如果有另一种方法，通常应该考虑使用它，因为语法或操作可能会随着版本升级而更改，而不会发出通知。 此外，它们不受官方支持，因此可能会在未修复的情况下发生错误。

The second problem is the most important. This command can only be executed on an existing database. Given that Karla has a static test server which will likely have limited disk space, she needs to drop the databases after she is finished testing them. By dropping the databases only the SQL Server error logs remain to parse out integrity evidence and those rotate out of existence over time.

第二个问题是最重要的。 该命令只能在现有数据库上执行。 鉴于Karla具有静态测试服务器（可能会限制磁盘空间），她需要在完成数据库测试后删除数据库。 通过删除数据库，仅保留SQL Server错误日志以解析出完整性证据，而随着时间的推移，这些证据将不复存在。

内置查询以获取还原证据 (Built-in query for restore evidence)

 
SELECT MAX( restore_date ) AS LastRestoreDate 
    , destination_database_name AS databaseName
FROM msdb.dbo.restorehistory
GROUP BY destination_database_name;
 

This query will access MSDB’s restorehistory table. Every time a restore operation completes, a record is logged in this table. This is how one could prove that the restore for a particular database occurred and when.

该查询将访问MSDB的restorehistory表。 每次还原操作完成时，此表中都会记录一条记录。 这样可以证明特定数据库的还原发生在何时以及何时进行。

NOTE: The system stored procedure sp_delete_backuphistory will purge records from this table. The KillDatabase method that was covered in part 1 does NOT call this procedure nor does T-SQL’s DROP DATABASE command. Dropping a database with SSMS, however, has this option checked by default. The check-box to beware of is labeled, “Delete backup and restore history information for databases.”

注意：系统存储过程sp_delete_backuphistory将从该表中清除记录。 第1部分中介绍的KillDatabase方法不会调用此过程，T-SQL的DROP DATABASE命令也不会。 但是，使用SSMS删除数据库时，默认情况下会选中此选项。 要注意的复选框标记为“删除数据库的备份和还原历史记录信息”。

There is a fear that the sp_delete_backuphistory stored procedure could be executed and the results get purged. This is a low risk in my opinion, though, because proper access and development discipline can prevent this from happening. Another drawback is that long term storage of data in this table can affect performance of MSDB. This is a low risk as well because it can be handled with an archive strategy.

担心可能会执行sp_delete_backuphistory存储过程并清除结果。 我认为这是低风险，因为正确的访问和开发纪律可以阻止这种情况的发生。 另一个缺点是该表中数据的长期存储会影响MSDB的性能。 这也是低风险的，因为可以通过存档策略进行处理。

As can be seen, restore evidence can be retrieved with minimal concerns. So why should Karla create new tables? Because enough concerns with the integrity checks are present to need its results logged, and having the evidence of the restores linked to the integrity checks will make for a clearer data set for the reports.

可以看出，可以以最少的担心来检索恢复证据。 那么，Karla为什么要创建新表？ 因为存在与完整性检查有关的足够问题，需要记录其结果，并且将恢复的证据链接到完整性检查将为报告提供更清晰的数据集。

But I digress…

但是我离题了……

数据库设计 (Database design)

The second requirement is that test results must be available without DBA interaction. This is critical because audits can be very time consuming. If the DBA is needed to provide evidence too often then the quality of their work may suffer or responsiveness to issues degrade. In addition, if the DBA were to implement this system and then deescalate themselves, auditors would be comfortable that the data has not been tampered with. To handle the need for unprivileged users to run the queries, the results will be provided via SSRS. So let us build the foundation of the reports, the database and table structure.

第二个要求是测试结果必须在没有DBA交互的情况下可用。 这很关键，因为审核可能非常耗时。 如果需要DBA经常提供证据，那么他们的工作质量可能会受到影响，或者对问题的响应能力会下降。 此外，如果DBA实施此系统然后自行降级，则审计师会感到满意，因为数据没有被篡改。 为了满足非特权用户运行查询的需求，将通过SSRS提供结果。 因此，让我们构建报表，数据库和表结构的基础。

 
USE master
CREATE DATABASE BackupTest;
GO
 

There are no special requirements for database creation for this process. Recommend configuring your database in accordance with your organization’s standards.

对于此过程，数据库创建没有特殊要求。 建议根据组织的标准配置数据库。

 
USE BackupTest
CREATE TABLE [dbo].[RestoreResult]
(
    restoreResultId UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_RestoreResult_restoreResultId PRIMARY KEY NONCLUSTERED,
    originatingServerName SYSNAME NOT NULL,
    databaseName SYSNAME NOT NULL,
backupFilePath NVARCHAR(256) NOT NULL,
    startDateTime DATETIME2(3) NOT NULL CONSTRAINT DF_RestoreResult_startDateTime DEFAULT (SYSUTCDATETIME()),
    endDateTime DATETIME2(3) NULL,
    errorMessage VARCHAR(MAX) NULL
);
 
CREATE CLUSTERED INDEX IX_RestoreResult_endDateTime_startDateTime 
    ON [dbo].[RestoreResult] (endDateTime DESC, startDateTime DESC);
 
CREATE NONCLUSTERED INDEX IX_RestoreResult_databaseName_originatingServerName
    ON [dbo].[RestoreResult] (databaseName ASC, originatingServerName ASC);
 

In RestoreResult table there will be a globally unique identifier (GUID) for uniquely identifying the row and linking the to-be-created CheckDbResult table. The originating server, database names, and backup file paths are stored to track what was checked. Finally, the start and end dates, and error messages are stored as evidence of the restore operation and its success or failure.

在RestoreResult表中，将有一个全局唯一标识符（GUID），用于唯一地标识该行并链接要创建的CheckDbResult表。 存储原始服务器，数据库名称和备份文件路径以跟踪检查的内容。 最后，存储开始和结束日期以及错误消息，作为还原操作及其成功或失败的证据。

 
USE BackupTest
 
CREATE TABLE [dbo].[CheckDbResult]
(
    checkDbResult UNIQUEIDENTIFIER NOT NULL CONSTRAINT PK_CheckDbResult_checkDbResult PRIMARY KEY NONCLUSTERED,
    restoreResultId UNIQUEIDENTIFIER NOT NULL CONSTRAINT FK_CheckDbResult_restoreResultId FOREIGN KEY REFERENCES [dbo].[RestoreResult] (restoreResultId),
    startDateTime DATETIME2(3) NOT NULL CONSTRAINT DF_CheckDbResult_startDateTime DEFAULT (SYSUTCDATETIME()),
    endDateTime DATETIME2(3) NULL,
    errorMessage VARCHAR(MAX) NULL
);
 
CREATE CLUSTERED INDEX IX_CheckDbResult_endDateTime_startDateTime 
    ON [dbo].[CheckDbResult] (endDateTime DESC, startDateTime DESC);
 
CREATE NONCLUSTERED INDEX IX_CheckDbResult_restoreResultId
    ON [dbo].[CheckDbResult] (restoreResultId ASC);
 

In the CheckDbResult table there is a GUID as the primary key and a foreign key reference to the RestoreResult table. Date fields and error message columns are once again used to indicate the status of the integrity checks.

在CheckDbResult表中，有一个GUID作为主键，并且有一个外键引用到RestoreResult表。 日期字段和错误消息列再次用于指示完整性检查的状态。

记录机制 (Logging mechanism)

As noted, there was no logging coded in part 1. This gave an understanding of how to automate the restore process but now there is need for a few tweaks. The Restore-Database function and our inputs into the script will be modified so that the logging can occur on each of the asynchronous threads.

如前所述，第1部分中没有记录日志。这使您了解如何自动执行还原过程，但是现在需要进行一些调整。 Restore-Database函数和我们对脚本的输入将被修改，以便可以在每个异步线程上进行日志记录。

First the script input needs one new parameter.

首先，脚本输入需要一个新参数。

 
[Parameter(Mandatory=$true)]
[ValidateNotNullorEmpty()]
[string]$loggingDbName, 
 

The $loggingDbName is fairly self-explanatory. This will be the name of the database which contains the RestoreResult and CheckDbResult tables.

$ loggingDbName是不言自明的。 这将是包含RestoreResult和CheckDbResult表的数据库的名称。

Next there is the new parameters for the Restore-Database function.

接下来是“还原数据库”功能的新参数。

 
[parameter(Mandatory=$true)]
[ValidateNotNullorEmpty()]
[string]$origServerName,
 
[parameter(Mandatory=$true)]
[ValidateNotNullorEmpty()]
[string]$loggingDbName, 
 

Once again, the logging database name is passed in and now the originating server is added. The rest of the logged data is already available to the script and function.

再次输入日志数据库名称，现在添加了原始服务器。 其余的已记录数据已可供脚本和功能使用。

记录命令 (Logging commands)

 
#Log restore process - start
[string]$restoreResultId = [System.Guid]::NewGuid().ToString();
 
[string]$sql = "INSERT INTO [dbo].[RestoreResult]
                        ([restoreResultId]
                        ,[originatingServerName]
                        ,[databaseName]
                        ,[backupFilePath])
                    VALUES
                        ('$restoreResultId'
                        ,'$origServerName'
                        ,'$newDBName'
                        ,'$backupFilePath');"
 
Invoke-Sqlcmd -ServerInstance $serverName -Database $loggingDbName -Query $sql -QueryTimeout 30;
 

Seen above, this is a basic T-SQL INSERT command being executed utilizing existing variables to populate the VALUES section. The endDateTime and errorMessage columns are left as NULL because this command is executed immediately before the restore operation is initiated. The startDateTime is automatically populated via default constraint.

从上面可以看到，这是一个基本的T-SQL INSERT命令，该命令将使用现有变量来执行以填充VALUES部分。 endDateTime和errorMessage列保留为NULL，因为此命令在还原操作启动之前立即执行。 将通过默认约束自动填充startDateTime。

The next addition will be a T-SQL UPDATE statement, also using Invoke-SqlCmd, to populate the endDateTime column and the errorMessage column, if there was an error. To accomplish this, the restore operation’s error handling will be revised. Previously the restore command was simply on its own, now it is wrapped in Try-Catch and the error message is concatenated into a string.

下一个添加项是一条T-SQL UPDATE语句，如果有错误，也将使用Invoke-SqlCmd填充endDateTime列和errorMessage列。 为此，将修改还原操作的错误处理。 以前，restore命令只是单独运行的，现在它被包装在Try-Catch中，并且错误消息被串联成一个字符串。

 
# Restore the database
$errList = @();
try
{
    $smoRestore.SqlRestore($server)
}
catch
{
    [System.Exception]
    $err = $_.Exception
    $errList += $err;
    while ( $err.InnerException )
    {
        $err = $err.InnerException
        $errList += $err;
        write-output $err.Message
    };
}
 

An $errList variable is populated with all available exception messages. Then it is concatenated into the $errMsg variable for command building.

$ errList变量将填充所有可用的异常消息。 然后将其串联到$ errMsg变量中以用于命令构建。

 
#Log restore process - end 
$restoreEndUtc = Get-Date;
[string]$restoreEnd = $restoreEndUtc.ToUniversalTime(); 
[string]$errMsg;
foreach($msg in $errList)
{
    $errMsg += $msg + "'r'n";
}
 

Finally, here is the T-SQL UPDATE statement referred to above.

最后，这是上面提到的T-SQL UPDATE语句。

 
$sql = "UPDATE [dbo].[RestoreResult]
            SET [endDateTime] = '$restoreEnd' ";
if($errMsg -ne $null)
{
    $sql += ",[errorMessage] = '$errMsg' ";
}
$sql += "WHERE restoreResultId = '$restoreResultId';";
 
Invoke-Sqlcmd -ServerInstance $serverName -Database $loggingDbName -Query $sql -QueryTimeout 30;
 

That last Invoke-SqlCmd completes the restore process and the logging of its results. The integrity checks follow next and use the exact same format for logging. One important note is that the $restoreResultId variable is re-used in the CheckDbResult table INSERT statement so that this entire process can be linked in the reports.

最后一个Invoke-SqlCmd完成了还原过程及其结果的记录。 接下来进行完整性检查，并使用完全相同的格式进行记录。 一个重要的注意事项是$ restoreResultId变量在CheckDbResult表INSERT语句中被重新使用，以便可以在报告中链接整个过程。

 
if($conductIntegrityChecks)
{
    #Log integrity checks - start
    [string]$checkDbResultId = [System.Guid]::NewGuid().ToString();
    [string]$sql = "INSERT INTO [dbo].[CheckDbResult]
                            ([checkDbResultId]
                            ,[restoreResultId])
                        VALUES
                            ('$checkDbResultId'
                            ,'$restoreResultId');"
 
    Invoke-Sqlcmd -ServerInstance $serverName -Database $loggingDbName -Query $sql -QueryTimeout 30;
 
    #Integrity checks
    $errList = @();
    try
    {
        $server.Databases[$newDBName].CheckTables("None");
    }
    catch
    {
        [System.Exception]
        $err = $_.Exception
        $errList += $err;
        while ( $err.InnerException )
        {
            $err = $err.InnerException
            $errList += $err;
            write-output $err.Message
        };
    }
 
    #Log integrity checks - end
    $checkDbEndUtc = Get-Date;
    [string]$checkDbEnd = $restoreEndUtc.ToUniversalTime();
    [string]$errMsg;
    foreach($msg in $errList)
    {
        $errMsg += $msg + "'r'n";
    }
    $sql = "UPDATE [dbo].[CheckDbResult]
                SET [endDateTime] = '$checkDbEnd' ";
    if($errMsg -ne $null)
    {
        $sql += ",[errorMessage] = '$errMsg' ";
    }
    $sql += "WHERE checkDbResultId = '$checkDbResultId';";
 
    Invoke-Sqlcmd -ServerInstance $serverName -Database $loggingDbName -Query $sql -QueryTimeout 30;
 

Example data from RestoreResult

RestoreResult的示例数据

Example data from CheckDbResult

CheckDbResult的示例数据

SQL Server报告服务 (SQL Server Reporting Services)

The data is in. It is time to take a look at it. The below query conducts a simple LEFT JOIN between our tables and polishes the data a little bit for ease of viewing. Aliases were added for clarifying things such as the fact that the dates are stored with UTC time values, all NULLs are replaced with more descriptive strings, and two status columns are added so that the viewer doesn’t have to decipher what indicates a success or failure.

数据已输入。现在该看看它了。 下面的查询在我们的表之间进行一个简单的LEFT JOIN，并稍微抛光数据以便于查看。 添加了别名以澄清诸如以下事实：日期存储为UTC时间值，所有NULL替换为更具描述性的字符串，并添加了两个状态列，因此查看者不必解读表示成功或失败的内容。失败。

 
SELECT originatingServerName as [OriginatingServerName]
	, databaseName as [DatabaseName]
	, backupFilePath as [TestFile]
	, CONVERT(VARCHAR(25), RR.startDateTime, 120) as [RestoreStartUtc]
	, COALESCE(CONVERT(VARCHAR(25), RR.endDateTime, 120),'None') as [RestoreEndUtc]
	, COALESCE(RR.errorMessage, 'None') as [RestoreErrorMsg]
	, CASE
		WHEN RR.endDateTime IS NULL THEN 'Incomplete'
		WHEN RR.endDateTime IS NOT NULL 
			AND RR.errorMessage IS NULL THEN 'Successful'
		WHEN RR.endDateTime IS NOT NULL 
			AND RR.errorMessage IS NOT NULL THEN 'Unsuccessful'
	END as [RestoreStatus]
	, COALESCE(CONVERT(VARCHAR(25), CR.startDateTime, 120),'None') as [IntegrityCheckStartUtc]
	, COALESCE(CONVERT(VARCHAR(25), CR.endDateTime, 120),'None') as [IntegrityCheckEndUtc]
	, COALESCE(CR.errorMessage, 'None') as [IntegrityCheckErrorMsg]
	, CASE
		WHEN CR.startDateTime IS NULL THEN 'Not conducted'
		WHEN CR.endDateTime IS NULL THEN 'Incomplete'
		WHEN CR.endDateTime IS NOT NULL 
			AND CR.errorMessage IS NULL THEN 'Successful'
		WHEN CR.endDateTime IS NOT NULL 
			AND CR.errorMessage IS NOT NULL THEN 'Unsuccessful'
	END as [IntegrityCheckStatus]
FROM dbo.RestoreResult RR
LEFT JOIN dbo.CheckDbResult CR ON CR.restoreResultId = RR.restoreResultId
 

At this point the report mechanism becomes a matter of personal choice or organization standards. Some organizations have existing SQL Server Reporting Services (SSRS) implementations, others might be using Crystal Reports, or there could be an existing in-house support application and all this query needs is a view or stored procedure to call its home. For the purposes of demonstration, the basic steps for exposing this data via an SSRS report are shown.

此时，报告机制将成为个人选择或组织标准的问题。 一些组织具有现有SQL Server Reporting Services（SSRS）实现，其他组织可能正在使用Crystal Reports，或者可能存在现有的内部支持应用程序，而所有这些查询需求都是一个视图或存储过程来调用其主目录。 为了演示的目的，显示了通过SSRS报告公开此数据的基本步骤。

Building the report

建立报告

Prerequisite: SQL Server Data Tools – Business Intelligence for Visual Studio 2013.

先决条件 ： SQL Server数据工具–适用于Visual Studio 2013的商业智能 。

1. Open Visual Studio 2013 and navigate to the menu bar. Click File > New > Project.
   打开Visual Studio 2013，然后导航到菜单栏。 单击文件>新建>项目。

2. Select the Report Server Project under Business Intelligence and populate the project name and directory. Click OK.

   在“商业智能”下选择“报表服务器项目”，然后填充项目名称和目录。 单击确定。

   

3. In the Solution Explorer, right-click on the Shared Data Sources folder and select Add New Data Source.

   在解决方案资源管理器中，右键单击“ 共享数据源”文件夹，然后选择“ 添加新数据源” 。

   

4. Select Microsoft SQL Server for Type and then configure your connection string. Click OK.

   选择“ Microsoft SQL Server”作为“类型”，然后配置您的连接字符串。 单击确定。

   

5. In the Solution Explorer, once again, right-click the Reports folder and select Add New Report.

   在解决方案资源管理器中 ，再次右键单击Reports文件夹，然后选择Add New Report 。

   

6. Select the shared data source that has been created already and click Next.

   选择已经创建的共享数据源，然后单击“下一步”。

   

7. Paste in the T-SQL query and click Next.

   粘贴在T-SQL查询中，然后单击“下一步”。

   

8. Select Tabular and click Next.

   选择表格 ，然后单击下一步。

   

9. On this step Karla can pick and choose how she wants the report formatted. For the demonstration OriginatingServerName and DatabaseName were selected for the Group section and all other fields as Details.

   在此步骤中，Karla可以选择要如何格式化报告的方式。 在演示中，为Group部分和所有其他字段选择了OriginatingServerName和DatabaseName作为Details 。

   

10. Select the layout type and make sure to Enable drilldown. The drilldown will be important for when the same database is tested regularly over a period of time.

    选择布局类型，并确保启用钻取 。 当一段时间内定期测试同一数据库时，向下钻取非常重要。

    

11. Now select the color scheme.

    现在选择配色方案。

    

12. Pick a report name and, finally, click Finish.

    选择一个报告名称，最后单击“完成”。

    

Report Preview

报告预览

After all of that setup, the report will look like this.

完成所有设置后，报告将如下所示。

结语 (Wrap-up)

In this part of the series reporting on the results of restore tests and integrity checks was covered. Two methods of querying result data were explored and the necessary logging mechanism was scripted into the Restore-Database function originally created in part 1. Finally, an SSRS report was created to handle viewing by unprivileged users.

在本系列的这一部分中，将介绍有关还原测试和完整性检查的结果。 探索了两种查询结果数据的方法，并将必要的日志记录机制编写为第1部分中最初创建的Restore-Database函数。 最后，创建了SSRS报告以处理非特权用户的查看。

With this process running regularly restore testing can be pushed to the back of Karla’s mind with intervention only required if a test flags a particular backup file as a failure.

通过定期运行此过程，只有在测试将特定备份文件标记为失败的情况下，才可以通过干预将恢复测试推迟到Karla的脑海。

Previous article in this series:

本系列的上一篇文章：

• Backup testing with PowerShell – Part 1: The test使用PowerShell进行备份测试–第1部分：测试

翻译自: https://www.sqlshack.com/backup-testing-powershell-part-2-reporting-results/