Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Kyle Neier

Kyle has been working with computers professionally since 1995 in both a development and administrative capacity. For over 10 of those years, SQL Server has been the focus of both a professional and personal passion. Kyle has worked in online retail, manufacturing, EMR software, and pharmaceutical industries. Kyle joined PTI in 2011 as a Senior Microsoft Consultant where he continues to chase his passion of automating DBA processes and doing everything humanly possible to prevent the 0200 page. Kyle serves as the Vice-President of the Indianapolis chapter of PASS (www.indypass.org) and served on the committee to bring SQL Saturday to Indianapolis. He shares his passion for SQL Server with others through speaking and mentoring at local and regional events.

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

Comments

Leave a comment on the original post [sqldbamusings.blogspot.com, opens in a new window]

Loading comments...