Verifying Last Successful CheckDB with Powershell and SMO - sorta

A few years ago, I wrote a script that was published on SQLServerCentral.com about how to verify the last successful CheckDB for all the databases on an instance. This was a tremendous help to me and other DBAs as we could now tell when the integrity was valid on our databases. If we kept record of this, we could know exactly to when we would have to restore to be guaranteed a consistent database.

I recently worked on a project where I needed to have Powershell collect this date. Much to my chagrin, SMO does not expose this property. I can get the LastBackupDate, LastLogBackupDate, and LastDifferentalBackupDate as properties of the database class, however LastSuccessfulIntegrityCheck is just not there.

I filed a Microsoft Connect in December, 2011 that requested to have this feature added to the SMO database class - - but that has yet to be responded to by Microsoft.

I knew I had to do something so that Lord Vader wouldn't smack me with the dark side of the force. I eventually just reverted to T-SQL and the ExecuteWithResults database method to return the DBCC DBINFO result set to Powershell for each database. The script below will give you the last successful checkdb for all databases in any instances that you provide in a text file. This script uses a very simple custom object - $iDatabase - and loads these objects up into an array for further processing later. This array could be piped to the ConvertTo-HTML or the ConvertTo-CSV cmdlet to create a more flexible output or even through the Where-Object to only report on those that were out of spec. In this example, however, it just sends all the rows unfiltered to the Format-Table cmdlet.

Be certain to vote for the Microsoft Connect suggestion if you think that this would be a valuable addition to the SMO database class.

#************************
#     LastSuccessfulCheckDB
#    Author:  Kyle Neier, Perpetual Technologies
#    Reports on the last successful checkdb for all databases within all instances provided
#    
#    $InstanceList file should be a file of SQL instances in either server, server,port or server\instancename
#    
#************************

param
(
    [string]$InstanceList = "C:\Users\kneier\Documents\Powershell\InstanceList.txt"
)

# Load SMO assembly
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null;

#Initialize Array to hold new database objects
$iDatabases = @()

#Loop over each instance provided
foreach ($instance in $(get-content $InstanceList))
{
     try
    {
        "Connecting to $instance" | Write-Host -ForegroundColor Blue
        
        $srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $instance;
        #How many seconds to wait for instance to respond
        $srv.ConnectionContext.ConnectTimeout = 5
        $srv.get_BuildNumber() | out-Null
    }
    catch
    {
        "Instance Unavailable - Could Not connect to $instance." | Write-Host -ForegroundColor Red
        continue
    }
    
    $srv.ConnectionContext.StatementTimeout = $QueryTimeout
    
    foreach($Database in $srv.Databases)
    {
        #create object with all string properties
        $iDatabase = "" | SELECT InstanceName, DatabaseName, LastSuccessfulCheckDB
        #populate object with known values
        $iDatabase.InstanceName = $srv.Name
        $iDatabase.DatabaseName = $database.Name
        
        try
        {
            #Get date of last successful checkdb
            #executes dbcc dbinfo on database and narrows by dbi_dbcclastknowngood
            $database.ExecuteWithResults('dbcc dbinfo() with tableresults').Tables[0] | `
            ?{$_.Field -eq "dbi_dbccLastKnownGood"}| `
            %{$iDatabase.LastSuccessfulCheckDB = [System.DateTime]$_.Value} -ErrorAction Stop
        }
        catch
        {
            "CheckDB could not be determined for $instance.$database" | Write-Host -ForegroundColor Red
        }
        
        #add the iDatabase object to the array of iDatabase objects
        $iDatabases += $iDatabase
    }
}

#output all the databases as a table for viewing pleasure
$iDatabases | ft

About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter