Welcome to Day 25 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at //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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
#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) |
