CODEWITHSUNDEEP
sql-serverpowershellt-sqldatabase-administration
Serdia
Serdia

Reputation: 4428

How to capture output message and save it to variable in powershell and SqlCmd

I am using a powershell code https://dba.stackexchange.com/a/254401/92058 that is looping through each database on an instance and checking for corruption using DBCC CheckDB sql command.

If a corruption detected (like in this case a page is corrupted on DB 50Ways), the DBCC CheckDB command will output a message (not an error!).

As you can see, processing database 50Ways the message popped up below, indicating that it found a corrupted page.

So how can I capture only this message (if any) and save it to a variable, so then, I can shoot an email notification to myself.

$ServerInstance = "myinstance" ## instance name 
$Databases = Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query "SELECT [name] AS [Database] FROM sys.databases  ORDER BY 1 DESC;"

foreach ($DB in $Databases)
{
    Write-Output "Processing $($DB.Database)..."

    Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Query "DBCC CHECKDB ([$($DB.Database)]) WITH NO_INFOMSGS,PHYSICAL_ONLY;" -OutputSqlErrors:$true -Verbose
}
Write-Output "Complete."

Output looks like this:

Processing WideWorldImporters...
Processing tempdb...
Processing msdb...
Processing model...
Processing master...
Processing DBA...
Processing AdventureWorks2017...
Processing 50Ways...
Invoke-SqlCmd : Table error: Object ID 901578250, index ID 0, partition ID 
72057594043105280, alloc unit ID 72057594049527808 (type In-row data), page (1:312). 
Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Object ID 901578250, index ID 0, partition ID 72057594043105280, alloc unit ID 
72057594049527808 (type In-row data): Page (1:312) could not be processed.  See other 
errors for details.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'ToLeaveYourLover' 
(object ID 901578250).
CHECKDB found 0 allocation errors and 2 consistency errors in database '50Ways'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB 
(50Ways).
At line:11 char:5
+     Invoke-SqlCmd -ServerInstance $ServerInstance -Database master -Q ...
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlEx 
   ecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScr 
   iptCommand
 
Complete.

As on a picture, only a red message need to be captured in a variable.

enter image description here

Also tried using TRY CATCH block, but it simply not capturing an error.

BEGIN TRY
   DBCC CHECKDB ([50Ways]) WITH NO_INFOMSGS,PHYSICAL_ONLY
END TRY
BEGIN CATCH
   SELECT 
          ERROR_MESSAGE() AS [Error Message]
         ,ERROR_LINE() AS ErrorLine
         ,ERROR_NUMBER() AS [Error Number]  
         ,ERROR_SEVERITY() AS [Error Severity]  
         ,ERROR_STATE() AS [Error State]  

END CATCH

Upvotes: 2

Views: 1296

Answers (1)

Daniel
Daniel

Reputation: 5114

These are just standard exception errors that you can capture using the CommonParameter -ErrorVariable or by using the automatic variable $Error.

Below is an example of using ErrorVariable. We tell Invoke-SqlCmd to store any errors encountered in a variable called 'dbCheckErrors'. We later check if this variable $dbCheckErrors contains any ErrorRecords and if it does we collect all the Exception messages into our $errorCollection array for later access. Once done with our db checks we can either view the messages, save the messages into file, both, or whatever else.

# Create an empty array to later capture error message in our loop
$errorCollection = @()
foreach ($DB in $Databases)
{
    Write-Output "Processing $($DB.Database)..."
    Invoke-SqlCmd -ErrorVariable 'dbCheckErrors' -ServerInstance '(localdb)\MSSQLLocalDB' -Database master -Query "DBCC CHECKDB ([$($DB.Database)]) WITH NO_INFOMSGS,PHYSICAL_ONLY;" -OutputSqlErrors:$true -Verbose 

    if ($dbCheckErrors) {
        # Do something with any errors here

        # Or just add the exception message(s) to a collection to handle later
        $errorCollection += $dbCheckErrors.Exception.Message
    }
}

# view the exception messages
$errorCollection

# save the exception messages to file
$errorCollection | Set-Content -Path '.\dbCheckErrors.log'

Upvotes: 4

Related Questions