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

A Month of PowerShell – Day 25 (Integrity Checks)

Welcome to Day 25 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.

We all know that we need to run integrity checks on our databases as frequently as possible to ensure that the databases are not corrupt, and to detect the corruption at the earliest possible time when you might have other resources (for example, backups) to assist in recovering the data. In PowerShell, you can run all of the DBCC CHECK consistency checks, except for DBCC CHECKDB. Since DBCC CHECKDB consists of running other DBCC CHECK consistency checks, the capability is still there, you just have to call the various checks individually. The following table shows a cross-reference of the DBCC CHECK commands to their corresponding PowerShell SMO methods and in which collections you can find those methods:

DBCC CHECK Command SMO Collection SMO Method
DBCC CHECKALLOC Databases CheckAllocations / CheckAllocationsDataOnly
DBCC CHECKCATALOG CheckCatalog
DBCC CHECKCONSTRAINTS
DBCC CHECKDB Not implemented Not implemented
DBCC CHECKFILEGROUP Databases / FileGroups CheckFileGroup / CheckFileGroupDataOnly
DBCC CHECKIDENT Databases / Tables CheckIdentityValues
DBCC CHECKTABLE Databases / Tables CheckTables / CheckTablesDataOnly

By now, it should be pretty easy to know how to run these methods in PowerShell:

 

#Assign variables
$Instance   = "localhost\SQL2012"
$DBName     = "PoSh"
 
#Assign the SMO class to a variable
$SMO        = "Microsoft.SqlServer.Management.Smo"
 
# get the server
$Server = New-Object ("$SMO.Server") "$Instance"
 
# assign the database name to a variable
$MyDB = $Server.Databases[$DBName]
 
#Run the consistency check
$MyDB.CheckCatalog([Microsoft.SqlServer.Management.Smo.RepairType]::None)
$MyDB.CheckAllocations([Microsoft.SqlServer.Management.Smo.RepairType]::None)
$MyDB.CheckTables([Microsoft.SqlServer.Management.Smo.RepairType]::None)

 

Comments

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

Loading comments...