如何自动将SQL数据库备份为.sql脚本文件

针对你需要每日自动生成Azure SQL数据库的.sql脚本的需求，以下是两种实用的自动化方案：

方案1：PowerShell + SqlServer模块 #

这是最灵活的方式，能完全复刻SSMS手动生成脚本的逻辑，支持自定义脚本选项。

步骤1：安装SqlServer模块 #

打开PowerShell（管理员权限）执行：

Install-Module -Name SqlServer -Force

步骤2：编写自动化脚本 #

创建Export-AzureSqlDailyScript.ps1文件，填入以下内容并替换你的配置：

# 配置参数
$serverName = "你的Azure SQL服务器名.database.windows.net"
$databaseName = "目标数据库名"
$outputDir = "C:\SQLBackups"
$retentionDays = 7  # 保留7天内的脚本

# 创建输出目录（如果不存在）
if (-not (Test-Path $outputDir)) { New-Item -Path $outputDir -ItemType Directory | Out-Null }

# 生成带日期的输出文件名
$outputPath = Join-Path $outputDir "DailyBackup_$(Get-Date -Format 'yyyyMMdd').sql"

# 配置数据库认证（推荐用Azure AD或加密存储密码，避免明文）
$username = "SQL登录名"
$password = ConvertTo-SecureString "SQL密码" -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ($username, $password)

# 设置脚本生成选项（和SSMS手动操作的选项对应）
$scriptOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$scriptOptions.ScriptData = $true          # 包含数据
$scriptOptions.ScriptSchema = $true        # 包含架构
$scriptOptions.IncludeIfNotExists = $true  # 生成IF NOT EXISTS语句
$scriptOptions.ScriptDrops = $false        # 不生成DROP语句
$scriptOptions.Encoding = [System.Text.Encoding]::UTF8

# 连接数据库并生成脚本
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$server.ConnectionContext.Credential = $credential
$database = $server.Databases[$databaseName]

# 导出所有对象的脚本到文件
$database.Script($scriptOptions) | Out-File -FilePath $outputPath -Encoding utf8

# 清理过期脚本
Get-ChildItem $outputDir -Filter "*.sql" | Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$retentionDays) } | Remove-Item -Force

步骤3：设置Windows任务计划 #

1. 打开「任务计划程序」，创建基本任务
2. 触发条件设置为「每天」，选择你需要的早上时间
3. 操作选择「启动程序」，程序/脚本填powershell.exe，添加参数：

   -ExecutionPolicy Bypass -File "C:\路径\到\Export-AzureSqlDailyScript.ps1"
   

4. 配置任务运行的账户（需有数据库读取权限和输出目录的写入权限）

方案2：SQLCMD + SSMS模板脚本 #

如果你已经有手动生成的脚本模板，可以用SQLCMD变量替换配合批处理实现自动化。

步骤1：生成SQLCMD模板 #

在SSMS手动生成脚本时，勾选「SQLCMD模式」，将动态内容替换为变量（比如$(BackupDate)），保存为BackupTemplate.sql。

步骤2：编写批处理文件 #

创建DailyBackup.bat：

@echo off
setlocal enabledelayedexpansion

set "ServerName=你的Azure SQL服务器名.database.windows.net"
set "DatabaseName=目标数据库名"
set "Username=SQL登录名"
set "Password=SQL密码"
set "OutputDir=C:\SQLBackups"
set "RetentionDays=7"

:: 创建输出目录
if not exist "%OutputDir%" mkdir "%OutputDir%"

:: 生成日期格式
for /f "tokens=1-3 delims=/ " %%a in ('date /t') do set "BackupDate=%%c%%b%%a"

:: 执行SQLCMD生成脚本
sqlcmd -S %ServerName% -d %DatabaseName% -U %Username% -P %Password% -i "C:\路径\到\BackupTemplate.sql" -o "%OutputDir%\DailyBackup_%BackupDate%.sql" -x

:: 清理过期脚本
forfiles /p "%OutputDir%" /s /m *.sql /d -%RetentionDays% /c "cmd /c del @path"

步骤3：设置任务计划 #

和方案1类似，创建任务每天运行这个批处理文件即可。

关键注意事项 #

• 安全优化：不要在脚本中明文写密码，推荐使用Azure AD认证（PowerShell中可通过Connect-AzAccount实现），或用Windows凭据管理器存储密码。
• 脚本选项调整：根据需求修改ScriptingOptions的参数，比如是否包含索引、约束、触发器等。
• 权限配置：运行任务的账户需要拥有Azure SQL数据库的db_datareader和db_ddladmin权限，以及输出目录的写入权限。

内容的提问来源于stack exchange，提问作者Hibbert