SQL Server数据库备份脚本
SQL Server数据库自动备份脚本，支持全量/差异/日志备份，自动压缩、清理旧备份、备份验证
详细内容
<#
.SYNOPSIS
SQL Server数据库自动备份脚本
.DESCRIPTION
支持全量/差异/日志备份，自动压缩，清理旧备份
.USAGE
.\sqlserver_backup.ps1 -BackupType Full
#>
param(
[string]$SqlInstance = "localhost",
[string]$Database = "",
[ValidateSet("Full", "Differential", "Log")]
[string]$BackupType = "Full",
[string]$BackupDir = "C:\Backup\SQLServer",
[int]$KeepDays = 7,
[switch]$Compress,
[switch]$Verify
)
$ErrorActionPreference = "Stop"
Write-Host "==========================================" -ForegroundColor Cyan
Write-Host " SQL Server数据库备份" -ForegroundColor Cyan
Write-Host " 实例: $SqlInstance, 类型: $BackupType" -ForegroundColor Cyan
Write-Host "==========================================" -ForegroundColor Cyan
if (-not (Test-Path $BackupDir)) {
New-Item -ItemType Directory -Path $BackupDir -Force | Out-Null
}
# 加载SQL Server模块
try {
Import-Module SqlServer -ErrorAction Stop
} catch {
try {
Import-Module SQLPS -ErrorAction Stop -DisableNameChecking
} catch {
Write-Host "❌ 未找到SQL Server PowerShell模块" -ForegroundColor Red
exit 1
}
}
# 获取数据库列表
if ($Database) {
$databases = @($Database)
} else {
$query = "SELECT name FROM sys.databases WHERE database_id > 4 AND state = 0"
$databases = Invoke-SqlCmd -ServerInstance $SqlInstance -Query $query | Select-Object -ExpandProperty name
Write-Host "发现 $($databases.Count) 个用户数据库"
}
$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$successCount = 0
$failCount = 0
foreach ($db in $databases) {
Write-Host "`n--- 备份: $db ---" -ForegroundColor Yellow
$backupFile = Join-Path $BackupDir "${db}_${BackupType}_${timestamp}.bak"
try {
$backupParams = @{
ServerInstance = $SqlInstance
Database = $db
BackupFile = $backupFile
BackupAction = if ($BackupType -eq "Log") { "Log" } else { "Database" }
Initialize = $true
}
if ($BackupType -eq "Differential") { $backupParams["Incremental"] = $true }
if ($Compress) { $backupParams["CompressionOption"] = "On" }
Backup-SqlDatabase @backupParams
if (Test-Path $backupFile) {
$sizeMB = [math]::Round((Get-Item $backupFile).Length / 1MB, 2)
Write-Host " ✓ 备份成功 ($sizeMB MB)" -ForegroundColor Green
if ($Verify) {
$verifyQuery = "RESTORE VERIFYONLY FROM DISK = '$backupFile'"
Invoke-SqlCmd -ServerInstance $SqlInstance -Query $verifyQuery | Out-Null
Write-Host " ✓ 验证通过" -ForegroundColor Green
}
$successCount++
}
} catch {
Write-Host " ✗ 备份失败: $($_.Exception.Message)" -ForegroundColor Red
$failCount++
}
}
# 清理旧备份
Write-Host "`n【清理旧备份】" -ForegroundColor Yellow
$cutoff = (Get-Date).AddDays(-$KeepDays)
$old = Get-ChildItem $BackupDir -Filter "*.bak" | Where-Object { $_.LastWriteTime -lt $cutoff }
if ($old) {
Write-Host "删除 $($old.Count) 个旧备份"
$old | Remove-Item -Force
}
Write-Host "`n==========================================" -ForegroundColor Cyan
Write-Host " 完成: 成功$successCount 失败$failCount" -ForegroundColor Cyan
Write-Host "==========================================" -ForegroundColor Cyan
适配环境
适配系统：Windows 10+,Windows Server 2016+
依赖环境：Windows PowerShell 5.1+
参数说明
[{"name": "SqlInstance", "label": "SQL Server\u5b9e\u4f8b", "default": "localhost"}, {"name": "Database", "label": "\u6570\u636e\u5e93\u540d(\u7559\u7a7a\u6240\u6709\u7528\u6237\u5e93)", "default": ""}, {"name": "BackupType", "label": "\u5907\u4efd\u7c7b\u578b(Full/Differential/Log)", "default": "Full"}, {"name": "BackupDir", "label": "\u5907\u4efd\u76ee\u5f55", "default": "C:\\Backup\\SQLServer"}, {"name": "KeepDays", "label": "\u4fdd\u7559\u5929\u6570", "default": "7"}, {"name": "Compress", "label": "\u662f\u5426\u538b\u7f29(true/false)", "default": "true"}, {"name": "Verify", "label": "\u662f\u5426\u9a8c\u8bc1\u5907\u4efd(true/false)", "default": "true"}]