mooriginal
asked on
Powershell DBCC Checkdb all databases using a server name list
Ive been trying to find a powershell script that can execute using a server list as a txt file.
Loop dbcc checkdb on all databases per the server list
If possible output the results of the dbcc to an excel file written to the local c drive of the server
Anyone come up with the code for this ?
Thanks
Mo
Loop dbcc checkdb on all databases per the server list
If possible output the results of the dbcc to an excel file written to the local c drive of the server
Anyone come up with the code for this ?
Thanks
Mo
ASKER
$ScriptName = $myInvocation.MyCommand.Name
[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")
$ConnString = "Server=DB-OCC05;Integrated Security=SSPI;Database=master;Application Name=$ScriptName"
$MasterConn = new-object ('System.Data.SqlClient.SqlConnection') $ConnString
$MasterCmd = new-object System.Data.SqlClient.SqlCommand
$MasterCmd.Connection = $MasterConn
$SqlDBCC = "DBCC CHECKDB(master) WITH TABLERESULTS"
$MasterCmd.CommandText = $SqlDBCC
$MasterConn.Open()
$Rset = $MasterCmd.ExecuteReader()
If ($Rset.HasRows -eq $true) {
While ($Rset.Read()) {
$line = $Rset["MessageText"]
If ($Rset["Level"] -gt 10) {
Write-Host $line -backgroundcolor Yellow -foregroundcolor Red
} else {
Write-Host $line
}
}
$Rset.Close()
}
$MasterConn.Close()
Thanks for link - but I wanted to PS to do the dbcc itself.Ive found the following code above which works provided you have specified the server name and databases.
Could someone provide a script that allows this code to read a server.txt file with list of server names - and that it runs against all databases when its run automatically.
And finally if it could output to a csv file the results of the DBCC's.
Thanks
Look into using invoke-sqlcmd
http://technet.microsoft.com/en-us/library/cc281720.aspx
e.g.
Invoke-Sqlcmd "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
you can feed in your server name and instance:
gc serverlist.txt | foreach-object {Invoke-Sqlcmd "DBCC xxx;" -ServerInstance "$_\MyInstance"}
http://technet.microsoft.com/en-us/library/cc281720.aspx
e.g.
Invoke-Sqlcmd "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
you can feed in your server name and instance:
gc serverlist.txt | foreach-object {Invoke-Sqlcmd "DBCC xxx;" -ServerInstance "$_\MyInstance"}
ASKER
Hmm
Im not great on PS - and hence my question about a working answer...
However I tried as a test the following line from a powershell window directly
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
And get error
The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path
was included, verify that the path is correct and try again.
At line:1 char:14
Also tried
gc "C:\Powershell\Servers_200 8.txt" | foreach-object {Invoke-Sqlcmd "DBCC checkdb;" -ServerInstance "$_\MyInstance"}
And same error....
Im not great on PS - and hence my question about a working answer...
However I tried as a test the following line from a powershell window directly
Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "MyComputer\MyInstance"
And get error
The term 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path
was included, verify that the path is correct and try again.
At line:1 char:14
Also tried
gc "C:\Powershell\Servers_200
And same error....
Here is a good post to resolve that for you:
http://jasonq.com/index.php/2012/03/3-things-to-do-if-invoke-sqlcmd-is-not-recognized-in-windows-powershell/
http://jasonq.com/index.php/2012/03/3-things-to-do-if-invoke-sqlcmd-is-not-recognized-in-windows-powershell/
ASKER
Thanks for the link - unfortunately the powershell environment for running these sort of checks is on a SQL 2005.
I wont be able to get this policy changed.
Is there any other way of doing this not using invoke ?
I wont be able to get this policy changed.
Is there any other way of doing this not using invoke ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Still willing to give out the points for anyone who can help output the dbcc results to a csv file
Check out the pipe (|) operator to do this.
Check out the pipe (|) operator to do this.
ASKER
I have after spoken to a Mod to confirm accepted my own solution as no expert here has given me anything working other than some suggestions
look into this :http://www.pertell.com/sqlservings/archive/tag/powershell/
Aaron