如何将PowerShell获取的备份文件大小存入T-SQL变量

核心思路 #

通过xp_cmdshell调用PowerShell命令读取备份文件的实际大小，将结果存入临时表后提取到T-SQL变量中，用于后续逻辑封装。

具体步骤及代码 #

1. 启用xp_cmdshell（首次执行）
   如果SQL Server未启用xp_cmdshell，先执行以下语句开启：

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

2. 编写T-SQL脚本获取文件大小
   以下脚本会将指定备份文件的字节数存入变量，并转换为更易读的KB/MB格式：

-- 定义备份文件路径
DECLARE @BackupFilePath NVARCHAR(256) = N'\\FileShare\DBBackups\Server1\DB1\DB1.BAK';
-- 存储文件大小的变量（单位：字节）
DECLARE @FileSizeBytes BIGINT;
-- 临时表用于接收PowerShell的输出结果
CREATE TABLE #TempFileSize (FileSize NVARCHAR(50));

-- 构造PowerShell命令：获取目标文件的字节长度
DECLARE @PowerShellCmd NVARCHAR(MAX) = 
    N'powershell -Command "(Get-Item ''' + REPLACE(@BackupFilePath, '''', '''''') + ''').Length"';

-- 执行PowerShell命令并将结果写入临时表
INSERT INTO #TempFileSize
EXEC xp_cmdshell @PowerShellCmd;

-- 从临时表中提取有效数值（过滤空行和非数字内容）
SELECT @FileSizeBytes = CAST(FileSize AS BIGINT)
FROM #TempFileSize
WHERE FileSize IS NOT NULL AND ISNUMERIC(FileSize) = 1;

-- 示例：输出转换后的大小（可替换为你的后续业务逻辑）
SELECT 
    @FileSizeBytes AS [备份文件大小(字节)],
    ROUND(@FileSizeBytes / 1024.0, 2) AS [备份文件大小(KB)],
    ROUND(@FileSizeBytes / 1024.0 / 1024.0, 2) AS [备份文件大小(MB)];

-- 清理临时表
DROP TABLE #TempFileSize;

关键注意事项 #

• 权限配置：确保SQL Server服务运行账户拥有目标文件共享路径的读取权限，否则PowerShell无法访问文件。
• 路径转义：用REPLACE处理路径中的单引号，避免SQL语法错误或注入风险。
• 错误处理：如果文件不存在或无法访问，@FileSizeBytes会保持NULL，可添加判断逻辑处理这种异常情况。

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