# ===================================================================
# SQL Agent Jobs Backup Script (PowerShell)
# Modern Version using SqlServer module
# & "E:\Neeraj\SqlAgent\v2\01_Devl\6_Backup\Backup-SQLAgent.ps1" `
#   -ServerName "DOES-DUTAS-SQL1" `
#   -BackupPath "\\DOES-RAINVM-DEV\E$\Neeraj\SqlAgent\Backup\"
#
# ===================================================================

param (
    [Parameter(Mandatory = $true)]
    [string]$ServerName,

    [Parameter(Mandatory = $true)]
    [string]$BackupPath
)

# Load SQL Server PowerShell module
Import-Module SqlServer -ErrorAction Stop

# Create timestamp and output paths
$Timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$ZipFile = Join-Path $BackupPath "SQLAgentJobs_$Timestamp.zip"

# Ensure backup folder exists
if (-not (Test-Path $BackupPath)) {
    New-Item -ItemType Directory -Path $BackupPath | Out-Null
}

# Connect to SQL Server
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $ServerName

# Export all jobs as individual SQL scripts
$JobFiles = @()
foreach ($job in $server.JobServer.Jobs) {
    $SafeJobName = ($job.Name -replace '[\\/:*?"<>|]', '_')
    $FileName = Join-Path $BackupPath "$SafeJobName`_$Timestamp.sql"
    $job.Script() | Out-File -FilePath $FileName -Encoding UTF8
    $JobFiles += $FileName
}

# Compress all SQL files into a single ZIP
Compress-Archive -Path $JobFiles -DestinationPath $ZipFile -Force

# Optional: clean up individual .sql files
# Remove-Item -Path $JobFiles

Write-Host "SQL Agent jobs backup complete: $ZipFile" -ForegroundColor Green