Solved

Powershell DBCC Checkdb all databases using a server name list

Posted on 2013-02-07
10
1,173 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Does Powershell have you tied up in knots?

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

"Migrate" an SMTP relay receive connector to a new server using info from an old server.
A procedure for exporting installed hotfix details of remote computers using powershell
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

896 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now