如何使用通配符拼接T-SQL脚本并实现更高动态性？

需求说明 #

现有固定日期、固定备份文件数量的T-SQL恢复脚本，需要完成两个优化：

• 将硬编码的固定日期（如20240819）替换为动态可配置的值，避免每次手动修改脚本
• 自动适配任意数量的备份文件，不再固定编写6条DISK语句

一、替换固定日期为动态参数 #

T-SQL不支持直接在RESTORE语句中用通配符*拼接文件名，需通过动态SQL+变量实现日期的动态替换，示例如下：

-- 定义可配置参数
DECLARE @BackupDate VARCHAR(8) = '20240819'; -- 可按需修改，或用FORMAT(GETDATE(), 'yyyyMMdd')自动取当前日期
DECLARE @BackupPathPrefix NVARCHAR(200) = N'F:\NH\WH_BEE_';
DECLARE @RestoreScript NVARCHAR(MAX);

-- 拼接基础恢复脚本（先按固定数量示例，后续优化为动态文件数量）
SET @RestoreScript = N'USE [master]
RESTORE DATABASE [WH_BEE] FROM  
DISK = N''' + @BackupPathPrefix + @BackupDate + N'_1.bak'',  
DISK = N''' + @BackupPathPrefix + @BackupDate + N'_2.bak'',  
DISK = N''' + @BackupPathPrefix + @BackupDate + N'_3.bak'',  
DISK = N''' + @BackupPathPrefix + @BackupDate + N'_4.bak'',  
DISK = N''' + @BackupPathPrefix + @BackupDate + N'_5.bak'', 
DISK = N''' + @BackupPathPrefix + @BackupDate + N'_6.bak'' WITH  FILE = 1, 
MOVE N''WH_Data1'' TO N''F:\data\WH_BEE.MDF'',  
MOVE N''WH_Data2'' TO N''F:\data\WH_BEE_1.NDF'',  
MOVE N''WH_Log'' TO N''G:\log\WH_BEE.LDF'',  NOUNLOAD,  STATS = 5';

-- 执行动态生成的脚本
EXEC sp_executesql @RestoreScript;

二、动态适配任意数量的备份文件 #

要自动识别目录下符合命名规则的备份文件（如WH_BEE_YYYYMMDD_*.bak），可通过SQL Server系统存储过程获取文件列表，再动态拼接DISK子句，以下是两种可靠实现方式：

方式1：使用xp_dirtree获取文件列表（无需启用xp_cmdshell） #

-- 1. 定义参数
DECLARE @BackupDate VARCHAR(8) = '20240819';
DECLARE @BackupDir NVARCHAR(200) = N'F:\NH\';
DECLARE @DbName NVARCHAR(50) = N'WH_BEE';
DECLARE @RestoreScript NVARCHAR(MAX);
DECLARE @DiskClauses NVARCHAR(MAX) = N'';

-- 2. 获取当前目录下的所有文件
CREATE TABLE #BackupFiles (subdirectory NVARCHAR(255), depth INT, isfile INT);
INSERT INTO #BackupFiles
EXEC sys.xp_dirtree @BackupDir, 1, 1; -- 1表示只遍历当前目录，1表示返回文件而非文件夹

-- 筛选符合规则的备份文件，拼接DISK子句
SELECT @DiskClauses += N'DISK = N''' + @BackupDir + subdirectory + N''',' + CHAR(13) + CHAR(10)
FROM #BackupFiles
WHERE subdirectory LIKE N'WH_BEE_' + @BackupDate + N'_%.bak'
ORDER BY subdirectory; -- 按文件名序号排序，确保备份文件顺序正确

-- 移除最后一条DISK语句末尾多余的逗号
SET @DiskClauses = LEFT(@DiskClauses, LEN(@DiskClauses) - 2);

-- 3. 拼接完整的恢复脚本
SET @RestoreScript = N'USE [master]
RESTORE DATABASE [' + @DbName + N'] FROM  
' + @DiskClauses + N' WITH  FILE = 1, 
MOVE N''WH_Data1'' TO N''F:\data\WH_BEE.MDF'',  
MOVE N''WH_Data2'' TO N''F:\data\WH_BEE_1.NDF'',  
MOVE N''WH_Log'' TO N''G:\log\WH_BEE.LDF'',  NOUNLOAD,  STATS = 5';

-- 先打印脚本确认正确性，再执行
PRINT @RestoreScript;
-- EXEC sp_executesql @RestoreScript;

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

方式2：使用xp_cmdshell获取文件列表（需启用该功能） #

-- 若未启用xp_cmdshell，先执行以下语句开启（使用后建议关闭）
-- EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
-- EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

-- 1. 定义参数
DECLARE @BackupDate VARCHAR(8) = '20240819';
DECLARE @BackupDir NVARCHAR(200) = N'F:\NH\';
DECLARE @DbName NVARCHAR(50) = N'WH_BEE';
DECLARE @RestoreScript NVARCHAR(MAX);
DECLARE @DiskClauses NVARCHAR(MAX) = N'';

-- 2. 获取符合规则的备份文件名
CREATE TABLE #BackupFiles (FileName NVARCHAR(255));
INSERT INTO #BackupFiles
EXEC xp_cmdshell 'dir /B "' + @BackupDir + 'WH_BEE_' + @BackupDate + '_*.bak"';

-- 过滤空行，拼接DISK子句
SELECT @DiskClauses += N'DISK = N''' + @BackupDir + FileName + N''',' + CHAR(13) + CHAR(10)
FROM #BackupFiles
WHERE FileName IS NOT NULL
ORDER BY FileName;

-- 移除最后一条DISK语句末尾多余的逗号
SET @DiskClauses = LEFT(@DiskClauses, LEN(@DiskClauses) - 2);

-- 3. 拼接并执行脚本
SET @RestoreScript = N'USE [master]
RESTORE DATABASE [' + @DbName + N'] FROM  
' + @DiskClauses + N' WITH  FILE = 1, 
MOVE N''WH_Data1'' TO N''F:\data\WH_BEE.MDF'',  
MOVE N''WH_Data2'' TO N''F:\data\WH_BEE_1.NDF'',  
MOVE N''WH_Log'' TO N''G:\log\WH_BEE.LDF'',  NOUNLOAD,  STATS = 5';

PRINT @RestoreScript;
-- EXEC sp_executesql @RestoreScript;

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

-- 关闭xp_cmdshell（可选）
-- EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
-- EXEC sp_configure 'show advanced options', 0; RECONFIGURE;

注意事项 #

• 确保SQL Server服务账号对备份目录有读取权限
• 使用xp_cmdshell时需注意安全风险，建议使用后立即关闭该功能
• 执行恢复脚本前，务必通过PRINT语句预览生成的内容，确认文件路径和数量正确

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