能否创建可从存储过程调用的脚本导出MSSQL全库（架构+数据）至.sql文件？

可以通过存储过程调用PowerShell+SMO组件实现需求，这和SSMS的Generate Scripts功能底层逻辑一致，还能指定目标SQL Server版本（2017）确保脚本兼容性，同时支持SQL Server Agent调度执行。

1. 前提准备 #

• 确保SQL Server实例已启用xp_cmdshell（用于执行外部命令）
• 服务器已安装SQL Server管理工具（SMO组件默认随SSMS或SQL Server安装）
• 执行存储过程的账号需具备：
  • 目标数据库的db_owner权限
  • 输出文件路径的读写权限
  • xp_cmdshell的执行权限

启用xp_cmdshell（若未启用） #

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

2. 创建存储过程 #

这个存储过程接受数据库名、输出路径、目标兼容版本等参数，通过PowerShell调用SMO生成包含架构和数据的完整脚本：

CREATE PROCEDURE dbo.usp_GenerateFullDatabaseScript
    @DatabaseName NVARCHAR(128),
    @OutputFilePath NVARCHAR(500),
    @TargetServerVersion NVARCHAR(50) = 'SQLServer2017'
AS
BEGIN
    SET NOCOUNT ON;

    -- 构造PowerShell执行命令
    DECLARE @PowerShellCmd NVARCHAR(MAX);
    SET @PowerShellCmd = N'powershell.exe -Command "$ErrorActionPreference=''Stop''; ' +
                         N'[System.Reflection.Assembly]::LoadWithPartialName(''Microsoft.SqlServer.SMO'') | Out-Null; ' +
                         N'$server = New-Object Microsoft.SqlServer.Management.Smo.Server(''.''); ' + -- 本地实例，远程实例替换为对应名称
                         N'$db = $server.Databases[''' + @DatabaseName + N''']; ' +
                         N'$scriptOptions = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions; ' +
                         N'$scriptOptions.ScriptSchema = $true; ' +
                         N'$scriptOptions.ScriptData = $true; ' +
                         N'$scriptOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::' + @TargetServerVersion + N'; ' +
                         N'$scriptOptions.IncludeHeaders = $false; ' +
                         N'$scriptOptions.AppendToFile = $false; ' +
                         N'$scriptOptions.ToFileOnly = $true; ' +
                         N'$scriptOptions.FileName = ''' + @OutputFilePath + N'''; ' +
                         N'$db.Script($scriptOptions);"';

    -- 执行命令生成脚本
    EXEC xp_cmdshell @PowerShellCmd;
END
GO

3. 使用方法 #

直接调用存储过程即可生成目标脚本：

-- 示例：导出AdventureWorks数据库到指定路径，兼容SQL Server 2017
EXEC dbo.usp_GenerateFullDatabaseScript
    @DatabaseName = N'AdventureWorks',
    @OutputFilePath = N'D:\Backups\AdventureWorks_Full.sql',
    @TargetServerVersion = N'SQLServer2017';

4. 调度设置 #

通过SQL Server Agent创建定时作业：

1. 新建作业并设置名称
2. 添加步骤，类型选择Transact-SQL (T-SQL)，指定目标数据库后输入上述存储过程调用语句
3. 设置调度计划（如每日凌晨2点执行）
4. 配置作业执行账号（需具备对应权限）

注意事项 #

• 远程实例需修改PowerShell命令中的$server = New-Object Microsoft.SqlServer.Management.Smo.Server(''远程实例名'')，并确保执行账号有远程连接权限
• 输出路径需是SQL Server服务账号可访问的路径（避免使用本地用户专属路径）
• 大数据库生成的脚本文件体积较大，需提前规划磁盘空间
• 若遇SMO版本问题，可安装最新版SSMS更新组件

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