2 different occasions at SQL Saturday Cambridge 2013 made me realise that I needed to ‘justify’ my use of Invoke-SQLCmd to run DBCC back on Day 21 of 31 Days of SQL Server Backup and Restore using PowerShell: Verfiying a restored database. One was during my session (Slides and scripts here), and the other was during Seb Matthews session where he said to view every use of Invoke-SQLCmd as a failure.
The first thing I don’t like about this particular message is what happens when you check a ‘good’ database:
Import-Module sqlps -DisableNameChecking
$sqlsvr = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server("Server1")
$db = $sqlsvr.Databases.item("GoodDB")
$db.CheckTables("None")
If you run this, you’ll notice you get nothing back, not a thing. This is because CheckTable
s runs with NO_INFOMSGS
as default. And there’s no way to override it. This might work for some applications, but in some environments you would want to have an informational messages returned and recorded, as proof that all checks were ran and passed.
That’s strike 1 for me.
If you’re wondering what the parameter to CheckTables
is, it’s the repair level. Accepted values are:
- None
- Fast
- Rebuild
- AllowDataLoss
Which operate in the same way as they do under T-SQL
If you’re lucky enough not to have a corrupt database to play with, then Paul Randal has some you can download: (Paul Randal, SQLSkills.com, Corrupt SQL Server Databases). Assuming you’ve restored the db as chkDBCC then you can check it as so:
Import-Module sqlps -DisableNameChecking
$sqlsvr = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server("Server1")
$db = $sqlsvr.Databases.item("chkDBCC")
$db.CheckTables("None")
This time we will get some output, unfortunately this time a not hugely useful generic error message:
Exception calling "CheckTables" with "1" argument(s): "Check tables failed for Database 'chkdbcc'. "
At line:5 char:1
+ $db.CheckTables("None")
+ ~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : Fai
Strike 2
We can improve on this is we actually examine the error in a bit more detail by putting it in a Try, Catch
block:
Import-Module sqlps -DisableNameChecking
$sqlsvr = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server("Server1")
$db = $sqlsvr.Databases.item("chkdbcc")
try{
$db.CheckTables("None")
}
catch{[System.Exception]
$err = $_.Exception
while ( $err.InnerException )
{
$err = $err.InnerException
write-output $err.Message
};
}
And now we get a more informative response:
Check tables failed for Database 'chkdbcc'.
An exception occurred while executing a Transact-SQL statement or batch.
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=1) in
sys.columns does not have a matching row (object_id=1977058079) in sys.objects.
Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row (object_id=1977058079,column_id=2) in
sys.columns does not have a matching row (object_id=1977058079) 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 'chkDBCC'.
To get the full information returned in our previous examples:
Error : 8992
Level : 16
State : 1
MessageText : Check Catalog Msg 3853, State 1: Attribute (object_id=1977058079) of row
(object_id=1977058079,column_id=2) in sys.columns does not have a matching row
(object_id=1977058079) in sys.objects.
RepairLevel :
Status : 16
DbId : 23
DbFragId : 1
ObjectId : 0
IndexId : -1
PartitionId : 0
AllocUnitId : 0
RidDbId : 23
RidPruId : 0
File : 0
Page : 0
Slot : 0
RefDbId : 23
RefPruId : 0
RefFile : 0
RefPage : 0
RefSlot : 0
Allocation : 1
Well, we can’t. As well as enforcing the NO_INFOMSGS
clause, CheckTables
doesn’t allow the use of TABLERESULTS
.
Which is strike 3, and it’s out of here!
This is all personal opinion, but this is one of the very few DBA tasks where I will always opt to use Invoke-SQLCmd
over an SMO object.
Hopefully we might see a better SMO option with the release of SQL Server 2014…..
This post is part of a series posted between 1st September 2013 and 3rd October 2013, an index for the series is available here.