?
Solved

Powershell DBCC Checkdb all databases using a server name list

Posted on 2013-02-07
10
Medium Priority
?
1,297 Views
Last Modified: 2013-03-02
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
0
Comment
Question by:mooriginal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 38864927
0
 

Author Comment

by:mooriginal
ID: 38867652
$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() 

Open in new window

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
0
 
LVL 29

Expert Comment

by:becraig
ID: 38872005
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"}
0
Four New Appliances. Same Industry-leading Speeds.

But don't take it from us.  The Firebox M370 is Miercom tested and Miercom approved, outperforming its competitors for stateless and stateful traffic throughput scenarios.  Learn more about the M370, M470, M570 and M670 and find the right solution for your organization today!

 

Author Comment

by:mooriginal
ID: 38875722
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_2008.txt" | foreach-object {Invoke-Sqlcmd "DBCC checkdb;" -ServerInstance "$_\MyInstance"}
And same error....
0
 
LVL 29

Expert Comment

by:becraig
ID: 38877027
0
 

Author Comment

by:mooriginal
ID: 38880023
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 ?
0
 
LVL 29

Expert Comment

by:becraig
ID: 38880596
0
 

Accepted Solution

by:
mooriginal earned 0 total points
ID: 38913012
I have managed to get help from another forum and get a working piece of code which is attached

Still willing to give out the points for anyone who can help output the dbcc results to a csv file
thought something like this.
{Export-Csv "c:\powershell\DBCC\$messageText_DBCC.csv"}  ???

Let me be clear please if you could post the working code..

Otherwise I will close the call

function Execute-DBCC
{
    param (
        [parameter(Mandatory = $true)][string]$serverInstance
    )
    $connString = "Server=$serverInstance;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

    $masterCmd.CommandText = "EXECUTE master.sys.sp_MSforeachdb 'DBCC CHECKDB([?]) WITH TABLERESULTS'"
    $masterConn.Open()
    $reader = $masterCmd.ExecuteReader()

    if ($reader.HasRows -eq $true) 
    {
        while ($reader.Read()) 
        {
            $messageText = $reader["MessageText"]

            if ($reader["Level"] -gt 10) 
                { Write-Host $messageText -backgroundcolor Yellow -foregroundcolor Red  } 
            else 
                { Write-Host $messageText  }
        }
        $reader.Close()
    }
    $masterConn.Close() 
}
[void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")
$servers = @(Get-Content ".\servers.txt")
$servers | %{
    Execute-DBCC $_
}

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38928348
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.
0
 

Author Closing Comment

by:mooriginal
ID: 38944974
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
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question