Solved

Powershell DBCC Checkdb all databases using a server name list

Posted on 2013-02-07
10
1,234 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article will help you understand what HashTables are and how to use them in PowerShell.
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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