SQL Server错误Msg 5170：如何每次生成新文件名？

问题根源 #

即使sys.databases中不存在目标数据库，若DATA目录下已存在同名的.mdf/.ldf物理文件，执行CREATE DATABASE仍会触发Msg 5170错误。需通过动态生成带唯一标识的文件名规避该问题。

方法1：用随机标识符生成唯一后缀 #

利用NEWID()生成全局唯一标识符，截取部分字符作为文件名后缀，确保物理文件唯一性：

DECLARE @baseDbName NVARCHAR(128) = 'example';
DECLARE @dataPath NVARCHAR(500);
DECLARE @logPath NVARCHAR(500);
DECLARE @randomSuffix NVARCHAR(8);

-- 获取SQL Server默认数据/日志目录
SELECT @dataPath = CONVERT(NVARCHAR(500), SERVERPROPERTY('InstanceDefaultDataPath'));
SELECT @logPath = CONVERT(NVARCHAR(500), SERVERPROPERTY('InstanceDefaultLogPath'));

-- 从NEWID中截取8位随机字符作为后缀
SET @randomSuffix = RIGHT(NEWID(), 8);

-- 拼接完整文件名与数据库名
SET @dataPath = @dataPath + @baseDbName + @randomSuffix + '.mdf';
SET @logPath = @logPath + @baseDbName + @randomSuffix + '_log.ldf';

-- 动态执行CREATE DATABASE语句
DECLARE @createSql NVARCHAR(MAX);
SET @createSql = N'CREATE DATABASE [' + @baseDbName + @randomSuffix + ']
ON PRIMARY (
    NAME = ''' + @baseDbName + @randomSuffix + ''',
    FILENAME = ''' + @dataPath + ''',
    SIZE = 8MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB
)
LOG ON (
    NAME = ''' + @baseDbName + @randomSuffix + '_log'',
    FILENAME = ''' + @logPath + ''',
    SIZE = 8MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB
)';

EXEC sp_executesql @createSql;

方法2：用高精度时间戳生成后缀 #

若需文件名包含时间信息，可使用精确到毫秒的时间戳作为后缀：

DECLARE @baseDbName NVARCHAR(128) = 'example';
DECLARE @dataPath NVARCHAR(500);
DECLARE @logPath NVARCHAR(500);
DECLARE @timeSuffix NVARCHAR(17);

-- 获取默认目录
SELECT @dataPath = CONVERT(NVARCHAR(500), SERVERPROPERTY('InstanceDefaultDataPath'));
SELECT @logPath = CONVERT(NVARCHAR(500), SERVERPROPERTY('InstanceDefaultLogPath'));

-- 生成格式为YYYYMMDDHHMMSSmmm的时间戳后缀
SET @timeSuffix = CONVERT(NVARCHAR(8), GETDATE(), 112) 
                  + REPLACE(CONVERT(NVARCHAR(12), GETDATE(), 114), ':', '');

-- 拼接文件名与数据库名
SET @dataPath = @dataPath + @baseDbName + @timeSuffix + '.mdf';
SET @logPath = @logPath + @baseDbName + @timeSuffix + '_log.ldf';

-- 动态创建数据库
DECLARE @createSql NVARCHAR(MAX);
SET @createSql = N'CREATE DATABASE [' + @baseDbName + @timeSuffix + ']
ON PRIMARY (
    NAME = ''' + @baseDbName + @timeSuffix + ''',
    FILENAME = ''' + @dataPath + ''',
    SIZE = 8MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB
)
LOG ON (
    NAME = ''' + @baseDbName + @timeSuffix + '_log'',
    FILENAME = ''' + @logPath + ''',
    SIZE = 8MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB
)';

EXEC sp_executesql @createSql;

注意事项 #

• 确保SQL Server服务账户对目标目录拥有读写权限，否则会触发权限异常。
• 若需使用自定义路径，直接替换@dataPath和@logPath的赋值语句即可，无需依赖SERVERPROPERTY。
• 动态SQL拼接时需注意字符转义，避免注入风险（上述示例中变量为内部生成，风险极低）。

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