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
Solved

Powershell DBCC Checkdb all databases using a server name list

Posted on 2013-02-07
10
1,218 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
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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