基于.BAK备份的SQL Server数据库批量创建自动化方案问询

一、批量删除所有用户创建的数据库 #

执行以下T-SQL脚本可快速清理测试实例中的非系统数据库（执行前确认无重要数据）：

-- 生成删除用户库的语句，排除系统库
DECLARE @DropDBs NVARCHAR(MAX) = ''

SELECT @DropDBs += 'ALTER DATABASE [' + name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [' + name + ']; '
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
  AND state_desc = 'ONLINE'

-- 执行删除操作
EXEC sp_executesql @DropDBs

说明：先将目标库设为单用户模式强制断开连接，避免因有活跃连接导致删除失败。

二、批量从BAK文件还原数据库 #

单库还原示例 #

替换备份路径、目标库名及文件路径后执行：

RESTORE DATABASE [TestDB1]
FROM DISK = N'C:\BackupFiles\TestDB1.bak'
WITH
  REPLACE, -- 覆盖现有数据库
  RECOVERY, -- 恢复至可用状态
  MOVE N'TestDB1_Data' TO N'C:\SQLData\TestDB1.mdf', -- 数据文件目标路径
  MOVE N'TestDB1_Log' TO N'C:\SQLLogs\TestDB1.ldf', -- 日志文件目标路径
  STATS = 10 -- 显示还原进度（每10%更新）

批量还原脚本（自动处理6个BAK文件） #

先开启xp_cmdshell（仅测试环境使用），自动读取指定目录下的BAK文件并还原：

-- 开启xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

-- 定义备份目录
DECLARE @BackupDir NVARCHAR(200) = 'C:\BackupFiles\'
DECLARE @FileList TABLE (FileName NVARCHAR(200))

-- 读取目录下所有BAK文件
INSERT INTO @FileList
EXEC xp_cmdshell 'DIR "' + @BackupDir + '*.bak" /B'

-- 循环还原每个文件
DECLARE @FileName NVARCHAR(200), @DBName NVARCHAR(100), @RestoreSQL NVARCHAR(MAX)

DECLARE FileCursor CURSOR FOR
SELECT FileName FROM @FileList WHERE FileName IS NOT NULL

OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @FileName

WHILE @@FETCH_STATUS = 0
BEGIN
  -- 从备份文件名提取数据库名（假设文件名与库名一致）
  SET @DBName = LEFT(@FileName, CHARINDEX('.bak', @FileName) - 1)
  
  -- 生成文件移动语句
  DECLARE @FileMoveSQL NVARCHAR(MAX) = ''
  SELECT @FileMoveSQL += 'MOVE N''' + logical_name + ''' TO N''C:\SQLData\' + @DBName + '_' + CASE WHEN type_desc = 'ROWS' THEN 'Data.mdf' ELSE 'Log.ldf' END + ''','
  FROM RESTORE FILELISTONLY FROM DISK = @BackupDir + @FileName
  
  -- 拼接完整还原语句
  SET @RestoreSQL = 'RESTORE DATABASE [' + @DBName + '] FROM DISK = N''' + @BackupDir + @FileName + ''' WITH REPLACE, RECOVERY, ' + LEFT(@FileMoveSQL, LEN(@FileMoveSQL)-1) + ', STATS = 10'
  
  -- 执行还原
  EXEC sp_executesql @RestoreSQL
  
  FETCH NEXT FROM FileCursor INTO @FileName
END

CLOSE FileCursor
DEALLOCATE FileCursor

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

三、解决备份与源实例绑定的问题 #

备份本身不会与源实例绑定，故障源于还原时的参数或路径处理错误。正确的备份设置只需执行标准完整备份：

-- 源实例上的备份语句，生成独立备份文件
BACKUP DATABASE [SourceDB]
TO DISK = N'\\BackupServer\SharedFolder\SourceDB.bak'
WITH
  INIT, -- 覆盖现有备份文件（需追加则用NOINIT）
  COMPRESSION, -- 可选，压缩节省空间
  CHECKSUM -- 可选，验证备份完整性

还原关键注意事项 #

• 必须添加WITH REPLACE参数：强制覆盖现有数据库，避免因库已存在或备份元数据不匹配报错。
• 必须用MOVE子句指定目标路径：源实例的文件路径与测试实例大概率不同，不指定会导致还原失败或数据库卡在“正在还原”状态。
• 默认使用RECOVERY参数：确保还原后数据库处于可用状态，若用NORECOVERY会保持还原状态，需后续日志备份恢复。

一键执行方案（可选） #

将上述T-SQL保存为ResetTestDBs.sql，编写批处理文件ResetDBs.bat一键调用：

@echo off
sqlcmd -S .\TESTINSTANCE -U sa -P YourPassword -i "C:\Scripts\ResetTestDBs.sql"
pause

替换实例名、登录密码及脚本路径即可。

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