So far we’ve just restored out databases, and assumed that the lack of an error from PowerShell means that all is good. Well, it can be, but it’s not guaranteed.
Paul S Randall provides a lot of examples of corrupted databases which will restore absolutely fine – http://www.sqlskills.com/blogs/paul/corruption-demo-databases-and-scripts/ – so just relying on a lack of error doesn’t mean your database is ready for use. If you want a corrupted database to run through these examples and are lucky enough to not have one to hand, these are ready made for you.
Also, a restored copy of your production database is the perfect place to do heavy DBCC checks. Getting a window to run them on production may be a nightmare, but you won’t have to negotiate if it’s a restored copy that noone else is using.
Today we’re just going to look at using SQL Server’s built in Database Consistency Check (DBCC), but there is nothing to stop you from rolling your own validation scripts which are aware of your business requirements as well as SQL Server’s internal needs.
To do this we’ll be using Invoke-SQLcmd, which lets us run SQL queries. Invoke-SQLcmd returns a PowerShell DataTable so we can take out quite complex results for later processing or saving.
At it’s simplest we can simply run DBCC against the database of our choice:
Import-Module sqlps -DisableNameChecking $results = Invoke-SQLCmd -ServerInstance "Server1" -Query "DBCC CHECKDB (database1) WITH TABLERESULTS"
Note that we use WITH TABLERESULTS, this ensures the output from DBCC goes smoothly into our $results table. As it stands this will return 80 rows whether the Database has issues or not as DBCC will return all of the informational messages as well.
If you want to keep the informational messages but don’t want act upon them, then you can filter them by averaging the Status column:
if (($results | Measure-Object -property status -Average).Average -eq 0){
# We have only information messages, do nothing
}else{
#We have error records, do something.
}
A status of 0 indicates no problem, so an average of 0 means that every value is 0.
You can also suppress the informational messages at the DBCC level by appending NO_INFOMSGS to your query:
Import-Module sqlps -DisableNameChecking $results = Invoke-SQLCmd -ServerInstance "Server1" -Query "DBCC CHECKDB (database1) WITH TABLERESULTS, NO_INFOMSGS"
Which means you’ll only get back rows for errors, in this case, you can simply check the size/length of your results:
if ($results.length -eq 0){
# We have only information messages, do nothing
}else{
#We have error records, do something.
}
There is also a 3rd option of return value. It is possible for the database to be so corrupted that DBCC does not complete, or even begin in some cases! Because of this, we need to wrap the Invoke-SQLCmd in a Try, Catch block so we catch this as well:
try{
$tmp2 = Invoke-Sqlcmd -Query "DBCC CHECKDB (fatal) with TABLERESULTS, NO_INFOMSGS" -ServerInstance "Server1" -ErrorAction Stop
}
catch{ [System.Exception]
$errorout += "major error, big problem"
$errorout += $error[0]
}
Note that we need add -ErrorAction Stop to our call to Invoke-SQLCmd, this is because in PowerShell terminology Invoke-SQLCmd is Non-Terminating, that is it won’t throw an error that stops execution or bet caught by Try,Catch. By adding -ErrorAction Stop we tell PowerShell that we want any errors raised to be Terminators, which means we can catch them. It is also possible to set this at a script level, but that can cause some unexpected things to bubble up, so only do so if you know what to expect.
There are a number of ways you can handle the errors. At the simplest level you can notify yourself or other operators that an error has been found, and then investigate manually. PowerShell provides a cmdlet Send-MailMessage which you can use to pass the message on:
Push-Location
import-module sqlps -DisableNameChecking
Pop-Location
$results = @()
$Database = "fatal"
$SQLServer = "Server1"
$errorout = ""
try{
$results = Invoke-Sqlcmd -Query "DBCC CHECKDB ($Database) with TABLERESULTS, NO_INFOMSGS" -ServerInstance $SQLServer -ErrorAction Stop
}
catch{ [System.Exception]
$errorout = $_
}
if (($results.length -eq 0) -and ($errorout -ne "")){
Send-MailMessage -SmtpServer smtphost.contoso.com -from sql@ncontoso.com -to stuart.moore@contoso.com -body $errorout -Subject "Fatal DBCC error for $Database"
}else{
$results | export-clixml c:\temp\dbcc.xml
Send-MailMessage -SmtpServer smtphost.contoso.com -from sql@ncontoso.com -to stuart.moore@contoso.com -body "See Attachment for output" -Subject "DBCC errors for $Database" -Attachments c:\temp\dbcc.xml
Remove-Item c:\temp\dbcc.xm
}
In this case I’m dropping the output into XML before sending it as an attachment, but PowerShell provided cmdlets for converting to text, CSV and other formats as well.
The results can also be written back to a database for audit and reporting purposes. You can also write the information from multiple restore servers back to a central repository. Unfortunately this has become slightly more complicated since the release of SQL Server 2012 as Microsoft have changed the columns that various DBCC commands retun. There are a number of options to cope with this:
- Different tables for SQL Server 2012, and pre 2012 SQL Server results
- Storing the data as XML in the database
- Only storing certain information (ie; ignore the bits that don’t fit)
Each of these presents benefits and drawbacks. Here’s a quick example of how to log it to a table. This based on have a table like this (this is the pre SQL Server 2012 version):
CREATE TABLE pre2012DBCCResults ( [ServerName] VARCHAR(200) NULL, [DatabaseName] VARCHAR(200) NULL, [DateOfRun] DATETIME NULL, [Error] INT NULL, [Level] INT NULL, [State] INT NULL, [MessageText] VARCHAR(7000) NULL, [RepairLevel] varchar(200) NULL, [Status] INT NULL, [DbId] INT NULL, [ObjectId] INT NULL, [IndexId] INT NULL, [PartitionId] INT NULL, [AllocUnitId] INT NULL, [File] INT NULL, [Page] INT NULL, [Slot] INT NULL, [RefFile] INT NULL, [RefPage] INT NULL, [RefSlot] INT NULL, [Allocation] INT NULL, )
And then to populate that table we add the following to our Verification script:
Push-Location
import-module sqlps -DisableNameChecking
Pop-Location
$results=@()
$Database = "semifatal"
$SQLServer = "ntuclstr-qc\quality"
$errorout = ""
try{
$results = Invoke-Sqlcmd -Query "DBCC CHECKDB ($Database) with TABLERESULTS, NO_INFOMSGS" -ServerInstance $SQLServer -ErrorAction Stop
}
catch{ [System.Exception]
$errorout = $_
}
if (($results.length -eq 0) -and ($errorout -ne "")){
Send-MailMessage -SmtpServer smtphost.contoso.com -from sql@ncontoso.com -to stuart.moore@contoso.com -body $errorout -Subject "Fatal DBCC error for $Database"
}else{
$results | Add-Member -MemberType NoteProperty -name ServerName -value $SQLServer
$results | Add-Member -MemberType NoteProperty -name DatabaseName -value $Database
$results | Add-Member -MemberType NoteProperty -name DateOfRun -value (get-date) -TypeName datetime
$ResultsServer = "Server2"
$ResultsDatabase = "RestoreChecks"
$ResultsTable = "pre2012DBCCResults"
$RestoreSQL = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($ResultsServer)
if ($RestoreSQL.VersionMajor -lt 12){
foreach ($row in $results){
$row.MessageText = $row.MessageText -replace ("'","''")
$str = "insert into "+$ResultsTable"+ ([ServerName], [DatabaseName],[DateOfRun],[Error],[Level],[State],[MessageText],[RepairLevel],[Status],[DbId],[ObjectId],[IndexId],[PartitionId],[AllocUnitId],[File],[Page],[Slot],[RefFile],[RefPage],[RefSlot],[Allocation]) values ('"+$row.ServerName+"','"+$row.DatabaseName+"','"+$row.DateOfRun+"','"+$row.Error+"','"+$row.Level+"','"+$row.State+"','"+$row.MessageText+"','"+$row.RepairLevel+"','"+$row.Status+"','"+$row.DbId+"','"+$row.Id+"','"+$row.IndId+"','"+$row.PartitionId+"','"+$row.AllocUnitId+"','"+$row.File+"','"+$row.Page+"','"+$row.Slot+"','"+$row.RefFile+"','"+$row.RefPage+"','"+$row.RefSlot+"','"+$row.Allocation+"')"
Invoke-Sqlcmd -query $str -ServerInstance $ResultsServer -Database $ResultsDatabase
}
Send-MailMessage -SmtpServer smtphost.contoso.com -from sql@ncontoso.com -to stuart.moore@contoso.com -body "Errors records in Table: $Resultstable in $ResultsDatabase on $ResultsServer" -Subject "DBCC errors for $Database" -
}else{
#Work with the SQL Server 2012 code
}
}
Again, in this example we’re only storing the results if there’s an error. But it would be simple enough to log all results if you wanted to by removing NO_INFOMSGS and moving the db write outside of the IF section.
If you wanted to run your own custom scripts, then you can just run them with Invoke-SQLCmd and handle the results however you want.
Tomorrow we’ll take a look at the performance differences between running your backups and restores via PowerShell or via T-SQL
This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.
Leave a Reply