Testing & Consolidating Results for SharePoint 2010 Content Databases using PowerShell

One of the most common things that people do during a migration from older versions of SharePoint into the 2010 version is using the PowerShell's Test-SPContentDatabase cmdlet to see how healthy the database is. When this test is performed, the cmdlet produces some results that will be very instrumental in figuring out what is going on with the database. In environments where we have a smaller number of content databases this is a very easy thing to do, but what does one do when there are several hundred databases with terabytes of content? I ended up in a situation like this and I decided to go & write a script using PowerShell that will come to my rescue.
The idea here is to invoke a script, supply a bunch of database names & a web application URL and let the script iterate through each database, test it, obtain the results and consolidate them into a CSV file. While performing the consolidation, the name of the database from which the results came are added to the CSV file therefore making it easy to understand which results belong to which database. This way of summarizing is also very useful to analyze the errors via Excel. All you have to do is simply import the results into an excel spreadsheet & use a pivot table to analyze the results. Now let's jump into the script...

 function Test-SPContentDatabases()  
 {  
      #region Params  
      [CmdletBinding(SupportsShouldProcess=$true)]  
      param(  
                [Parameter(Mandatory = $true)]  
                [ValidateNotNullOrEmpty()]  
                [string] $WebApplicationUrl,  
                [Parameter(Mandatory = $true)]  
                $DatabaseList,                 
                [Parameter(Mandatory=$true)]  
                [ValidateNotNullOrEmpty()]  
                [string] $TestLog  
           )  
      #endregion  
      process  
      {  
           #region Vars  
           $EmailBody = @()  
           $EmailSubject = ""  
           $HostName = hostname  
           $StartTime = Get-Date -Format G  
           $EndTime = Get-Date -Format G  
           $InvokeCommandFailed = $true  
           $TestResults = @()  
           $ConsolidatedTestResults = $null  
           #endregion  
           #region Create Test Result File  
           $TestResultsFile = $HostName + "_" + "TestDB_" + $Batch  
           #endregion  
           $WhatIfMessage = "Execute Test-SPContentDatabase on : " + $Database + " -WebApplication: " + $WebApplicationUrl  
           if($PSCmdlet.ShouldProcess("$WhatIfMessage"))  
           {  
                foreach($Database in $DatabaseList)  
                {  
                     $Message = "Attempting to test database " + $Database + " in relation to the web application located at " + $WebApplicationUrl  
                     Write-Host $Message  
                     $Command = "Test-SPContentDatabase -Name " + $Database.Name + " -WebApplication " + $WebApplicationUrl  
                     $Message = "Attempting to execute PS Command: " + $Command  
                     Write-Host $Message  
                     try  
                     {  
                          $ProcessDuration = Measure-Command{$TestResults = Invoke-Expression -Command $Command}                 
                          $InvokeCommandFailed = $false  
                          $Duration = $ProcessDuration.Days.ToString() + ":" + $ProcessDuration.Hours.ToString() + ":" + $ProcessDuration.Minutes.ToString() + ":" + $ProcessDuration.Seconds.ToString()                           
                     }  
                     catch  
                     {  
                          $InvokeCommandFailed = $true  
                          $Message = "Failed to execute PS Command: " + $Command  
                          Write-Host $Message   
                     }  
                     if($InvokeCommandFailed)  
                     {  
                          $Message = "PS Command Execution Failed: " + $Command  
                          Write-Host $Message  
                     }  
                     else  
                     {  
                          Write-Host $Message  
                          if($TestResults -ne $null -and $TestResults.Count -gt 0)  
                          {  
                               #This is where I add the database name to each row in the result set after testing a database!  
                               $ConsolidatedTestResults += AddDBNameToTestLog -DatabaseName $Database.Name -InputCSVObject $TestResults  
                               $Message = "Database " + $Database + " has " + $TestResults.Count + " errors in it."  
                               Write-Host $Message -ForegroundColor Red  
                          }  
                          else  
                          {  
                               $Message = "No errors were found in the database " + $Database.Name  
                               Write-Host $Message -ForegroundColor Green  
                          }  
                     }  
                }  
                $ConsolidatedTestResults | Export-Csv -Path $TestLog -NoTypeInformation;  
                if($ConsolidatedTestResults -ne $null -and $ConsolidatedTestResults.Count -gt 0)  
                {  
                     $Message = "There are a total of " + $ConsolidatedTestResults.Count + " errors to be resolved in the databsaes tested."                      
                     Write-Host $Message -ForegroundColor Red  
                }  
                else  
                {  
                     $Message = "No errors were found in the tested databases."  
                     Write-Host $Message -ForegroundColor Green  
                }  
                Write-Host $Message -ForegroundColor Green  
           }  
      }  
 }  
 function AddDBNameToTestLog($InputCSVObject, $DatabaseName)  
 {  
      foreach($Row in $InputCSVObject)  
      {  
           $Row | Add-Member -MemberType NoteProperty -Name Database -Value $DatabaseName             
      }       
      return $InputCSVObject  
 }