Solved

Powershell DBCC Checkdb all databases using a server name list

Posted on 2013-02-07
10
1,188 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A brief introduction to what I consider to be the best editor for PowerShell.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

806 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