如果通过PSD脚本执行Powershell脚本的SQL 2005作业显示失败，则PS脚本失败 [英] Show failure for SQL 2005 Job that executes Powershell script through CMD prompt, IF PS script fails
问题描述
我有一个SQL 2005实例，该实例运行一个作业，该作业使用Powershell脚本通过向其附加 -PrevDay来重命名当前的SQL TX Log备份文件（随后删除已经名为 XXX-PrevDay.bak的备份）如果数据库不在简单模式下，则运行数据库的完整备份和TX日志备份。
SQL Server代理作业启动在每个作业步骤中通过CMD通过Powershell脚本执行脚本，然后使用 Invoke-SQLCmd cmdlet通过Powershell脚本启动sql备份。除非备份失败，否则这样做会很好，因为SQL作业仍显示为成功。这是因为通过CMD提示符启动Powershell脚本的SQL作业仅关心Powershell脚本是否运行，而不关心脚本中的命令实际上是成功还是失败。
是否有可能在powershell中（或实际上是任何方法）使用错误捕获，使powershell脚本失败运行脚本的cmd提示操作...，以便SQL Job报告失败？ p>
这是否有意义？大声笑
我认为，如果我能够使用SQL 2008，则可以使用SQL作业步骤类型为 Powershell脚本（而不是步骤类型）是操作系统...启动PS脚本），这不是问题...但是...这不是一个选择。
现在，作业步骤通过使用用于DBName，Path和Servername的参数通过CMD运行powershell脚本，如下所示：
powershell。 exe C：\SQLBackupScriptsTest\SQLServerBackup.ps1 -DBName'Angel_Food'-路径'E：\SQLBackup1'-服务器'DEVSQLSRV'
实际的Powershell脚本如下所示：
Param（$ DBName，$ Path ，$ Server）
##添加sql snapins ...必须具有Powershell 2.0的Invoke-Sqlcmd ##
add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
[reflection.assembly] :: LoadWithPartialName（ Microsoft.SqlServer.Smo）| out-null
##设置用于查找数据库恢复模型的参数##
$ Recovery =（Invoke-Sqlcmd -Query从sys.databases查询SELECT recovery_model_desc，名称='$ DBName' -服务器$服务器）
##执行数据库的完整备份##
（Invoke-Sqlcmd-查询 BACKUP DATABASE $ DBName TO DISK = N'$ Path\ $ DBName\ $ DBName.bak'与NOFORMAT，INIT，名称= N'$ DBNameTEST'，跳过，NOREWIND，NOUNLOAD，STATS = 10，CHECKSUM-服务器$ Server -ConnectionTimeout 0 -QueryTimeout 65535）
＃ ############################################### ############################################### #######
##检查恢复模式，如果已满，请检查Log-PrevDay.bak。如果存在，则删除。如果不存在，请继续##
##然后检查当前TX日志备份。如果存在，请重命名为Log-PreDay.bak。如果不存在，请继续##
##然后执行TX日志备份##
##如果恢复模式不完整，则不执行任何操作##
######### ################################################ ##############################################
IF
（$ Recovery.recovery_model_desc -eq'FULL'）
＃THEN＃
{
##查看是否存在PrevDay TX日志。如果是这样，请删除（如果不是），请删除##
IF
（测试路径$ Path\ $ DBName\ $ DBName-Log-PrevDay.bak）
＃THEN＃
{删除项目$ Path\ $ DBName\ $ DBName-Log-PrevDay.bak -force}
ELSE
{}
##查看当前TX日志存在，如果存在，则重命名为上一交易日发送日志，如果不存在，则继续运行##
IF
（测试路径$ Path\ $ DBName\ $ DBName-Log.bak）
＃THEN＃
{重命名项目$ Path\ $ DBName\ $ DBName-Log.bak-新名称$ 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
{}
好吧，看了几篇博客和一些试错/运气/运气之后，……我做了它想要做的事。
我决定需要将Powershell退出代码发送回CMDEXEC。但是，从我发现的结果来看，Powershell总是默认将退出代码设置为0（成功）...除非您跳过使用2个PS脚本进行的侵扰...我真的不想这么做。因此，我决定只捕获任何错误，如果捕获了任何错误，则无论如何都要以代码1退出PS脚本。老实说...我真正想要的只是一个可靠的退出代码0（成功）或1（失败）。所以...长话短说...这就是我更改代码的方式。
我将每一步的CMDEXEC更改为：
powershell.exe -noprofile C：\SQLBackupScriptsTest\SQLServerBackup2.ps1 -DBName'Angel_Food'-路径'E：\SQLBackup'-服务器'DEVSQLSRV'
@Echo％errorlevel％
然后我将PS脚本更改为：
Param（$ DBName，$ Path，$ Server）
##添加sql snapins ...必须使用Powershell 2.0调用Sqlcmd ##
add-pssnapin sqlserverprovidersnapin100
add-pssnapin sqlservercmdletsnapin100
[reflection.assembly] :: LoadWithPartialName（ Microsoft.SqlServer.Smo）| out-null
##设置用于查找数据库恢复模型的参数##
$ Recovery =（Invoke-Sqlcmd -Query从sys.databases查询SELECT recovery_model_desc，名称='$ DBName' -Server $ Server）
##执行数据库的完整备份##
trap {$ _。Exception.Message; 1号出口；继续} Invoke-Sqlcmd-查询备份数据库$ DBName到磁盘= N'$ Path\ $ DBName\ $ DBName.bak'，并带有NOFORMAT，INIT，NAME = N'$ DBNameTEST'，跳过，NOREWIND，NOUNLOAD，STATS = 10，CHECKSUM-服务器$ Server -ConnectionTimeout 0 -QueryTimeout 65535 -ea stop
######################## ############################################### #################################
##检查恢复模式，如果为FULL，则检查日志-PrevDay.bak。如果存在，则删除。如果不存在，请继续##
##然后检查当前TX日志备份。如果存在，请重命名为Log-PreDay.bak。如果不存在，请继续##
##然后执行TX日志备份##
##如果恢复模式不完整，则不执行任何操作##
######### ############################################### ##############################################
IF
（$ Recovery.recovery_model_desc -eq'FULL'）
＃THEN＃
{
##查看是否存在PrevDay TX日志。如果是这样，请删除（如果不是），请删除##
IF
（测试路径$ Path\ $ DBName\ $ DBName-Log-PrevDay.bak）
＃THEN＃
{删除项目$ Path\ $ DBName\ $ DBName-Log-PrevDay.bak -force}
ELSE
{}
##查看当前TX日志存在，如果存在，则重命名为上一交易日发送日志，如果不存在，则继续运行##
IF
（测试路径$ Path\ $ DBName\ $ DBName-Log.bak）
＃THEN＃
{重命名项目$ Path\ $ DBName\ $ DBName-Log.bak-新名称$ DBName-Log-PrevDay.bak -force}
ELSE
{}
陷阱{$ _。Exception.Message; 1号出口；继续} Invoke-Sqlcmd-查询备份日志$ DBName到磁盘= N'$ Path\ $ DBName\ $ DBName-Log.bak'WITH NOFORMAT，INIT，NAME = N'$ DBName LogTEST（Init）'，跳过，NOREWIND，NOUNLOAD，STATS = 10，CHECKSUM-服务器$ Server -ConnectionTimeout 0 -QueryTimeout 65535 -ea stop}
ELSE
{}
基本上我添加了 trap {$ _。Exception.Message; 1号出口；继续} 在每个 Invoke-Sqlcmd 语句的前面，并结束每个 Invoke-Sqlcmd -ea stop 的语句。
陷阱$ _。Exception.Message 捕获任何错误...收集错误消息，然后退出1 立即退出，退出代码为 1 。
SQL作业使用0或1读取每个步骤，并自动将0解释为成功，将1解释为失败，并将SQL Job标记为成功或失败正确。另外，由于我捕获了实际的错误消息...它显示在SQL作业历史记录中。
这正是我所需要的。 ：）
如果您好奇的话，以下是对我帮助最大的博客：
I have a SQL 2005 instance that runs a job that uses a Powershell script to rename the current SQL TX Log backup file by appending "-PrevDay" to it, (subsequently deleting the backup already named "XXX-PrevDay.bak" if it exists), and then run a full backup of the DB and a TX Log backup, if the DB is not in Simple mode.
SQL Server Agent Job kicks off the Powershell script through CMD in each job step and the powershell script kicks off the sql backup using "Invoke-SQLCmd" cmdlet. This works great, unless the backup fails, because the SQL job still shows as "Successful". This is because the SQL job that kicks off the Powershell script through the CMD prompt, only cares if the Powershell script runs...not if the commands IN the script actually succeed or fail.
Is it possible, using error trapping in powershell (or any method really), to have the powershell script "fail" the cmd prompt action of running the script...so that the SQL Job reports a failure?
Does this even make sense? LOL
I would assume that if I was able to use SQL 2008, which allows for a SQL job step type of "Powershell Script" (instead of the step type having to be Operating System...that kicks off the PS script) this wouldn't be an issue...however...that's not an option.
Right now, the job step runs the powershell script, through CMD using Parameters for DBName, Path, and Servername and looks like this:
powershell.exe "C:\SQLBackupScriptsTest\SQLServerBackup.ps1" -DBName 'Angel_Food' -Path 'E:\SQLBackup1' -Server 'DEVSQLSRV'
The actual Powershell script looks like this:
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
{}
Ok, after looking at a few blogs and a little trial/error/luck...I got it to do what I want.
I decided I needed to send the Powershell exit code back to the CMDEXEC. However, from what I found, Powershell always defaults an exit code of 0 (success)...unless you jump through a few hoops invloving using 2 PS scripts...which I really didn't want to do. So I decided to just trap any error and if any error was trapped, have it exit the PS script with a code of 1, no matter what. Honestly...all I really wanted was a reliable exit code of 0 (success) or 1 (fail). So ...long story short...here's how I changed my code.
I changed the 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
{}
Basically I added trap {$_.Exception.Message; exit 1; continue} right in front of each Invoke-Sqlcmd statement and ended each Invoke-Sqlcmd statement with -ea stop.
The trap $_.Exception.Message traps any error... collects the error message, then exit 1 immediate exits the PS script with an exit code of 1.
The SQL job reads each step with either a 0 or 1 and automatically interprets 0 as success and 1 as failure and marks the SQL Job as a success or failure correctly. Plus, since I captured the actual error message...it shows up in the SQL job history.
This wound up being exactly what I need. :)
If you're curious...here's the blogs that helped me the most:
这篇关于如果通过PSD脚本执行Powershell脚本的SQL 2005作业显示失败，则PS脚本失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
