Show crash for SQL 2005 Job running Powershell script via CMD hint, IF PS script not working

Question

I have an instance of SQL 2005 that runs a job that uses a Powershell script to rename the current TX TX log backup file by adding “-PrevDay” to it (subsequently deleting the backup already named “XXX-PrevDay. Bak” if it exists), and then perform a full backup of the database backup and TX backup if the database is not in Simple mode.

A SQL Server Agent job runs a Powershell script through CMD at each step of the job, and a powershell script starts sql backups using the Invoke-SQLCmd cmdlet. This works fine if the backup fails because the SQL job is still showing as Success. This is because the SQL job that runs the Powershell script through the CMD hint only cares if the Powershell script is working ... not if the IN script commands actually succeed or fail.

Is it possible using the error trap in powershell (or any method really) to make the powershell script crash the cmd command when the script runs ... so that SQL Job reports a crash

Does that even make sense? Lol

I would suggest that if I could use SQL 2008, which allows the SQL type of the job step to be “Powershell Script” (instead of the type of step being supposed to be the operating system ... that runs the PS script), that would not be a problem. .. however ... this is not an option.

Currently, the job step runs the powershell script via CMD using the parameters for DBName, Path and Servername and looks like this:

powershell.exe "C:\SQLBackupScriptsTest\SQLServerBackup.ps1" -DBName 'Angel_Food' -Path 'E:\SQLBackup1' -Server 'DEVSQLSRV' 

A valid Powershell script is as follows:

 Param($DBName,$Path,$Server) ## Add sql snapins...must have for Invoke-Sqlcmd with powershell 2.0 ## add-pssnapin sqlserverprovidersnapin100 add-pssnapin sqlservercmdletsnapin100 [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null ## Set parameter for finding DB recovery model ## $Recovery = (Invoke-Sqlcmd -Query "SELECT recovery_model_desc FROM sys.databases WHERE name = '$DBName'" -Server $Server) ## Do full backup of DB ## (Invoke-Sqlcmd -Query "BACKUP DATABASE $DBName TO DISK = N'$Path\$DBName\$DBName.bak' WITH NOFORMAT, INIT, NAME = N'$DBNameTEST', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM" -Server $Server -ConnectionTimeout 0 -QueryTimeout 65535) ############################################################################################################ ## Check recovery mode, if FULL, check for Log-PrevDay.bak. If exists then delete. If not exist, move on ## ## Then check for Current TX log backup. If exists, rename to Log-PreDay.bak. If not exist, move on ## ## Then perform TX Log backup ## ## If recovery mode NOT FULL, do nothing ## ############################################################################################################ IF ($Recovery.recovery_model_desc -eq 'FULL') #THEN# { ## Look to see if PrevDay TX log exists. If so, delete, if not, move on ## IF (Test-Path $Path\$DBName\$DBName-Log-PrevDay.bak) #THEN# {remove-item $Path\$DBName\$DBName-Log-PrevDay.bak -force} ELSE {} ## Look to see if current TX log exists, if so, rename to Prev Day TX Log, if not, move on ## IF (Test-Path $Path\$DBName\$DBName-Log.bak) #THEN# {rename-item $Path\$DBName\$DBName-Log.bak -newname $DBName-Log-PrevDay.bak -force} ELSE {} Invoke-Sqlcmd -Query "BACKUP LOG $DBName TO DISK = N'$Path\$DBName\$DBName-Log.bak' WITH NOFORMAT, INIT, NAME = N'$DBName LogTEST (Init)', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM" -Server $Server -ConnectionTimeout 0 -QueryTimeout 65535} ELSE {} 

Answer 1

Ok, after watching some blogs and some trial / error / luck ... I did this to do what I want.

I decided that I needed to send the Powershell exit code back to CMDEXEC. However, from what I found, Powershell always defaults to exit code 0 (success) ... unless you jump over several hoops using 2 PS scripts ... which I really didn't want to do. So I decided to just catch any error, and if any error was trapped, let it exit the PS script with code 1, no matter what. Honestly ... all I really wanted was a reliable exit code of 0 (success) or 1 (failure). So ... the long story is short ... here's how I changed my code.

I changed CMDEXEC of each step to this:

 powershell.exe -noprofile C:\SQLBackupScriptsTest\SQLServerBackup2.ps1 -DBName 'Angel_Food' -Path 'E:\SQLBackup' -Server 'DEVSQLSRV' @Echo %errorlevel% 

Then I changed my PS script to:

 Param($DBName,$Path,$Server) ## Add sql snapins...must have for Invoke-Sqlcmd with powershell 2.0 ## add-pssnapin sqlserverprovidersnapin100 add-pssnapin sqlservercmdletsnapin100 [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null ## Set parameter for finding DB recovery model ## $Recovery = (Invoke-Sqlcmd -Query "SELECT recovery_model_desc FROM sys.databases WHERE name = '$DBName'" -Server $Server) ## Do full backup of DB ## trap {$_.Exception.Message; exit 1; continue}Invoke-Sqlcmd -Query "BACKUP DATABASE $DBName TO DISK = N'$Path\$DBName\$DBName.bak' WITH NOFORMAT, INIT, NAME = N'$DBNameTEST', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM" -Server $Server -ConnectionTimeout 0 -QueryTimeout 65535 -ea stop ############################################################################################################ ## Check recovery mode, if FULL, check for Log-PrevDay.bak. If exists then delete. If not exist, move on ## ## Then check for Current TX log backup. If exists, rename to Log-PreDay.bak. If not exist, move on ## ## Then perform TX Log backup ## ## If recovery mode NOT FULL, do nothing ## ############################################################################################################ IF ($Recovery.recovery_model_desc -eq 'FULL') #THEN# { ## Look to see if PrevDay TX log exists. If so, delete, if not, move on ## IF (Test-Path $Path\$DBName\$DBName-Log-PrevDay.bak) #THEN# {remove-item $Path\$DBName\$DBName-Log-PrevDay.bak -force} ELSE {} ## Look to see if current TX log exists, if so, rename to Prev Day TX Log, if not, move on ## IF (Test-Path $Path\$DBName\$DBName-Log.bak) #THEN# {rename-item $Path\$DBName\$DBName-Log.bak -newname $DBName-Log-PrevDay.bak -force} ELSE {} trap {$_.Exception.Message; exit 1; continue}Invoke-Sqlcmd -Query "BACKUP LOG $DBName TO DISK = N'$Path\$DBName\$DBName-Log.bak' WITH NOFORMAT, INIT, NAME = N'$DBName LogTEST (Init)', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM" -Server $Server -ConnectionTimeout 0 -QueryTimeout 65535 -ea stop} ELSE {} 

I basically added trap {$_.Exception.Message; exit 1; continue} trap {$_.Exception.Message; exit 1; continue} trap {$_.Exception.Message; exit 1; continue} right before each Invoke-Sqlcmd and ended each Invoke-Sqlcmd -ea stop .

trap $_.Exception.Message aborts any error ... collects an error message, then exit 1 immediately exits the PS script with exit code 1 .

The SQL task reads each step from 0 or 1 and automatically interprets 0 as success, and 1 as failure and correctly evaluates SQL Job as successful or unsuccessful. Also, since I captured the actual error message ... it appears in the SQL job history.

That turned out to be exactly what I needed. :)

If you're curious ... here are the blogs that have helped me the most:

• Returning exit code from PowerShell script (weblogs.asp.net)
• Trap Bugs (Powershell.com)