SQL Server 2008 Job Agent 執行記錄匯出工具

- 4月 06, 2025

(1)建立專用的低權限SQL帳號 - 可以建立一個只有讀取Job Agent記錄權限的專用SQL帳號，而非使用高權限的sa帳號。

建立SQL登入帳號：

USE [master]

CREATE LOGIN [JobLogReader] WITH PASSWORD=N'複雜密碼', DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

在msdb資料庫中建立使用者

USE [msdb]

CREATE USER [JobLogReader] FOR LOGIN [JobLogReader]

授予最小權限

USE [msdb]

-- 授予讀取sysjobs和sysjobhistory表的權限

GRANT SELECT ON sysjobs TO [JobLogReader]

GRANT SELECT ON sysjobhistory TO [JobLogReader]

GRANT SELECT ON sysjobsteps TO [JobLogReader]

(2)

<#
.SYNOPSIS
    撈取SQL Server 2008 Job Agent執行狀況的LOG並匯出為.log檔案

.DESCRIPTION
    此腳本用於從SQL Server 2008撈取Job Agent的執行記錄，並使用BCP命令將結果匯出為.log檔案
    匯出的檔案格式與SQL Server Agent圖形介面匯出的格式相容，可再次匯入
    腳本使用安全的方式處理SQL Server連線憑證，避免明碼存儲

.PARAMETER ServerInstance
    SQL Server的實例名稱，例如："SERVERNAME\INSTANCENAME"

.PARAMETER Database
    包含Job Agent記錄的資料庫名稱，通常為"msdb"

.PARAMETER Days
    要撈取的天數範圍，例如：7表示撈取最近7天的記錄

.PARAMETER OutputPath
    匯出的.log檔案路徑

.EXAMPLE
    .\Export-JobAgentLog.ps1 -ServerInstance "SQLSERVER01\SQL2008" -Database "msdb" -Days 7 -OutputPath "C:\Logs\JobAgentLog.log"

.NOTES
    作者: AI助手
    日期: 2023-11-01
    版本: 1.0
    需求: Windows Server 2008, SQL Server 2008, PowerShell 2.0+
#>

[CmdletBinding()]
param (
    [Parameter(Mandatory = $true)]
    [string]$ServerInstance,
    
    [Parameter(Mandatory = $true)]
    [string]$Database = "msdb",
    
    [Parameter(Mandatory = $true)]
    [int]$Days = 7,
    
    [Parameter(Mandatory = $true)]
    [string]$OutputPath
)

function Get-SecureCredentials {
    <#
    .SYNOPSIS
        安全地獲取SQL Server連線憑證
    .DESCRIPTION
        此函數嘗試從加密的憑證檔案讀取憑證，如果檔案不存在則提示使用者輸入並儲存
    #>
    param (
        [Parameter(Mandatory = $false)]
        [switch]$UseStoredCredential = $true,
        
        [Parameter(Mandatory = $false)]
        [string]$CredentialPath = "$env:USERPROFILE\Documents\SQLServerCredential.xml"
    )
    
    $credential = $null
    
    try {
        # 檢查是否使用儲存的憑證
        if ($UseStoredCredential -and (Test-Path -Path $CredentialPath)) {
            Write-Verbose "正在從檔案讀取儲存的憑證..."
            $credential = Import-Clixml -Path $CredentialPath
            Write-Verbose "已成功讀取儲存的憑證。"
        }
        else {
            # 提示使用者輸入SQL Server的使用者名稱和密碼
            $credential = Get-Credential -Message "請輸入SQL Server的登入憑證" -UserName "JobLogReader"
            
            if ($null -eq $credential) {
                Write-Error "未提供有效的憑證，程序終止。"
                exit 1
            }
            
            # 詢問是否儲存憑證
            $saveCredential = Read-Host "是否要儲存此憑證以便自動執行？(Y/N)"
            
            if ($saveCredential -eq "Y" -or $saveCredential -eq "y") {
                # 確保目錄存在
                $credentialDir = Split-Path -Path $CredentialPath -Parent
                if (-not (Test-Path -Path $credentialDir)) {
                    New-Item -Path $credentialDir -ItemType Directory -Force | Out-Null
                }
                
                # 將憑證加密儲存到檔案
                $credential | Export-Clixml -Path $CredentialPath -Force
                Write-Host "憑證已安全地儲存到 $CredentialPath" -ForegroundColor Green
                Write-Host "下次執行時將自動使用此憑證。" -ForegroundColor Green
            }
        }
    }
    catch {
        Write-Error "獲取憑證時發生錯誤: $_"
        exit 1
    }
    
    return $credential
}

function Test-SQLConnection {
    <#
    .SYNOPSIS
        測試SQL Server連線
    .DESCRIPTION
        此函數測試與SQL Server的連線是否成功
    #>
    param (
        [string]$ServerInstance,
        [string]$Database,
        [System.Management.Automation.PSCredential]$Credential
    )
    
    try {
        $username = $Credential.UserName
        $password = $Credential.GetNetworkCredential().Password
        
        # 建立連線字串
        $connectionString = "Server=$ServerInstance;Database=$Database;User Id=$username;Password=$password;"
        
        # 建立SQL連線
        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
        $connection.Open()
        
        # 如果能夠開啟連線，則關閉連線並返回成功
        $connection.Close()
        return $true
    }
    catch {
        Write-Error "SQL連線測試失敗: $_"
        return $false
    }
}

function Get-JobAgentLogQuery {
    <#
    .SYNOPSIS
        生成用於撈取Job Agent LOG的SQL查詢
    .DESCRIPTION
        此函數根據指定的天數範圍生成SQL查詢，用於撈取Job Agent的執行記錄
    #>
    param (
        [int]$Days
    )
    
    # 生成SQL查詢，撈取指定天數範圍內的Job Agent執行記錄
    # 此查詢格式確保匯出的記錄可以再次匯入到SQL Server Agent
    $query = @"
SELECT
    j.name AS [作業名稱],
    h.step_id AS [步驟識別碼],
    h.step_name AS [步驟名稱],
    CASE h.run_status
        WHEN 0 THEN N'失敗'
        WHEN 1 THEN N'成功'
        WHEN 2 THEN N'重試'
        WHEN 3 THEN N'取消'
        WHEN 4 THEN N'進行中'
        ELSE N'未知'
    END AS [執行狀態],
    h.message AS [訊息],
    h.run_date AS [執行日期],
    h.run_time AS [執行時間],
    h.run_duration AS [執行持續時間],
    h.operator_id_emailed AS [已寄電子郵件給操作員識別碼],
    h.operator_id_netsent AS [已傳送網路訊息給操作員識別碼],
    h.operator_id_paged AS [已呼叫操作員識別碼],
    h.retries_attempted AS [嘗試重試次數],
    h.server AS [伺服器]
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
WHERE CONVERT(datetime, STUFF(STUFF(CAST(h.run_date AS varchar), 7, 0, '-'), 5, 0, '-')) >= DATEADD(day, -$Days, GETDATE())
ORDER BY h.run_date DESC, h.run_time DESC
"@
    
    return $query
}

function Export-JobAgentLog {
    <#
    .SYNOPSIS
        使用BCP命令匯出Job Agent LOG
    .DESCRIPTION
        此函數使用BCP命令將SQL查詢結果匯出為.log檔案
    #>
    param (
        [string]$ServerInstance,
        [string]$Database,
        [System.Management.Automation.PSCredential]$Credential,
        [string]$Query,
        [string]$OutputPath
    )
    
    try {
        $username = $Credential.UserName
        $password = $Credential.GetNetworkCredential().Password
        
        # 將查詢儲存到臨時檔案
        $tempQueryFile = [System.IO.Path]::GetTempFileName()
        $Query | Out-File -FilePath $tempQueryFile -Encoding UTF8
        
        Write-Host "正在匯出Job Agent執行記錄到 $OutputPath..." -ForegroundColor Cyan
        
        # 使用BCP命令匯出查詢結果
        # 注意：這裡使用-c參數表示字元格式，確保匯出的格式與SQL Server Agent圖形介面匯出的格式相容
        $bcpCommand = "bcp \"$Query\" queryout \"$OutputPath\" -c -t\"\t\" -r\"\n\" -S\"$ServerInstance\" -U\"$username\" -P\"$password\" -d\"$Database\""
        
        # 使用Invoke-Expression執行BCP命令
        # 注意：這裡不直接顯示BCP命令，以避免在日誌中顯示密碼
        Write-Verbose "執行BCP命令匯出資料..."
        Invoke-Expression $bcpCommand | Out-Null
        
        # 刪除臨時查詢檔案
        if (Test-Path $tempQueryFile) {
            Remove-Item -Path $tempQueryFile -Force
        }
        
        # 檢查匯出檔案是否存在
        if (Test-Path $OutputPath) {
            $fileInfo = Get-Item $OutputPath
            Write-Host "匯出完成！檔案大小: $([math]::Round($fileInfo.Length / 1KB, 2)) KB" -ForegroundColor Green
            Write-Host "檔案路徑: $OutputPath" -ForegroundColor Green
        }
        else {
            Write-Error "匯出失敗，無法找到輸出檔案。"
            return $false
        }
        
        return $true
    }
    catch {
        Write-Error "匯出Job Agent LOG時發生錯誤: $_"
        return $false
    }
}

# 新增參數，用於控制是否使用儲存的憑證
[CmdletBinding()]
param (
    [Parameter(Mandatory = $true)]
    [string]$ServerInstance,
    
    [Parameter(Mandatory = $true)]
    [string]$Database = "msdb",
    
    [Parameter(Mandatory = $true)]
    [int]$Days = 7,
    
    [Parameter(Mandatory = $true)]
    [string]$OutputPath,
    
    [Parameter(Mandatory = $false)]
    [switch]$UseStoredCredential = $true,
    
    [Parameter(Mandatory = $false)]
    [string]$CredentialPath = "$env:USERPROFILE\Documents\SQLServerCredential.xml"
)

# 主程序
try {
    # 檢查輸出路徑的目錄是否存在，如果不存在則創建
    $outputDir = Split-Path -Path $OutputPath -Parent
    if (-not (Test-Path -Path $outputDir)) {
        New-Item -Path $outputDir -ItemType Directory -Force | Out-Null
        Write-Host "已創建輸出目錄: $outputDir" -ForegroundColor Yellow
    }
    
    # 獲取安全的SQL Server連線憑證
    if ($UseStoredCredential) {
        Write-Host "嘗試使用儲存的SQL Server憑證..." -ForegroundColor Cyan
    } else {
        Write-Host "請提供SQL Server的登入憑證..." -ForegroundColor Cyan
    }
    
    $credential = Get-SecureCredentials -UseStoredCredential $UseStoredCredential -CredentialPath $CredentialPath
    
    # 測試SQL Server連線
    Write-Host "正在測試SQL Server連線..." -ForegroundColor Cyan
    $connectionTest = Test-SQLConnection -ServerInstance $ServerInstance -Database $Database -Credential $credential
    
    if (-not $connectionTest) {
        Write-Error "無法連線到SQL Server，請檢查連線參數和憑證。"
        exit 1
    }
    
    Write-Host "SQL Server連線成功！" -ForegroundColor Green
    
    # 生成SQL查詢
    Write-Host "正在生成SQL查詢，撈取最近 $Days 天的Job Agent執行記錄..." -ForegroundColor Cyan
    $query = Get-JobAgentLogQuery -Days $Days
    
    # 匯出Job Agent LOG
    $exportResult = Export-JobAgentLog -ServerInstance $ServerInstance -Database $Database -Credential $credential -Query $query -OutputPath $OutputPath
    
    if ($exportResult) {
        Write-Host "操作完成！Job Agent執行記錄已成功匯出。" -ForegroundColor Green
        Write-Host "此檔案可以透過SQL Server Agent圖形介面匯入。" -ForegroundColor Green
    }
    else {
        Write-Error "匯出Job Agent執行記錄失敗。"
        exit 1
    }
}
catch {
    Write-Error "執行腳本時發生錯誤: $_"
    exit 1
}

目前PowerShell腳本中使用DPAPI的部分是在儲存憑證時，透過`ConvertFrom-SecureString` 和`ConvertTo-SecureString` 命令，這些命令內部使用Windows的DPAPI來加密和解密密碼。

如果使用了`-UseStoredCredential $true` 參數並事先儲存了憑證，腳本就不需要每次都輸入憑證。這種方式適合排程自動化，因為它可以在無人值守的情況下執行。

但請注意，儲存的憑證只能被建立它的Windows使用者帳戶解密，所以排程任務必須使用相同的使用者帳戶執行。