如何设置SQL定时作业定期导出存储过程等对象到文件

方案1：PowerShell + SMO（推荐，稳定可靠） #

SMO是SQL Server官方提供的管理类库，可直接读取所有用户对象的完整定义，支持批量导出为独立.sql文件。

前置要求 #

• 执行环境安装SqlServer PowerShell模块，可通过Install-Module SqlServer命令安装
• 执行账号具备目标数据库的VIEW DEFINITION权限，以及导出目录的写入权限

导出脚本示例 #

# 配置参数
$serverName = "你的SQL实例名"
$databaseName = "目标数据库名"
$exportPath = "D:\SQL代码备份\$(Get-Date -Format 'yyyyMMdd')" # 按日期创建目录

# 创建导出目录
New-Item -ItemType Directory -Path $exportPath -Force | Out-Null

# 加载SMO对象
Import-Module SqlServer
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverName)
$db = $server.Databases[$databaseName]

# 导出存储过程
foreach ($proc in $db.StoredProcedures | Where-Object {!$_.IsSystemObject}) {
    $proc.Script() | Out-File "$exportPath\PROC_$($proc.Name).sql" -Encoding utf8
}

# 导出用户定义函数
foreach ($func in $db.UserDefinedFunctions | Where-Object {!$_.IsSystemObject}) {
    $func.Script() | Out-File "$exportPath\FUNC_$($func.Name).sql" -Encoding utf8
}

# 导出触发器
foreach ($trig in $db.Triggers) {
    $trig.Script() | Out-File "$exportPath\TRIG_$($trig.Name).sql" -Encoding utf8
}
foreach ($tableTrig in $db.Tables | Select-Object -ExpandProperty Triggers) {
    $tableTrig.Script() | Out-File "$exportPath\TABLE_TRIG_$($tableTrig.Name).sql" -Encoding utf8
}

方案2：配置定时执行任务 #

直接通过SQL Server代理作业实现每日自动执行，步骤如下：

• 打开SSMS，依次展开「SQL Server代理」→ 「作业」→ 右键选择「新建作业」
• 新建作业步骤，类型选择「PowerShell」，将修改好参数的上述脚本粘贴到命令输入框
• 新建作业计划，设置为每日凌晨业务低峰时段执行即可

补充方案：T-SQL直接查询对象定义 #

如果不想依赖PowerShell环境，也可以通过系统视图查询所有对象定义，再结合bcp命令导出：

SELECT 
    o.type_desc AS 对象类型,
    o.name AS 对象名,
    m.definition AS 对象定义
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE 
    o.type IN ('P','FN','IF','TF','TR') -- 过滤需要导出的对象类型
    AND o.is_ms_shipped = 0 -- 排除系统内置对象

注意事项 #

• 导出路径建议设置为独立于SQL Server服务器的外部存储，避免服务器整机故障时备份文件一同丢失
• 可在脚本中增加历史文件清理逻辑，自动删除超过30天/90天的旧备份，节省存储空间
• 可新增校验逻辑，对比导出的文件数量和系统视图统计的对象数量，避免出现导出不全的情况

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