SQL Server 2019批量还原多个.bak文件为独立数据库求助

问题说明 #

需要还原一个文件夹下的80+独立.bak备份文件，要求：

• 每个.bak文件还原为独立顶级数据库，数据库名称与.bak文件名（去除.bak后缀）一致
• 批量自动处理，避免手动逐个还原
• 仅熟悉SSMS图形界面操作，对SQL脚本不熟悉，之前生成的脚本无法正常运行

原有脚本的问题 #

提供的脚本存在几个致命错误：

• sys.system_file不是SQL Server的系统视图，无法读取本地文件夹中的.bak文件
• 未定义@BackupPath变量，执行时会因变量不存在报错
• 硬编码固定逻辑文件名LogicalDataFileName/LogicalLogFileName，实际每个备份的逻辑文件名可能不同，直接使用会导致还原失败
• @SQLServer变量未被使用，属于冗余代码

修正后的批量还原方案 #

方案1：SQL脚本（需开启xp_cmdshell） #

步骤1：开启xp_cmdshell（若未开启）

-- 仅在需要时执行，开启xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

步骤2：批量还原脚本

-- 配置参数
DECLARE @BackupPath NVARCHAR(500) = 'D:\BackupFiles'; -- 替换为你的.bak文件所在文件夹
DECLARE @DataPath NVARCHAR(500) = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA'; -- 替换为你的SQL数据文件存储路径
DECLARE @FileName NVARCHAR(255);
DECLARE @DatabaseName NVARCHAR(100);
DECLARE @LogicalDataName NVARCHAR(128);
DECLARE @LogicalLogName NVARCHAR(128);
DECLARE @RestoreSQL NVARCHAR(MAX);

-- 创建临时表存储备份文件列表
CREATE TABLE #BackupFiles (FileName NVARCHAR(255));

-- 获取文件夹下所有.bak文件
INSERT INTO #BackupFiles
EXEC xp_cmdshell 'DIR "' + @BackupPath + '\*.bak" /B';

-- 过滤空行和非.bak文件
DELETE FROM #BackupFiles WHERE FileName IS NULL OR FileName NOT LIKE '%.bak';

-- 遍历每个备份文件
DECLARE backup_cursor CURSOR FOR
SELECT FileName FROM #BackupFiles;

OPEN backup_cursor;
FETCH NEXT FROM backup_cursor INTO @FileName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 生成数据库名称（去除.bak后缀）
    SET @DatabaseName = LEFT(@FileName, LEN(@FileName) - 4);
    
    -- 获取备份文件中的逻辑文件名
    RESTORE FILELISTONLY FROM DISK = @BackupPath + '\' + @FileName;
    
    SELECT 
        @LogicalDataName = name 
    FROM tempdb.sys.sysfiles 
    WHERE fileid = 1; -- 数据文件逻辑名
    
    SELECT 
        @LogicalLogName = name 
    FROM tempdb.sys.sysfiles 
    WHERE fileid = 2; -- 日志文件逻辑名

    -- 生成还原语句
    SET @RestoreSQL = N'RESTORE DATABASE [' + @DatabaseName + '] 
FROM DISK = N''' + @BackupPath + '\' + @FileName + '''
WITH 
    FILE = 1,
    MOVE N''' + @LogicalDataName + ''' TO N''' + @DataPath + '\' + @DatabaseName + '.mdf'',
    MOVE N''' + @LogicalLogName + ''' TO N''' + @DataPath + '\' + @DatabaseName + '_log.ldf'',
    REPLACE,
    STATS = 10'; -- 显示还原进度（每完成10%输出一次）

    -- 打印还原语句（可选，用于验证）
    PRINT @RestoreSQL;
    
    -- 执行还原
    EXEC sp_executesql @RestoreSQL;

    FETCH NEXT FROM backup_cursor INTO @FileName;
END;

-- 清理资源
CLOSE backup_cursor;
DEALLOCATE backup_cursor;
DROP TABLE #BackupFiles;

方案2：PowerShell脚本（更安全，无需开启xp_cmdshell） #

如果你的环境禁用了xp_cmdshell，推荐使用PowerShell脚本，兼容性更好：

# 配置参数
$backupPath = "D:\BackupFiles" # 替换为你的.bak文件夹路径
$dataPath = "C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA" # 替换为SQL数据路径
$sqlInstance = "SQLSERVER\SQLINSTANCE" # 替换为你的SQL实例名称

# 获取所有.bak文件
$bakFiles = Get-ChildItem -Path $backupPath -Filter "*.bak"

foreach ($file in $bakFiles) {
    # 数据库名称（去除后缀）
    $dbName = $file.BaseName
    
    # 获取备份文件的逻辑文件名
    $fileList = Invoke-SqlCmd -ServerInstance $sqlInstance -Query "RESTORE FILELISTONLY FROM DISK = N'$($file.FullName)'"
    $dataFile = $fileList | Where-Object { $_.Type -eq 'D' }
    $logFile = $fileList | Where-Object { $_.Type -eq 'L' }
    
    # 生成还原语句
    $restoreQuery = @"
RESTORE DATABASE [$dbName]
FROM DISK = N'$($file.FullName)'
WITH
    FILE = 1,
    MOVE N'$($dataFile.LogicalName)' TO N'$dataPath\$dbName.mdf',
    MOVE N'$($logFile.LogicalName)' TO N'$dataPath\$dbName_log.ldf',
    REPLACE,
    STATS = 10
"@
    
    # 执行还原
    Write-Host "开始还原数据库: $dbName"
    Invoke-SqlCmd -ServerInstance $sqlInstance -Query $restoreQuery
    Write-Host "数据库 $dbName 还原完成`n"
}

使用注意事项 #

1. 路径替换：务必将脚本中的@BackupPath/$backupPath和@DataPath/$dataPath替换为你实际的文件夹路径
2. 权限验证：确保SQL Server服务账户（或执行PowerShell的账户）拥有备份文件夹的读取权限，以及数据目录的写入权限
3. 测试单个还原：在批量执行前，先手动还原一个.bak文件，验证路径、逻辑文件名和权限是否正常
4. 重名处理：脚本中使用了REPLACE参数，若存在同名数据库会被覆盖，请确保数据已备份或无需保留
5. 进度查看：脚本中加入了STATS = 10，会在还原时输出进度，方便跟踪

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