49 lines
1.5 KiB
PowerShell
49 lines
1.5 KiB
PowerShell
# ===================================================================
|
|
# SQL Agent Jobs Backup Script (PowerShell)
|
|
# Modern Version using SqlServer module
|
|
# & "E:\Neeraj\SqlAgent\v2\01_Prod\6_Backup\Backup-SQLAgent.ps1" `
|
|
# -ServerName "DOES-DUTAS-SQL" `
|
|
# -BackupPath "\\DOES-RAINVM-PRD\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
|