Powershell script will not continue even after error

Question

I have done some research for hours and currently stuck, i have a look that picks up a bunch of files, and then passes it onto some written functions, the issue that i am having is that if I have 200 files to process, I do not want every error to terminate the script because that would mean that the whole thing needs to be re-executed again.

So I want to use Try/Catch or any other means to trap the error, so that i know about it, but i want the loop to move onto the next item and process it. When I removed the Try..Catch within the loop and specified erroraction = 'continue', it did continue but then failed for all the files because the database connection was still open.

Any ideas here ?

So the objective is that during the loop, if an error is encountered for a file, just move on to the next one but highlight the error.

function GetDatabaseFiles ([STRING]$backupfile)
{

Try
{

$SQLConnection.Open()
$SQLQuery = "RESTORE FILELISTONLY 
FROM DISK = N'$backupfile'
WITH NOUNLOAD"
$SQLCommand = New-Object system.Data.SqlClient.SqlCommand
$SQLCommand.CommandText = $SQLQuery
$SQLCommand.Connection = $SQLConnection
$SQLAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SQLAdapter.SelectCommand = $SQLCommand
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SQLConnection.Close()
return $DataSet.Tables[0] | Select-Object LogicalName,PhysicalName,type
}


Catch 
{
# Handle the error
$err = $_.Exception
write-host $err.Message -ForegroundColor Red
while( $err.InnerException ) {
$err = $err.InnerException
write-host $err.Message -ForegroundColor Red
LogInfo -db "Database file - $backupfile" -message "ERROR DETAILS for Getting DB Files section !!!! $err.Message"

}
if ($error) {   $failedcount ++ }	  
}



}  




[STRING]$SQLServer                = $dbserver 
[STRING]$SQLDatabase             = 'master'
[STRING]$SQLConnectString        = "Data Source=$SQLServer; Initial Catalog=$SQLDatabase; Integrated Security=True; Connection Timeout=0"
[OBJECT]$SQLConnection             = New-Object System.Data.SqlClient.SqlConnection($SQLConnectString);

$files = Get-ChildItem  $backup_path -Recurse | Where-Object {$_.extension -eq ".bak"} | Sort-Object $_.name
$total_count  = $files.Length

Try
{

$error.clear()
# Start looping through each backup file and restoring the databases
foreach ($filename in $files) {

$filecount ++
write-host "Currently attemping to restore the backup file $filename number  $filecount" -ForegroundColor "Green"

#Set the filename variable to the fullpath/name of the backup file
$filename = $filename.FullName


$dbFiles = GetDatabaseFiles -backupfile $filename #-ErrorAction Continue
$dbFiles = $dbFiles[1..$dbFiles.Length]


}
}


catch 
{
# Handle the error
$err = $_.Exception
write-output $err.Message
while( $err.InnerException ) {
$err = $err.InnerException
write-output $err.Message


}

if ($error) {   $failedcount ++ }	  
}
finally {
write-output "script completed"

}

G42 · Accepted Answer · 2017-06-15 15:53:11Z

4

Your code is quote verbose. I see this structure in your loop:

Try{

    Foreach(...){
        $dbFiles = GetDatabaseFiles -backupfile $filename #-ErrorAction Continue
    }

}
catch{
    ...
}
finally{
    write-output "script completed"
}

Instead, try this. -ErrorAction Stop will turn non-terminating errors into terminating errors. Try/catch does not work with non-terminating errors.

Foreach(...){

    Try{
        $dbFiles = GetDatabaseFiles -backupfile $filename #-ErrorAction Stop
    }
    catch{

    }

}
write-output "script completed"

answered Jun 15, 2017 at 15:53

G42

10.1k2 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Powershell script will not continue even after error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related