脚本概述
以下方案面向 SQL Server 环境，提供安全、可审计、可直接部署的备份与恢复脚本，满足以下目标：
- 备份频率：每小时执行差异备份（Differential），并支持每日执行全量备份（Full）
- 备份存储位置：使用网络共享 \nas01\backup\sqlserver\prod（UNC 路径）
- 安全性：使用证书加密备份（AES_256），不在脚本中存储明文密码；使用 Windows 身份验证
- 可用性：包含日志记录、错误处理、备份校验（RESTORE VERIFYONLY）与保留策略
- 恢复：支持根据最新全量+差异自动恢复到指定实例/目录
建议调度：
- 每日一次全量备份（例如 00:30）
- 每小时一次差异备份（整点执行，避开全量窗口）
备份脚本
下面为完整的 PowerShell 备份脚本。它依赖 SqlServer PowerShell 模块（Invoke-Sqlcmd），使用 Windows 身份验证连接 SQL Server。脚本将对所有在线、非只读的用户数据库执行备份，默认执行差异备份，可通过参数切换为全量备份。备份文件使用证书加密、压缩，并进行校验验证。
<#
.SYNOPSIS
SQL Server 安全备份脚本（全量/差异），包含加密、校验、日志与保留策略。
.DESCRIPTION
- 默认每小时执行差异备份；每日执行全量备份（建议单独调度）。
- 使用服务器证书加密（AES_256），不允许未加密备份，除非显式允许。
- 对所有在线、非只读的用户数据库执行备份，记录日志并清理过期备份。
- 备份完成后执行 RESTORE VERIFYONLY 校验。
.PARAMETERS
-Instance SQL Server 实例名（默认 localhost）
-BackupRoot 备份根目录（UNC 路径）
-BackupType Full 或 Differential（默认 Differential）
-RetentionDays 备份保留天数（默认 7）
-CertificateName 备份加密证书名称（默认 BackupCert）
-AllowUnencrypted 允许在证书缺失时执行未加密备份（默认不允许）
-CommandTimeoutSeconds T-SQL命令超时（秒，默认3600）
.NOTES
需要：PowerShell 模块 SqlServer；SQL Server 服务账户需对 UNC 路径有写权限。
#>
[CmdletBinding()]
param(
[string]$Instance = "localhost",
[Parameter(Mandatory=$true)]
[string]$BackupRoot,
[ValidateSet("Full","Differential")]
[string]$BackupType = "Differential",
[int]$RetentionDays = 7,
[ValidatePattern("^[A-Za-z0-9_]+$")]
[string]$CertificateName = "BackupCert",
[switch]$AllowUnencrypted,
[int]$CommandTimeoutSeconds = 3600
)
Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'
# 检查并导入 SqlServer 模块
if (-not (Get-Module -ListAvailable -Name SqlServer)) {
throw "缺少 SqlServer PowerShell 模块。请先安装：Install-Module -Name SqlServer -Scope AllUsers"
}
Import-Module SqlServer | Out-Null
# 日志目录与文件
$LogDir = Join-Path $env:ProgramData "SQLBackup\Logs"
if (-not (Test-Path $LogDir)) { New-Item -ItemType Directory -Path $LogDir | Out-Null }
$timestamp = (Get-Date).ToString("yyyyMMdd_HHmmss")
$LogFile = Join-Path $LogDir "Backup_${BackupType}_${timestamp}.log"
function Write-Log([string]$msg) {
$line = "[{0}] {1}" -f (Get-Date).ToString("yyyy-MM-dd HH:mm:ss"), $msg
$line | Tee-Object -FilePath $LogFile -Append
}
Write-Log "开始执行备份。实例: $Instance，类型: $BackupType，根目录: $BackupRoot"
# 路径检测（本机可达性）；注意：SQL Server 服务账户必须可写此路径
if (-not (Test-Path $BackupRoot)) {
throw "备份根目录不可达：$BackupRoot。请确认网络共享与权限。"
}
# 检查证书存在（用于备份加密）
$certExists = $false
try {
$certExists = [bool](Invoke-Sqlcmd -ServerInstance $Instance -Query @"
SELECT CASE WHEN EXISTS(SELECT 1 FROM master.sys.certificates WHERE name = N'$CertificateName') THEN 1 ELSE 0 END AS CertExists;
"@ -QueryTimeout $CommandTimeoutSeconds).CertExists
} catch {
throw "无法查询证书状态：$($_.Exception.Message)"
}
if (-not $certExists -and -not $AllowUnencrypted) {
throw "未发现备份加密证书 [$CertificateName]。为保障安全，脚本已拒绝执行未加密备份。请先创建证书（见‘使用指南’）。如需临时允许未加密备份，可添加 -AllowUnencrypted 开关。"
}
# 获取用户数据库列表
$databases = Invoke-Sqlcmd -ServerInstance $Instance -Query @"
SELECT name
FROM sys.databases
WHERE database_id > 4
AND state_desc = 'ONLINE'
AND is_read_only = 0;
"@ -QueryTimeout $CommandTimeoutSeconds | Select-Object -ExpandProperty name
if (-not $databases -or $databases.Count -eq 0) {
Write-Log "未发现可备份的用户数据库。脚本结束。"
exit 0
}
Write-Log ("将备份以下数据库：{0}" -f ($databases -join ", "))
foreach ($db in $databases) {
try {
$dbDir = Join-Path $BackupRoot $db
if (-not (Test-Path $dbDir)) { New-Item -ItemType Directory -Path $dbDir | Out-Null }
$fileStamp = (Get-Date).ToString("yyyyMMddHHmm")
$fileName = "{0}_{1}_{2}.bak" -f $db, $BackupType.ToUpper(), $fileStamp
$filePath = Join-Path $dbDir $fileName
Write-Log "开始备份数据库 [$db] 到 $filePath"
$encClause = ""
if ($certExists) {
$encClause = ", ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [$CertificateName])"
} elseif ($AllowUnencrypted) {
Write-Log "警告：证书不存在，按指示执行未加密备份。"
}
$diffClause = ($BackupType -eq "Differential") ? "DIFFERENTIAL, " : ""
$backupTsql = @"
BACKUP DATABASE [$db]
TO DISK = N'$filePath'
WITH INIT, $diffClause COMPRESSION, CHECKSUM, STATS = 10 $encClause;
"@
Invoke-Sqlcmd -ServerInstance $Instance -Query $backupTsql -QueryTimeout $CommandTimeoutSeconds | Out-Null
Write-Log "备份完成，开始校验：RESTORE VERIFYONLY [$db]"
$verifyTsql = "RESTORE VERIFYONLY FROM DISK = N'$filePath' WITH CHECKSUM;"
Invoke-Sqlcmd -ServerInstance $Instance -Query $verifyTsql -QueryTimeout $CommandTimeoutSeconds | Out-Null
Write-Log "校验成功：[$db] -> $fileName"
# 备份保留清理
$cutoff = (Get-Date).AddDays(-$RetentionDays)
Get-ChildItem -Path $dbDir -Filter "*.bak" -File |
Where-Object { $_.LastWriteTime -lt $cutoff } |
ForEach-Object {
Write-Log "清理过期备份文件：$($_.FullName)"
Remove-Item -Path $_.FullName -Force -ErrorAction Stop
}
} catch {
Write-Log "错误：备份数据库 [$db] 失败。原因：$($_.Exception.Message)"
# 不中断其它库备份，但记录非零退出码
}
}
Write-Log "备份任务执行完毕。日志文件：$LogFile"
恢复脚本
以下 PowerShell 脚本自动选择指定数据库的“最新全量备份 + 最新差异备份”组合进行恢复。支持将数据文件/日志文件移动到指定目录、校验、替换现有数据库等。
<#
.SYNOPSIS
SQL Server 安全恢复脚本：从指定目录自动选择最新 FULL + DIFF 进行恢复。
.PARAMETERS
-Instance 目标 SQL Server 实例名（默认 localhost）
-BackupRoot 备份根目录（UNC 路径）
-Database 需要恢复的源数据库名称（对应备份子目录名）
-TargetDatabase 恢复到的目标数据库名称（默认与源相同）
-DataDir 数据文件(MDF/NDF)目标目录（可选）
-LogDir 日志文件(LDF)目标目录（可选）
-WithReplace 若目标数据库已存在，允许覆盖
-VerifyOnly 仅校验备份可用性，不实际恢复
-CommandTimeoutSeconds T-SQL命令超时（秒，默认7200）
.NOTES
仅适用于 FULL + DIFF 备份链；未包含日志备份的点时间恢复。
#>
[CmdletBinding()]
param(
[string]$Instance = "localhost",
[Parameter(Mandatory=$true)]
[string]$BackupRoot,
[Parameter(Mandatory=$true)]
[ValidatePattern("^[A-Za-z0-9_\-]+$")]
[string]$Database,
[ValidatePattern("^[A-Za-z0-9_\-]+$")]
[string]$TargetDatabase,
[string]$DataDir,
[string]$LogDir,
[switch]$WithReplace,
[switch]$VerifyOnly,
[int]$CommandTimeoutSeconds = 7200
)
Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'
if (-not (Get-Module -ListAvailable -Name SqlServer)) {
throw "缺少 SqlServer PowerShell 模块。请先安装：Install-Module -Name SqlServer -Scope AllUsers"
}
Import-Module SqlServer | Out-Null
if (-not $TargetDatabase) { $TargetDatabase = $Database }
$srcDir = Join-Path $BackupRoot $Database
if (-not (Test-Path $srcDir)) { throw "未找到备份目录：$srcDir" }
# 找到最新 FULL 与 DIFF
$full = Get-ChildItem -Path $srcDir -Filter "${Database}_FULL_*.bak" -File | Sort-Object LastWriteTime -Descending | Select-Object -First 1
if (-not $full) { throw "未找到全量备份文件：${Database}_FULL_*.bak" }
$fullTime = $full.LastWriteTime
$diff = Get-ChildItem -Path $srcDir -Filter "${Database}_DIFFERENTIAL_*.bak" -File |
Where-Object { $_.LastWriteTime -ge $fullTime } |
Sort-Object LastWriteTime -Descending | Select-Object -First 1
if ($VerifyOnly) {
Write-Host "校验备份文件有效性（VERIFYONLY）..."
Invoke-Sqlcmd -ServerInstance $Instance -Query "RESTORE VERIFYONLY FROM DISK = N'$($full.FullName)' WITH CHECKSUM;" -QueryTimeout $CommandTimeoutSeconds | Out-Null
if ($diff) {
Invoke-Sqlcmd -ServerInstance $Instance -Query "RESTORE VERIFYONLY FROM DISK = N'$($diff.FullName)' WITH CHECKSUM;" -QueryTimeout $CommandTimeoutSeconds | Out-Null
}
Write-Host "校验成功。FULL: $($full.Name) $(if ($diff) {"; DIFF: $($diff.Name)"} else {""})"
exit 0
}
# 获取逻辑文件名与构建 MOVE 子句（若指定了目标路径）
$files = Invoke-Sqlcmd -ServerInstance $Instance -Query "RESTORE FILELISTONLY FROM DISK = N'$($full.FullName)';" -QueryTimeout $CommandTimeoutSeconds
if (-not $files) { throw "无法读取备份文件逻辑文件列表：$($full.FullName)" }
function Build-MoveClauses {
param([System.Data.DataRow[]]$fileRows, [string]$dataDir, [string]$logDir)
$clauses = @()
foreach ($row in $fileRows) {
$logical = $row.LogicalName
$type = $row.Type # D or L
$origPhys = $row.PhysicalName
$targetPath = $origPhys
if ($type -eq 'D' -and $dataDir) {
$baseName = Split-Path $origPhys -Leaf
$targetPath = Join-Path $dataDir $baseName
} elseif ($type -eq 'L' -and $logDir) {
$baseName = Split-Path $origPhys -Leaf
$targetPath = Join-Path $logDir $baseName
}
$clauses += "MOVE N'$logical' TO N'$targetPath'"
}
return $clauses -join ", "
}
$moveClause = Build-MoveClauses -fileRows $files -dataDir $DataDir -logDir $LogDir
$replaceClause = $WithReplace.IsPresent ? ", REPLACE" : ""
# 若目标数据库已存在且未指定替换，则提示
$exists = [bool](Invoke-Sqlcmd -ServerInstance $Instance -Query "SELECT CASE WHEN DB_ID(N'$TargetDatabase') IS NULL THEN 0 ELSE 1 END AS ExistsDB;" -QueryTimeout $CommandTimeoutSeconds).ExistsDB
if ($exists -and -not $WithReplace) {
throw "目标数据库 [$TargetDatabase] 已存在。若需覆盖，请添加 -WithReplace"
}
# 执行恢复：先 FULL(NOREDOVERY)，后 DIFF(RECOVERY)
$restoreFull = @"
RESTORE DATABASE [$TargetDatabase]
FROM DISK = N'$($full.FullName)'
WITH NORECOVERY, CHECKSUM, STATS = 10 $replaceClause $(if ($moveClause) {", $moveClause"} else {""});
"@
Invoke-Sqlcmd -ServerInstance $Instance -Query $restoreFull -QueryTimeout $CommandTimeoutSeconds | Out-Null
if ($diff) {
$restoreDiff = @"
RESTORE DATABASE [$TargetDatabase]
FROM DISK = N'$($diff.FullName)'
WITH RECOVERY, CHECKSUM, STATS = 10;
"@
Invoke-Sqlcmd -ServerInstance $Instance -Query $restoreDiff -QueryTimeout $CommandTimeoutSeconds | Out-Null
Write-Host "恢复完成：FULL + DIFF 已应用。目标数据库：$TargetDatabase"
} else {
# 没有差异则直接 RECOVERY
$recover = "RESTORE DATABASE [$TargetDatabase] WITH RECOVERY;"
Invoke-Sqlcmd -ServerInstance $Instance -Query $recover -QueryTimeout $CommandTimeoutSeconds | Out-Null
Write-Host "恢复完成：仅全量备份。目标数据库：$TargetDatabase"
}
配置参数
- Instance：SQL Server 实例名（如 hostname\INSTANCE，默认 localhost）
- BackupRoot：备份根目录，使用 UNC 路径；本例为 \nas01\backup\sqlserver\prod
- BackupType：Full 或 Differential；推荐每日 Full，每小时 Differential
- RetentionDays：备份保留天数（默认 7），脚本会自动清理过期 .bak 文件
- CertificateName：用于备份加密的服务器证书名称（默认 BackupCert）
- AllowUnencrypted：若证书缺失，是否允许未加密备份；默认不允许（安全优先）
- CommandTimeoutSeconds：SQL 命令超时时间（备份默认 3600 秒；恢复默认 7200 秒）
- DataDir / LogDir（恢复脚本）：指定恢复后数据/日志文件的存放目录
使用指南
-
准备工作
- 确保 SQL Server 服务账户（运行数据库引擎的账户）对 \nas01\backup\sqlserver\prod 具有写权限（共享与 NTFS 权限）。
- 安装 PowerShell 模块：以管理员运行 PowerShell 执行 Install-Module -Name SqlServer -Scope AllUsers
- 在 SQL Server 上创建备份加密证书（一次性操作，避免明文密码写入脚本）：
- 在 master 数据库中执行（请在 SSMS 交互式执行并使用强密码，勿写入脚本/文件）：
- 创建数据库主密钥（DMK）
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '请使用强密码';
- 创建证书（示例名 BackupCert）
CREATE CERTIFICATE [BackupCert] WITH SUBJECT = 'Backup Encryption Certificate';
- 备份证书与私钥（妥善保存，灾难恢复时需要）
BACKUP CERTIFICATE [BackupCert] TO FILE = 'C:\Secure\BackupCert.cer'
WITH PRIVATE KEY ( FILE = 'C:\Secure\BackupCert.pvk', ENCRYPTION BY PASSWORD = '请使用强密码' );
- 将证书备份保存在安全位置并纳入合规管理。
- 创建本地日志目录（脚本会自动创建）：C:\ProgramData\SQLBackup\Logs
-
首次执行（手工测试）
- 全量备份（建议每日一次）：
powershell -File .\Backup-SqlServer.ps1 -Instance "localhost" -BackupRoot "\nas01\backup\sqlserver\prod" -BackupType Full
- 差异备份（每小时）：
powershell -File .\Backup-SqlServer.ps1 -Instance "localhost" -BackupRoot "\nas01\backup\sqlserver\prod" -BackupType Differential
- 查看日志文件：C:\ProgramData\SQLBackup\Logs\Backup_Differential_yyyyMMdd_HHmmss.log
-
调度建议
- 使用 SQL Server Agent 或 Windows 任务计划程序：
- 每日 00:30 调度全量：
powershell -File "C:\Ops\Backup-SqlServer.ps1" -Instance "hostname\PROD" -BackupRoot "\nas01\backup\sqlserver\prod" -BackupType Full -RetentionDays 14
- 每小时整点调度差异（避开 00:30 的全量窗口）：
powershell -File "C:\Ops\Backup-SqlServer.ps1" -Instance "hostname\PROD" -BackupRoot "\nas01\backup\sqlserver\prod" -BackupType Differential -RetentionDays 7
-
恢复操作（演练与生产）
- 仅校验备份可用性（不实际恢复）：
powershell -File .\Restore-SqlServer.ps1 -Instance "localhost" -BackupRoot "\nas01\backup\sqlserver\prod" -Database "YourDB" -VerifyOnly
- 恢复到同名数据库（覆盖）：
powershell -File .\Restore-SqlServer.ps1 -Instance "localhost" -BackupRoot "\nas01\backup\sqlserver\prod" -Database "YourDB" -WithReplace
- 恢复到不同名数据库并指定文件目录：
powershell -File .\Restore-SqlServer.ps1 -Instance "localhost" -BackupRoot "\nas01\backup\sqlserver\prod" -Database "YourDB" -TargetDatabase "YourDB_Restore" -DataDir "D:\SQLData" -LogDir "E:\SQLLogs"
-
备份验证与审计
- 通过 SQL 查询 msdb 备份历史：
SELECT TOP 20 database_name, backup_start_date, backup_finish_date, type, backup_size
FROM msdb.dbo.backupset
ORDER BY backup_finish_date DESC;
- 校验文件完整性：
RESTORE VERIFYONLY FROM DISK = N'\nas01\backup\sqlserver\prod\YourDB\YourDB_FULL_YYYYMMDDHHmm.bak' WITH CHECKSUM;
故障排查
注意：如需更低 RPO（小于 1 小时），请增加事务日志备份频率（例如每 15 分钟），并在恢复流程中应用日志备份；这超出当前“每小时差异备份”的范围，可按需扩展脚本。
