如何在SQL Server备份磁盘路径与DBCC命令中使用变量？

问题重现 #

可正常执行的静态SQL #

DECLARE @database NVARCHAR(128) = 'DEMO';
BACKUP LOG @database TO DISK = 'D:\Backups\DEMO_Log.bak' WITH NOFORMAT, NOINIT, NAME = N'DEMO_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
DBCC SHRINKFILE (N'DEMO_Log', 0, TRUNCATEONLY);

报错的变量拼接尝试 #

DECLARE @database NVARCHAR(128) = 'DEMO';
DECLARE @logPath NVARCHAR(256) = 'D:\Backups\' + @database + '_Log.bak';
DECLARE @logFileName NVARCHAR(128) = @database + '_Log';

-- TO DISK无法直接使用变量，执行报错
BACKUP LOG @database TO DISK = @logPath WITH NOFORMAT, NOINIT, NAME = N'DEMO_LogBackup', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
-- DBCC SHRINKFILE的文件名参数无法直接用变量，执行报错
DBCC SHRINKFILE (@logFileName, 0, TRUNCATEONLY);

原因分析 #

SQL Server中，BACKUP LOG的TO DISK路径参数、DBCC SHRINKFILE的文件名称参数，都属于不支持直接使用局部变量的语法节点，必须通过动态SQL拼接完整语句后执行。

解决方案 #

使用sp_executesql（推荐，支持参数化，避免SQL注入）拼接并执行动态SQL，同时通过日期函数生成带时间戳的文件名，避免备份文件被覆盖。

关键日期拼接方法 #

可生成无特殊字符的日期格式（示例为yyyyMMddHHmmss），适配Windows文件名规则：

REPLACE(REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 120), '-', ''), ':', '')

完整可执行示例 #

DECLARE @database NVARCHAR(128) = 'DEMO';
DECLARE @logFileName NVARCHAR(128);
DECLARE @backupPath NVARCHAR(512);
DECLARE @dynamicSql NVARCHAR(MAX);

-- 动态获取数据库日志文件名，避免硬编码
SELECT @logFileName = name FROM sys.database_files WHERE type_desc = 'LOG';

-- 生成带日期戳的备份路径
SET @backupPath = 'D:\Backups\' + @database + '_Log_' 
                  + REPLACE(REPLACE(CONVERT(NVARCHAR(19), GETDATE(), 120), '-', ''), ':', '') 
                  + '.bak';

-- 拼接并执行备份日志的动态SQL
SET @dynamicSql = N'BACKUP LOG ' + QUOTENAME(@database) + N' TO DISK = ''' + @backupPath + N''' 
                   WITH NOFORMAT, NOINIT, NAME = N''' + @database + N'_LogBackup'', 
                   SKIP, NOREWIND, NOUNLOAD, STATS = 10;';
EXEC sp_executesql @dynamicSql;

-- 拼接并执行收缩日志的动态SQL
SET @dynamicSql = N'DBCC SHRINKFILE (''' + @logFileName + N''', 0, TRUNCATEONLY);';
EXEC sp_executesql @dynamicSql;

说明 #

1. QUOTENAME(@database)处理数据库名称，避免名称含特殊字符时语法出错。
2. 通过sys.database_files动态获取日志文件名，提升脚本通用性。
3. 日期格式化为无分隔符字符串，规避Windows文件名禁用字符。
4. sp_executesql比EXEC()更安全，支持扩展参数化逻辑。

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