通过 CMD 提示符执行 Powershell 脚本的 SQL 2005 作业显示失败，如果 PS 脚本失败

Question

我有一个 SQL 2005 实例，它运行一个作业，该作业使用 Powershell 脚本通过附加“-PrevDay”来重命名当前的 SQL TX Log 备份文件（随后删除已命名为“XXX-PrevDay.bak”的备份（如果存在） ），然后运行数据库的完整备份和 TX 日志备份（如果数据库不处于简单模式）。

SQL Server 代理作业在每个作业步骤中通过 CMD 启动 Powershell 脚本，并且 powershell 脚本使用“Invoke-SQLCmd”cmdlet 启动 sql 备份。这非常有效，除非备份失败，因为 SQL 作业仍然显示为“成功”。这是因为通过 CMD 提示符启动 Powershell 脚本的 SQL 作业只关心 Powershell 脚本是否运行……而不关心脚本中的命令是否实际成功或失败。

是否有可能使用 powershell 中的错误捕获（或实际上的任何方法）使 powershell 脚本“失败”运行脚本的 cmd 提示操作...以便 SQL 作业报告失败？

这还有道理吗？哈哈，

我假设如果我能够使用 SQL 2008，它允许 SQL 作业步骤类型为“Powershell 脚本”（而不是步骤类型必须是操作系统...启动 PS 脚本），这将这不是问题……但是……这不是一个选择。

现在，作业步骤通过 CMD 使用 DBName、Path 和 Servername 参数运行 powershell 脚本，如下所示：

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

实际的 Powershell 脚本如下所示：

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

好吧，在看了一些博客和一点尝试/错误/运气之后......我让它做我想做的事。

我决定需要将 Powershell 退出代码发送回 CMDEXEC。然而，根据我的发现，Powershell 总是默认退出代码为 0（成功）...除非你跳过一些涉及使用 2 个 PS 脚本的麻烦...我真的不想这样做。因此，我决定捕获任何错误，如果捕获了任何错误，则无论如何都将其以代码 1 退出 PS 脚本。老实说...我真正想要的只是一个可靠的退出代码 0（成功）或 1（失败）。所以......长话短说......这就是我如何更改代码。

我将每个步骤的 CMDEXEC 更改为：

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

然后我将 PS 脚本更改为：

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
    {}

基本上我添加了 trap {$_.Exception.Message; 1号出口； continue} 位于每个 Invoke-Sqlcmd 语句前面，并以 -ea stop 结束每个 Invoke-Sqlcmd 语句。

trap $_.Exception.Message 捕获任何错误...收集错误消息，然后 exit 1 立即退出 PS 脚本，退出代码为 1。

SQL 作业读取带有 0 或 1 的每个步骤，并自动将 0 解释为成功，将 1 解释为失败，并正确地将 SQL 作业标记为成功或失败。另外，由于我捕获了实际的错误消息...它显示在 SQL 作业历史记录中。

这正是我所需要的。 :)

如果你很好奇......这里是对我帮助最大的博客：

• 从 PowerShell 脚本 (weblogs.asp.net) 返回退出代码
• 捕获错误 (Powershell.com)