使用PowerShell进行备份测试–第1部分：测试

最新推荐文章于 2024-05-16 05:46:54 发布

culuo4781

最新推荐文章于 2024-05-16 05:46:54 发布

阅读量756

点赞数

文章标签：数据库 python java 大数据 linux

原文链接：https://www.sqlshack.com/backup-testing-powershell-part-1-test/

版权

Karla is a production database administrator and she has a lot in common with you. Being responsible for database backups and recovery, she has implemented a well-structured automated backup system. Maybe she’s using Ola Hallengren’s Maintenance Solution, custom T-SQL stored procedures, or a set of PowerShell scripts. She has heard the saying, “a DBA only needs one thing, either a backup or a resume, but never both,” and is confident that she won’t be dusting off the resume any time soon.

Karla是生产数据库管理员，她与您有很多共同点。她负责数据库备份和恢复，已实施了结构良好的自动备份系统。也许她正在使用Ola Hallengren的维护解决方案，自定义T-SQL存储过程或一组PowerShell脚本。她听说过这样的话：“ DBA只需要一件事，无论是备份还是履历，但都不需要。”而且，她坚信她不会在不久的将来弄脏履历。

Karla is employed by a medium size company which is now growing concerned with overseers and regulation compliance, such as with the Sarbanes-Oxley Act of 2002. As the company prepares to prove their compliance with the regulations which now apply to it, management establishes procedures for performing internal practice audits.

卡拉（Karla）受雇于一家中等规模的公司，该公司现在越来越关注监督者和法规遵从性，例如2002年的《萨班斯-奥克斯利法案》。当公司准备证明自己遵守现在适用的法规时，管理层就建立了执行内部实践审核的程序。

Suddenly, Karla is less certain of her backups when presented with this inquiry, “how can you prove that your backups are recoverable?” Karla begins to wonder if her backups were enough and realizes that the best way to prove that the backups are not corrupted and can be restored successfully, is to restore them and run integrity checks.

突然，Karla在收到此询问时不确定她的备份，“您如何证明您的备份是可恢复的？” Karla开始怀疑自己的备份是否足够，并意识到，证明备份没有损坏并且可以成功还原的最佳方法是还原备份并运行完整性检查。

计划 (The plan)

The ideal solution to any problem that a DBA faces is one which requires minimal interaction by the DBA. So, Karla built a list of goals.

DBA面临的任何问题的理想解决方案都是需要DBA进行最少交互的解决方案。因此，卡拉制定了目标清单。

Backup tests and integrity checks are 100% automatic.
备份测试和完整性检查是100％自动的。
Every production user database must have at least one full backup tested each quarter.
每个生产用户数据库每个季度必须至少测试一个完整备份。
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运行。

Given the above goals Karla has recorded her technical requirements and choices.

鉴于上述目标，Karla记录了她的技术要求和选择。

SQL Server Central Management Server. SQL Server Central Management Server上的注册服务器进行维护和组织。
PowerShell 3.0 so that its powerful asynchronous abilities can be leveraged. PowerShell 3.0编写，以便可以利用其强大的异步功能。
- Windows Management Framework 3.0 must be installed for operating systems older than Windows 8 or Windows Server 2012.Windows Management Framework 3.0 。
SQL Server Management Objects (SMO) and PowerShell module SQL Server管理对象（SMO）和PowerShell模块SQLPS (available in SQLPS （在SQL Server 2014 Feature Pack) will be used to interact with SQL Server.SQL Server 2014 Feature Pack中提供）将用于与SQL Server进行交互。
Queries to produce evidence of tests and results will be written in T-SQL.
产生测试和结果证据的查询将用T-SQL编写。
The process will be initiated by a SQL Server Agent Job and repeat on a schedule to maximize usage of the test server’s resources and to maximize the number of backups being tested in a given quarter.

该过程将由SQL Server代理作业启动，并按计划重复执行，以最大程度地利用测试服务器的资源并最大化给定季度中要测试的备份数量。

tl; dr –完整的PowerShell脚本 (tl;dr – Complete PowerShell script)

 
<# .synopsis
   the test-backups script will reach out to a sql server central management server, derive a server list and database backup list. then asynchronously restore them to a test server followed by integrity checks.
   .example
   .\test-backups.ps1 -cmsname "localhost" -servergroup "Production" -testservername "localhost" -randommultiplier 0.1 -loggingdbname "BackupTest"
   .\test-backups.ps1 -cmsname "localhost" -servergroup "Production" -testservername "localhost" -randommultiplier 0.5 -loggingdbname "BackupTest" -recurse
   .inputs
   [string]$cmsname - the central management server to connect to.
   [string]$servergroup - the root server group to parse.
   [string]$testservername - the test server to restore to.
   [string]$loggingdbname - name of the database on the test server to log results to.
   [decimal]$randommultiplier - decimal multiplier for the number of servers and databases to test at a time. e.g. 0.1=10%, 1=100%.
   [switch]$recurse - switch to determine whether the server group should be recursively searched.
   .outputs
   none.
   #>
[CmdletBinding()]
param
(
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$cmsName,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$serverGroup,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$testServerName,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$loggingDbName,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [decimal]$randomMultiplier,
    [parameter(Mandatory=$false)]
    [switch]$recurse
) 
Import-Module SQLPS -DisableNameChecking 
$ErrorActionPreference = "Continue";
Trap {
    $err = $_.Exception
    while ( $err.InnerException )
    {
    $err = $err.InnerException
    write-output $err.Message
    throw $_.Exception;
    };
    continue
    } 
Function Parse-ServerGroup($serverGroup)
{
    $results = $serverGroup.RegisteredServers;
    foreach($group in $serverGroup.ServerGroups)
    {
        $results += Parse-ServerGroup -serverGroup $group;
    }
    return $results;
}
Function Get-ServerList ([string]$cmsName, [string]$serverGroup, [switch]$recurse)
{
    $connectionString = "data source=$cmsName;initial catalog=master;integrated security=sspi;"
    $sqlConnection = New-Object ("System.Data.SqlClient.SqlConnection") $connectionstring
    $conn = New-Object ("Microsoft.SQLServer.Management.common.serverconnection") $sqlconnection
    $cmsStore = New-Object ("Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore") $conn 
    $cmsRootGroup = $cmsStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$serverGroup]
    
    if($recurse)
    {
        return Parse-ServerGroup -serverGroup $cmsRootGroup | select ServerName
    }
    else
    {
        return $cmsRootGroup.RegisteredServers | select ServerName
    }
}
[scriptblock]$restoreDatabaseFunction = 
{
    Function Restore-Database
    {
    <# .synopsis
       restores a full database backup to target server. it will move the database files to the default data and log directories on the target server.
       .example
       restore-database -servername "localhost" -newdbname "testdb" -backupfilepath "D:\Backup\testdb.bak"
       restore-database -servername "localhost" -newdbname "testdb" -backupfilepath "\\KINGFERGUS\Shared\Backup\testdb.bak" -dropdbbeforerestore -conductintegritychecks
       .inputs
       [string]$servername - the server to restore to.
       [string]$newdbname - the database name that you'd like to use for the restore.
       [string]$backupfilepath - local or unc path for the *.bak file (.bak extension is required).
       [string]$origservername - name of the server where the backup originated. used for logging purposes.
       [string]$loggingdbname - name of the logging database.
       [switch]$dropdbbeforerestore - set if you would like the database matching $newdbname to be dropped before restored.
       the intent of this would be to ensure exclusive access to the database can be had during restore.
       [switch]$conductintegritychecks - set if you would like dbcc checktables to be run on the entire database after restore.
       .outputs
       none.
       #>
        [CmdletBinding()]
        param
        (
            [Parameter(Mandatory=$true)]
            [ValidateNotNullorEmpty()]
            [string]$serverName,
            [Parameter(Mandatory=$true)]
            [ValidateNotNullorEmpty()]
            [string]$newDBName,
            [parameter(Mandatory=$true)]
            [ValidateNotNullorEmpty()]
            [string]$backupFilePath,
            [parameter(Mandatory=$true)]
            [ValidateNotNullorEmpty()]
            [string]$origServerName,
            [parameter(Mandatory=$true)]
            [ValidateNotNullorEmpty()]
            [string]$loggingDbName,
            [parameter(Mandatory=$false)]
            [switch]$dropDbBeforeRestore,
            [parameter(Mandatory=$false)]
            [switch]$conductIntegrityChecks
        ) 
        Import-Module SQLPS -DisableNameChecking 
    
        ## BEGIN input validation ## 
        $ErrorActionPreference = "Stop";
        Trap {
          $err = $_.Exception
          while ( $err.InnerException )
            {
            $err = $err.InnerException
            write-output $err.Message
            throw $_.Exception;
            };
            continue
          } 
        if($backupFilePath -notlike "*.bak")
        {
            throw "the file extension should be .bak."
        }
 
        if(!(test-path -Path $backupFilePath))
        {
            throw "Could not find the backup file."
        }
        # Test connection
        $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
        if($server.Version.Major -eq $null)
        {
            throw "Could not establish connection to $serverName."
        }
        ## END input validation ##
  
        # Create restore object and specify its settings
        $smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
        $smoRestore.Database = $newDBName
        $smoRestore.NoRecovery = $false;
        $smoRestore.ReplaceDatabase = $true;
        $smoRestore.Action = "Database"
 
        # Create location to restore from
        $backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFilePath, "File")
        $smoRestore.Devices.Add($backupDevice)
 
        # Get the file list from backup file
        $dbFileList = $smoRestore.ReadFileList($server)
        # Specify new data file (mdf)
        $smoRestoreDataFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
        $defaultData = $server.DefaultFile
        if (($defaultData -eq $null) -or ($defaultData -eq ""))
        {
            $defaultData = $server.MasterDBPath
        }
        $smoRestoreDataFile.PhysicalFileName = "$defaultData$newDBName" + ".mdf";
        $smoRestoreDataFile.LogicalFileName = $dbFileList.Select("FileId = 1").LogicalName
        $smoRestore.RelocateFiles.Add($smoRestoreDataFile) | Out-Null
 
        # Specify new log file (ldf)
        $smoRestoreLogFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
        $defaultLog = $server.DefaultLog
        if (($defaultLog -eq $null) -or ($defaultLog -eq ""))
        {
            $defaultLog = $server.MasterDBLogPath
        }
        $smoRestoreLogFile.PhysicalFileName = "$defaultData$newDBName" + "_Log.ldf";
        $smoRestoreLogFile.LogicalFileName = $dbFileList.Select("FileId = 2").LogicalName
        $smoRestore.RelocateFiles.Add($smoRestoreLogFile) | Out-Null
        # Loop through remaining files to generate relocation file paths.
        $smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
        foreach($file in $dbFileList.Select("FileId > 2"))
        {
            $smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
            $smoLogicalName = $file.LogicalName;
            $smoRestoreFile.LogicalFileName = $smoLogicalName
            $smoRestoreFile.PhysicalFileName = "$defaultData$smoLogicalName" + ".ndf";
            $smoRestore.RelocateFiles.Add($smoRestoreFile) | Out-Null
        }
             
        # Ensure exclusive access
        if($dropDbBeforeRestore -and $server.Databases[$newDBName] -ne $null)
        {
            $server.KillAllProcesses($newDBName);
            $server.KillDatabase($newDBName);
        }
        #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;
        # 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
            };
        }
        #Log restore process - end
        $restoreEndUtc = Get-Date;
        [string]$restoreEnd = $restoreEndUtc.ToUniversalTime();
        [string]$errMsg;
        foreach($msg in $errList)
        {
            $errMsg += $msg + "'r'n";
        }
        $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;
        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;
        } 
        # clean up databases
        $server.KillAllProcesses($newDBName);
        $server.KillDatabase($newDBName);
 
        Write-Host -Object "Restore-Database has completed processing."
    } 
}
if($recurse)
{
    $serverList = Get-ServerList -cmsName $cmsName -serverGroup $serverGroup -recurse
}
else
{
    $serverList = Get-ServerList -cmsName $cmsName -serverGroup $serverGroup
}
$servers = $serverList | Get-Random -Count ([Math]::Ceiling([decimal]$serverList.Count * $randomMultiplier)) 
$jobs = @() 
foreach($svr in $servers)
{
    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $svr.ServerName;
    $databaseList = $server.Databases | Where-Object { $_.IsSystemObject -eq $false };
    $databaseList = $databaseList | Get-Random -Count ([Math]::Ceiling([decimal]$databaseList.Count * $randomMultiplier)) | select Name;
    $backupSetQuery = "SELECT TOP 1 BMF.physical_device_name
                        FROM msdb.dbo.backupmediafamily BMF
                        INNER JOIN msdb.dbo.backupset BS ON BS.media_set_id = BMF.media_set_id
                        WHERE BS.database_name = '`$(databaseName)'
	                        AND BS.type = 'D' 
	                        AND BS.is_copy_only = 0
	                        AND BMF.physical_device_name NOT LIKE '{%'
                        ORDER BY BS.backup_finish_date DESC";
    foreach($database in $databaseList)
    {
        $params = "databaseName = " + $database.Name; 
        $results = @();
        $results += Invoke-Sqlcmd -ServerInstance $server.Name -Query $backupSetQuery -Variable $params -QueryTimeout 30; 
        if($results.Count -eq 0 -or $results -eq $null)
        {
            continue;
        } 
        [string]$backupPath = $results[0].physical_device_name;
        
        # set arguments
        $arguments = @()
        $arguments += $testServerName;
        $arguments += $database.Name;
        $arguments += $backupPath;
        $arguments += $loggingDbName;
        $arguments += $svr.ServerName;
        # start job
        $jobs += Start-job -ScriptBlock {Restore-Database -serverName $args[0] -newDBName $args[1] -backupFilePath $args[2] -loggingDbName $args[3] -origServerName $args[4] –dropDbBeforeRestore -conductIntegrityChecks} `
            -InitializationScript $restoreDatabaseFunction -ArgumentList($arguments) -Name $database.Name;  
 
    } 
} 
$jobs | Wait-Job | Receive-Job
$jobs | Remove-Job

中央管理服务器 (Central Management Server)

Setup

建立

As previously mentioned, Karla is using SQL Server’s Central Management Server (CMS) to organize her database server list and this will be the source of truth for her backup testing process. She has her servers registered to server groups, one group per environment, by following MSDN’s instructions when using SSMS.

如前所述，Karla正在使用SQL Server的中央管理服务器（CMS）来组织她的数据库服务器列表，这将成为她的备份测试过程的真相。在使用SSMS时，按照MSDN的说明，她已将服务器注册到服务器组（每个环境一个组）。

Registered Servers - The Production folder

The Production folder, seen above, will be the list of servers to be selected from for testing. First she will need to import the SQLPS module. This will load all of the assemblies necessary for using the SQL Server Management Objects and a number of useful Cmdlets, some of which she will be using later.

上面显示的Production文件夹将是要进行测试的服务器列表。首先，她将需要导入SQLPS模块。这将加载使用SQL Server管理对象所需的所有程序集和许多有用的Cmdlet，她稍后将使用其中的一些。

 
Import-Module SQLPS -DisableNameChecking

NOTE: The DisableNameChecking switch is used because some of the commands in the SQLPS module do not comply with PowerShell’s list of approved verbs, such as Backup and Restore.

注意：之所以使用DisableNameChecking开关，是因为SQLPS模块中的某些命令不符合PowerShell的已批准动词列表，例如“备份和还原”。

Data retrieval

资料检索

Next we’ll build the functions which will derive a server list from the CMS server group.

接下来，我们将构建从CMS服务器组派生服务器列表的功能。

 
Function Parse-ServerGroup($serverGroup)
{
    $results = $serverGroup.RegisteredServers;
    foreach($group in $serverGroup.ServerGroups)
    {
        $results += Parse-ServerGroup -serverGroup $group;
    }
    return $results;
}
Function Get-ServerList ([string]$cmsName, [string]$serverGroup, [switch]$recurse)
{
    $connectionString = "data source=$cmsName;initial catalog=master;integrated security=sspi;"
    $sqlConnection = New-Object ("System.Data.SqlClient.SqlConnection") $connectionstring
    $conn = New-Object ("Microsoft.SQLServer.Management.common.serverconnection") $sqlconnection
    $cmsStore = New-Object ("Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore") $conn 
    $cmsRootGroup = $cmsStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$serverGroup]
    
    if($recurse)
    {
        return Parse-ServerGroup -serverGroup $cmsRootGroup | select ServerName
    }
    else
    {
        return $cmsRootGroup.RegisteredServers | select ServerName
    }
}

The Get-ServerList function will accept the CMS server name, the root server group name (Production in our case), and an optional switch for making the function recursive. The goal with this function is to retrieve the Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore object and then return a list of RegisteredServers.

Get-ServerList函数将接受CMS服务器名称，根服务器组名称（在本例中为Product）以及使该函数递归的可选开关。此功能的目标是检索Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore对象，然后返回RegisteredServers列表。

选择要测试的备份文件 (Selecting backup files to test)

Once the server list is retrieved, a randomly selected sub-set of the servers is needed, followed by a randomly selected sub-set of the databases from those servers.

一旦检索到服务器列表，就需要服务器的随机选择子集，然后是来自那些服务器的数据库的随机选择子集。

Get-Random

获取随机

For this Karla will pipe the server list into the Get-Random PowerShell Cmdlet using the –Count option to designate the number of objects to select.

为此，Karla将使用–Count选项将服务器列表通过管道传递到Get-Random PowerShell Cmdlet中，以指定要选择的对象数。

$servers = Get-ServerList -cmsName “localhost\SQL2012” -serverGroup “Production” -recurse | Get-Random -Count 1

For this example, randomly selecting one server from the list would be ok, since there are only two servers registered. This, however, wouldn’t scale so Karla is going to derive the count based on a percentage of the servers. Here, the Math.Ceiling static method is being used to guarantee that zero is not selected.

对于此示例，可以从列表中随机选择一台服务器，因为只有两台服务器注册。但是，这不会扩展，因此Karla将根据服务器的百分比得出计数。在此，使用Math.Ceiling静态方法来确保未选择零。

 
[decimal]$randomMultiplier = 0.1;
$serverList = Get-ServerList -cmsName $cmsName -serverGroup $serverGroup -recurse
$servers = $serverList | Get-Random -Count ([Math]::Ceiling([decimal]$serverList.Count * $randomMultiplier))

The same concept will apply to retrieving the list of databases. The SMO.Server and Database objects will be used to retrieve a list of databases for each server. This loop will also be where the restores and integrity checks are initiated, discussed further in the next few sections.

相同的概念将适用于检索数据库列表。 SMO.Server和Database对象将用于检索每个服务器的数据库列表。此循环还将是启动还原和完整性检查的地方，在接下来的几节中将进一步讨论。

 
foreach($svr in $servers)
{
    $server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $svr.ServerName;
    $databaseList = $server.Databases | Where-Object { $_.IsSystemObject -eq $false };
    $databaseList = $databaseList | Get-Random -Count ([Math]::Ceiling([decimal]$databaseList.Count * $randomMultiplier)) | select Name;
    ...
}

Get backup paths

获取备份路径

Next Karla will need to find the most recent full backup that was taken for the list of databases chosen above. For this she will need to use T-SQL to query msdb.dbo.backupset and msdb.dbo.backupmediafamily to extract the physical file paths.

接下来，Karla将需要找到用于上面选择的数据库列表的最新完整备份。为此，她将需要使用T-SQL查询msdb.dbo.backupset和msdb.dbo.backupmediafamily以提取物理文件路径。

NOTE: Local or UNC paths are acceptable for this process. Understand that restore commands are sent to the SQL Server and then the restore itself is run on the SQL Server. This means that local paths must be local to the SQL Server not to the PowerShell script.

注意：此过程可接受本地或UNC路径。 了解还原命令已发送到SQL Server，然后还原本身在SQL Server上运行。 这意味着本地路径必须是SQL Server本地的，而不是PowerShell脚本的本地。

 
SELECT TOP 1 BMF.physical_device_name
FROM msdb.dbo.backupmediafamily BMF
INNER JOIN msdb.dbo.backupset BS ON BS.media_set_id = BMF.media_set_id
WHERE BS.database_name = 'myDbName' --Database name to look-up
	AND BS.type = 'D' --D = Database
	AND BS.is_copy_only = 0 --0 = false
	AND BMF.physical_device_name NOT LIKE '{%' --filter out SQL Server VSS writer service backups
ORDER BY BS.backup_finish_date DESC

To execute this script Karla will use the SQLPS module, imported earlier on. The Invoke-SqlCmd cmdlet will be used to execute the T-SQL. The cmdlet accepts a query variable and a set of parameters, in this case, the database name.

为了执行此脚本，Karla将使用之前导入SQLPS模块。 Invoke-SqlCmd cmdlet将用于执行T-SQL。该cmdlet接受查询变量和一组参数，在这种情况下为数据库名称。

 
$backupSetQuery = "SELECT TOP 1 BMF.physical_device_name
                    FROM msdb.dbo.backupmediafamily BMF
                    INNER JOIN msdb.dbo.backupset BS ON BS.media_set_id = BMF.media_set_id
                    WHERE BS.database_name = '`$(databaseName)'
	                    AND BS.type = 'D' 
	                    AND BS.is_copy_only = 0
	                    AND BMF.physical_device_name NOT LIKE '{%'
                    ORDER BY BS.backup_finish_date DESC";
foreach($database in $databaseList)
{
    $params = "databaseName = " + $database.Name; 
    $results = @();
    $results += Invoke-Sqlcmd -ServerInstance $server.Name -Query $backupSetQuery -Variable $params -QueryTimeout 30; 
    if($results.Count -eq 0 -or $results -eq $null)
    {
        continue;
    } 
    [string]$backupPath = $results[0].physical_device_name;
    ...
}

异步作业 (Asynchronous jobs)

Up to this point everything has been about collecting the data required to issue a restore command. The Restore-Database function will be called asynchronously for each of our backup files that we wish to restore. This is a custom function which will be covered in depth in the following section.

到目前为止，一切都与收集发出还原命令所需的数据有关。对于我们希望还原的每个备份文件，将异步调用Restore-Database函数。这是一个自定义功能，将在下一节中深入介绍。

In PowerShell 3.0 the Start-Job cmdlet was introduced. This cmdlet creates and starts a job which executes on an asynchronous thread. It returns an object representing the job which is important for keeping track of its progress and retrieving output from the thread.

在PowerShell 3.0中，引入了Start-Job cmdlet。此cmdlet创建并启动在异步线程上执行的作业。它返回一个表示作业的对象，该对象对于跟踪作业的进度以及从线程中检索输出很重要。

 
$jobs = @()
...
# set arguments
$arguments = @()
$arguments += $testServerName;
$arguments += $database.Name;
$arguments += $backupPath;
$arguments += $loggingDbName;
$arguments += $svr.ServerName;
 
# start job
$jobs += Start-job -ScriptBlock {Restore-Database -serverName $args[0] -newDBName $args[1] -backupFilePath $args[2] -loggingDbName $args[3] -origServerName $args[4] –dropDbBeforeRestore -conductIntegrityChecks} `
    -InitializationScript $restoreDatabaseFunction -ArgumentList($arguments) -Name $database.Name;

As can be seen, the Restore-Database function will accept the server name, the name for the database to be restored, the backup file path, the name of a database to log test results in, the originating server name, an optional switch to drop the existing database before restoring, and finally our switch to conduct integrity checks (CheckTables) after the restore.

可以看出， Restore-Database函数将接受服务器名称，要还原的数据库的名称，备份文件路径，要记录测试结果的数据库的名称，原始服务器名称，一个可选的开关。在还原之前先删除现有数据库，最后在还原后切换到完整性检查（ CheckTables ）。

In order to execute our thread we pass in a script block object to the Start-Job cmdlet calling our function. Next we pass in another script block object to initialize the session. In this case, the Restore-Database function definition is supplied in a scriptblock variable format. Finally, the argument list parameter accepts an array of objects to be used as variables in the original script block.

为了执行线程，我们将脚本块对象传递给调用我们的函数的Start-Job cmdlet。接下来，我们传入另一个脚本块对象以初始化会话。在这种情况下，Restore-Database函数定义以脚本块变量格式提供。最后，参数列表参数接受对象数组，以用作原始脚本块中的变量。

Later in the script the array of $jobs will be used to wait on the threads to complete and return their output like this.

在脚本的后面，将使用$ jobs数组等待线程完成并像这样返回它们的输出。

 
$jobs | Wait-Job | Receive-Job
$jobs | Remove-Job

The Wait-Job cmdlet pauses the current session and waits for all of the threads in the $jobs array to complete before moving on. Then the jobs are looped through and Receive-Job is called for each of them to retrieve the output, whether of the type information, warning, or error. Finally, Remove-Job to kill the background job.

Wait-Job cmdlet暂停当前会话，并等待$ jobs数组中的所有线程完成后再继续。然后，这些作业将循环通过，并为每个作业调用Receive-Job来检索输出，无论输出是类型信息，警告还是错误。最后，使用“ 删除作业”杀死后台作业。

使用SMO还原 (Restore with SMO)

At the heart of the Restore-Database function are the SQL Server Management Objects. The Server object and the Database objects have already been touched on when the backup files were selected for restore. In the Restore-Database function, SMO is taken a bit further.

恢复数据库功能的核心是SQL Server管理对象。选择备份文件进行还原时，已经触摸了Server对象和Database对象。在“ 还原数据库”功能中，将SMO再进一步一点。

还原数据库功能细分 (Restore-Database function break-down)

Parameter definitions

参数定义

 
CmdletBinding()]
param
(
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$cmsName,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$serverGroup,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$testServerName,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [string]$loggingDbName,
    [Parameter(Mandatory=$true)]
    [ValidateNotNullorEmpty()]
    [decimal]$randomMultiplier,
    [parameter(Mandatory=$false)]
    [switch]$recurse
)

By using the CmdletBinding attribute and defining a set of parameters Karla is able to produce a function that is invoked as if it were a built-in cmdlet. The Parameter and ValidateNotNullorEmpty attributes are great for pre-validating the inputs before the function even begins.

通过使用CmdletBinding属性并定义一组参数，Karla可以生成一个被调用的函数，就好像它是内置cmdlet一样。 Parameter和ValidateNotNullorEmpty属性非常适合在函数开始之前预先验证输入。

Custom validation

自定义验证

 
## BEGIN input validation ## 
$ErrorActionPreference = "Stop";
Trap {
    $err = $_.Exception
    while ( $err.InnerException )
    {
    $err = $err.InnerException
    write-output $err.Message
    throw $_.Exception;
    };
    continue
    } 
 
if ($backupFilePath -notlike "*.bak")
{
    throw "the file extension should be .bak."
}
 
if (!(test-path -Path $backupFilePath))
{
    throw "Could not find the backup file."
}
# Test connection
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $serverName
if($server.Version.Major -eq $null)
{
    throw "Could not establish connection to $serverName."
}
        
## END input validation ##

To move inside the function, Karla has coded some additional parameter validation. First, the file path extension is verified. Next, the Test-Path cmdlet is used to verify that the path is syntactically correct.

为了在函数内部移动，Karla对其他一些参数验证进行了编码。首先，验证文件路径扩展名。接下来，使用Test-Path cmdlet验证路径在语法上是否正确。

The Server class is instantiated partially for its use later in the function and partially as a means of validating the inputted server name. When the object is instantiated a connection is not attempted. So, she checks the major version of the server. This will establish a connection and retrieve the version number. If the return object is $null then the connection could not be established and an exception is thrown.

Server类被部分实例化，以供稍后在函数中使用，并部分地用作验证输入的服务器名称的手段。实例化对象时，不尝试连接。因此，她检查了服务器的主版本。这将建立连接并获取版本号。如果返回对象为$ null，则无法建立连接，并引发异常。

Instantiate the Restore object

实例化还原对象

 
# Create restore object and specify its settings
$smoRestore = new-object("Microsoft.SqlServer.Management.Smo.Restore")
$smoRestore.Database = $newDBName
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
 
# Create location to restore from
$backupDevice = New-Object("Microsoft.SqlServer.Management.Smo.BackupDeviceItem") ($backupFilePath, "File")
$smoRestore.Devices.Add($backupDevice)
 
# Get the file list from backup file
$dbFileList = $smoRestore.ReadFileList($server)

The Restore object represents the settings which would normally be scripted in the T-SQL RESTORE DATABASE command. The basic settings are applied directly to the object, as seen above, immediately after the New-Object cmdlet.

Restore对象代表通常在T-SQL RESTORE DATABASE命令中编写脚本的设置。如上所示，基本设置直接在New-Object cmdlet之后直接应用于对象。

Where things become a bit more complicated is when files need to be moved from the location stored in the backup file’s file list. This will almost always be the case for Karla because she is restoring production backups to a test server. It is not likely that every database server in the enterprise will have identical file paths for all databases which means that the restore command has to be told where to restore these files. Traditionally, the T-SQL MOVE option in the RESTORE DATABASE statement would be used but a list of all of the files is required to produce the MOVE paths.

当需要从备份文件的文件列表中存储的位置移动文件时，事情变得更加复杂。 Karla几乎总是这样，因为她正在将生产备份还原到测试服务器。企业中的每个数据库服务器都不可能为所有数据库都具有相同的文件路径，这意味着必须告知restore命令这些文件的还原位置。传统上，将使用RESTORE DATABASE语句中的T-SQL MOVE选项，但是要生成MOVE路径，需要所有文件的列表。

In order to retrieve a list of the files dynamically she will execute her first restore operation, a restore of the backup’s file list. The backup file path is used to instantiate a BackupDeviceItem and is added to the restore object. Then a variable will be populated with the ReadFileList method output.

为了动态检索文件列表，她将执行第一个还原操作，即备份文件列表的还原。备份文件路径用于实例化BackupDeviceItem，并将其添加到还原对象中。然后，将使用ReadFileList方法的输出填充变量。

Generate RelocateFile objects

生成RelocateFile对象

 
# Specify new data file (mdf)
$smoRestoreDataFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$defaultData = $server.DefaultFile
if (($defaultData -eq $null) -or ($defaultData -eq ""))
{
    $defaultData = $server.MasterDBPath
}
$smoRestoreDataFile.PhysicalFileName = "$defaultData$newDBName" + ".mdf";
$smoRestoreDataFile.LogicalFileName = $dbFileList.Select("FileId = 1").LogicalName
$smoRestore.RelocateFiles.Add($smoRestoreDataFile) | Out-Null
 
# Specify new log file (ldf)
$smoRestoreLogFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$defaultLog = $server.DefaultLog
if (($defaultLog -eq $null) -or ($defaultLog -eq ""))
{
    $defaultLog = $server.MasterDBLogPath
}
$smoRestoreLogFile.PhysicalFileName = "$defaultData$newDBName" + "_Log.ldf";
$smoRestoreLogFile.LogicalFileName = $dbFileList.Select("FileId = 2").LogicalName
$smoRestore.RelocateFiles.Add($smoRestoreLogFile) | Out-Null
# Loop through remaining files to generate relocation file paths.
$smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
foreach($file in $dbFileList.Select("FileId > 2"))
{
    $smoRestoreFile = New-Object("Microsoft.SqlServer.Management.Smo.RelocateFile")
    $smoLogicalName = $file.LogicalName;
    $smoRestoreFile.LogicalFileName = $smoLogicalName
    $smoRestoreFile.PhysicalFileName = "$defaultData$smoLogicalName" + ".ndf";
    $smoRestore.RelocateFiles.Add($smoRestoreFile) | Out-Null
}

There are three different file types that are targeted for relocation. Each of the files that are worked with will be identified by selecting the FileId attribute of the $dbFileList object. For the primary file it will be FileId 1, for the log file it will be FileId 2, and all other data files will have FileIds greater than 2.

有三种用于重定位的文件类型。通过选择$ dbFileList对象的FileId属性，可以识别每个正在使用的文件。对于主文件，它将是FileId 1，对于日志文件，它将是FileId 2，而所有其他数据文件的FileId将大于2。

For each file, a number of operations will take place. First, a RelocateFile object is instantiated and then the logical and physical file names are set. For the primary and log file, Karla will populate string variables derived from either the $server.DefaultFile and $server.DefaultLog paths, or the $server.MasterDBPath and $server.MasterDBLogPath. Finally, the RelocateFile object is added to the Restore object that has been maintained thus far.

对于每个文件，将执行许多操作。首先，实例化RelocateFile对象，然后设置逻辑和物理文件名。对于主文件和日志文件，Karla将填充从$ server .DefaultFile和$ server .DefaultLog路径或$ server派生的字符串变量。 MasterDBPath和$ server 。 MasterDBLogPath 。最后，将RelocateFile对象添加到到目前为止已维护的Restore对象中。

Prepare server for restore

准备要还原的服务器

 
# Ensure exclusive access
if($dropDbBeforeRestore -and $server.Databases[$newDBName] -ne $null)
{
    $server.KillAllProcesses($newDBName);
    $server.KillDatabase($newDBName);
}

Given the defined process flow, each time this script executes there should only be system databases on the server. In case of a problem, though, the script will be self-healing. The dropDbDBeforeRestore switch is used to check for the existence of the database and, if exists, kill all connections to it and drop the database. With a T-SQL restore, this is normally accomplished by setting the SINGLE_USER mode but the SMO objects do not support this. SINGLE_USER mode could be set with T-SQL by using the Invoke-SqlCmd cmdlet, used early in the script, but the connection context would have to be maintained between it and the restore operation. The method seen above utilizes SMO and guarantees that the server is prepared for restore operations.

给定已定义的流程，每次执行此脚本时，服务器上应仅存在系统数据库。但是，如果出现问题，脚本将自动修复。 dropDbDBeforeRestore开关用于检查数据库是否存在，如果存在，则终止与其的所有连接并删除数据库。对于T-SQL还原，通常可以通过设置SINGLE_USER模式来实现，但是SMO对象不支持此模式。可以使用脚本早期使用的Invoke-SqlCmd cmdlet通过T-SQL设置SINGLE_USER模式，但是必须在连接上下文和还原操作之间维护连接上下文。上面看到的方法利用SMO并保证服务器已准备好进行还原操作。

Restore and check integrity

恢复并检查完整性

 
# Restore the database
$smoRestore.SqlRestore($server)
try
{
    #Integrity checks
    if($conductIntegrityChecks)
    {
        $server.Databases[$newDBName].CheckTables("None");
    }
}
catch
{
    [System.Exception]
    $err = $_.Exception
    while ( $err.InnerException )
    {
        $err = $err.InnerException
        write-output $err.Message
    };
} 
 
...
}

NOTE: This is a simplified version of the restore command and integrity checks. In the second part of this series, there will be modifications to this section to support logging the results. For a preview of the differences refer to the complete script at the beginning of the article.

注意：这是还原命令和完整性检查的简化版本。 在本系列的第二部分中，将对该部分进行修改以支持记录结果。 有关差异的预览，请参阅本文开头的完整脚本。

Up to this point everything that was executed was in preparation for the restore operation and integrity checks. Here is where it all happens. The $smoRestore object contains all of the configurations and is ready to execute with the SqlRestore method. Normally a Try-Catch block would not be required to handle exceptions thrown from SMO. In this case, one is used because of the CheckTables method which handles the integrity checking. If there is corruption detected in the restored database, the normal error is less than useful.

到目前为止，已执行的所有操作都为恢复操作和完整性检查做准备。这就是所有发生的地方。 $ smoRestore对象包含所有配置，并准备使用SqlRestore方法执行。通常，将不需要Try-Catch块来处理从SMO引发的异常。在这种情况下，使用CheckTables方法来处理完整性检查，是一种方法。如果在还原的数据库中检测到损坏，则正常错误的用处不大。

 
Exception calling "CheckTables" with "1" argument(s): "Check tables failed for Database 'SQLHammerRocks'. "
At line:5 char:1
+ $server.Databases[$newDBName].CheckTables("None");
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : Failed

By using the Try-Catch and looping through the inner exceptions the corruption details that are desired can now be seen, such as the specific object_ids, object names, and column_ids.

通过使用Try-Catch并遍历内部异常，现在可以看到所需的损坏详细信息，例如特定的object_id，对象名称和column_id。

 
Check tables failed for Database 'SQLHammerRocks'.
An exception occurred while executing a Transact-SQL statement or batch.
Check Catalog Msg 3853, State 1: Attribute (object_id=1977021079) of row (object_id=1977021079,column_id=1) in
 sys.columns does not have a matching row (object_id=1977021079) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=1977021079) of row (object_id=1977021079,column_id=2) in
 sys.columns does not have a matching row (object_id=1977021079) in sys.objects.
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'SQLHammerRocks'.

Clean up

清理

The final steps in the script will be to drop the database that was just restored so that disk space is available for further executions.

脚本中的最后一步将是删除刚刚还原的数据库，以便磁盘空间可用于进一步执行。

 
# clean up databases
$server.KillAllProcesses($newDBName);
$server.KillDatabase($newDBName); 
 
Write-Host -Object "Restore-Database has completed processing." 
}
+

安排脚本执行 (Scheduling script execution)

With the Central Management Server setup, servers registered, and script complete, all that must be done is schedule its execution. In Karla’s technical requirements, recorded at the beginning of the article, she has decided to use the SQL Server Agent for job scheduling. She will be using the PowerShell job step type because, in SQL Server 2012 and above, it uses the natively installed PowerShell components for execution.

使用Central Management Server设置，注册服务器并完成脚本后，只需计划执行时间即可。在本文开头记录的Karla的技术要求中，她决定使用SQL Server代理进行作业调度。她将使用PowerShell作业步骤类型，因为在SQL Server 2012及更高版本中，它将使用本地安装的PowerShell组件执行。

Server setup

服务器设置

To prepare for this configuration, Karla will need to set her test server’s execution policy to RemoteSigned with the Set-ExecutionPolicy cmdlet. This only has to occur once on the test server so the manual method is demonstrated here, however, setting execution policy with group policy is recommended because it saves DBA time.

为了为此配置做准备，Karla将需要使用Set-ExecutionPolicy cmdlet将其测试服务器的执行策略设置为RemoteSigned。这仅需在测试服务器上发生一次，因此此处将说明手动方法，但是，建议使用组策略设置执行策略，因为这可以节省DBA时间。

Set-ExecutionPolicy RemoteSigned -Force

With the above script, Karla is able to execute script files. The file should be loaded on the server locally, possibly in a Scripts folder located on the operating system drive. The command must be run “As Administrator” and the –Force switch is used to bypass the confirmation question.

使用以上脚本，Karla能够执行脚本文件。该文件应本地加载到服务器上，可能在操作系统驱动器上的Scripts文件夹中。该命令必须以“以管理员身份”运行，并且-Force开关用于绕过确认问题。

Creating the job

创造工作

Creating a SQL Agent job to run a PowerShell script is extremely similar to how you would create a job to execute T-SQL or even SSIS packages. The only difference is in the options selected for the job step.

创建SQL Agent作业以运行PowerShell脚本与创建作业以执行T-SQL甚至SSIS包的方式极为相似。唯一的区别在于为作业步骤选择的选项。

Open SSMS and verify that the SQL Agent is started.
打开SSMS并验证SQL Agent是否已启动。
Drill into the SQL Server Agent in the object explorer and right-click on Jobs selecting New Job

在对象资源管理器中钻取SQL Server代理 ，然后右键单击“ 作业”，选择“ 新建作业”
Give your job a name and make sure that the Enabled checkbox is checked. It is preferable to change the category to Database Maintenance as well but this is not required.

给您的工作起一个名字，并确保选中“ 启用”复选框。最好也将类别更改为“ 数据库维护” ，但这不是必需的。
Jump down to the Schedules page, listed on the left.

跳至左侧列出的“ 计划”页面。
New…新建 ...
Name your schedule, set frequency as desired. In Karla’s case, she will set the schedule to run daily, every hour, all day, with no end. This will keep the job running nearly 24 hours a day, 7 days a week to maximize the amount of tests possible in a given quarter.

命名您的日程表，根据需要设置频率。以Karla为例，她将时间表设置为每天，每小时，整天无休止地运行。这将使该作业每周7天，一天24小时不间断运行，以最大程度地提高给定季度中的测试数量。
Click OK.

单击确定。
Steps page, listed on the left. Click 步骤”页面。点击New…新建 ...
Once in the New Job Step window, name your step and open up the Type drop-down box.

在“ 新作业步骤”窗口中，为您的步骤命名，然后打开“ 类型”下拉框。

NOTE: In versions of SQL Server equal to or greater than 2012, the PowerShell type will use the installed PowerShell executable and its execution policy, which was set in the last section. When running SQL Server 2008 or 2008 R2, the PowerShell type will run in a mini-shell called SQLPS.exe and use the execution policy RemoteSigned. The SQLPS.exe was created before the SQLPS PowerShell module was available and it was the best way of accessing the necessary assemblies. Unfortunately, it was compiled with limited cmdlets from PowerShell 2.0 and has not been updated. When using SQL Server 2008/R2 it is recommended to use the Operating Sytem (CmdExec) type instead because you can explicitly call the proper PowerShell.exe.

注意：在等于或大于2012SQL Server版本中，PowerShell类型将使用在上一节中设置的已安装PowerShell可执行文件及其执行策略。 当运行SQL Server 2008或2008 R2时，PowerShell类型将在名为SQLPS.exe的微型外壳中运行，并使用执行策略RemoteSigned。 SQLPS.exe是在SQLPS PowerShell模块可用之前创建的，它是访问必要程序集的最佳方法。 不幸的是，它是使用PowerShell 2.0中的有限cmdlet编译的，尚未更新。 使用SQL Server 2008 / R2时，建议改用Operating System（CmdExec）类型，因为您可以显式调用正确的PowerShell.exe。
PowerShell.PowerShell 。
Using the Run As drop-down, select SQL Server Agent Service Account.

使用运行方式下拉列表，选择SQL Server代理服务帐户 。

NOTE: This account is the one which will need file system access to the backup files. Karla’s SQL Server Agent account has the required read access to the backup shares. If yours doesn’t, then you will need to create a proxy which will then appear in this drop-down box.

注意：此帐户是一个需要文件系统访问备份文件的帐户。 KarlaSQL Server代理帐户具有对备份共享的必需读取权限。 如果没有，则需要创建一个代理，该代理将出现在此下拉框中。
Paste the PowerShell command for calling your script into the Command textbox.

将用于调用脚本的PowerShell命令粘贴到“ 命令”文本框中。
```
 
 Set-Location "C:\Scripts\"
.\Test-Backups.ps1 -cmsName localhost\sql2012 -serverGroup Production -testServerName localhost\sql2014_2 -loggingDbName BackupTest -randomMultiplier .1 -recurse 
 
```
REMINDER: The location passed into the Set-Location cmdlet is relative to the SQL Server. In this case, the Test-Backups.ps1 script must be located in the C:\Scripts folder and the SQL Server Agent account must have read and execute access to it.

提醒：传递到Set-Location cmdlet中的位置是相对于SQL Server的。 在这种情况下，Test-Backups.ps1脚本必须位于C：\ Scripts文件夹中，并且SQL Server代理帐户必须具有对其的读取和执行访问权限。
Advanced page and check the checkbox labeled 高级”页面，然后选中标有Include step output in history.在历史记录中包括步骤输出的复选框。
Click OK
点击确定
New Job page.新作业”页面上再次单击“确定”。

测试一下！ (Test it!)

The SQL Server Agent job is created all that is left is to start it. Right-click on the job in the object explorer and click Start Job at Step…

创建SQL Server代理作业后，剩下的就是启动它。右键单击对象资源管理器中的作业，然后单击“ 在步骤 … 开始作业” 。

结语 (Wrap up)

That was exciting! A lot has been covered.

太刺激了！已经涵盖了很多。

A system plan and process flow was established.
建立了系统计划和流程。
A registered server list was added to the Central Management Server.
已将注册的服务器列表添加到中央管理服务器。
Backup files were retrieved and restored.
备份文件已检索并还原。
Databases were checked for data integrity.
检查数据库的数据完整性。
The process was scheduled for automated execution via a SQL Server Agent job.
该过程计划通过SQL Server代理作业自动执行。

In part 2 of this series, Reporting Results, different methods of querying the data will be explored. In addition, tweaks to the existing script will be made for result logging and a SQL Server Reporting Services Report will be setup.

在本系列的第2部分“ 报告结果”中 ，将探讨查询数据的不同方法。此外，将对现有脚本进行调整以记录结果，并设置SQL Server Reporting Services报告。

Next article in this series:

本系列的下一篇文章：

Backup testing with PowerShell – Part 2: Reporting results 使用PowerShell进行备份测试–第2部分：报告结果

SPOOLER ALERT: The descriptive section of this article didn’t include the logging mechanisms but the complete script referenced in the beginning did. Maybe there are hints of where part 2 will be heading in there.

SPOOLER ALERT：本文的描述性部分没有包括日志记录机制，但开头提到的完整脚本却包括在内。 也许有暗示第2部分将前往那里。