SQL Server 2014迁2019：如何批量附加文件夹内所有MDF文件？

方案一：PowerShell脚本（推荐，无需开启xp_cmdshell） #

该脚本会遍历指定文件夹，匹配每个.MDF对应的.LDF文件，自动生成并执行附加数据库的T-SQL语句，处理含空格、特殊字符的文件名，同时跳过已存在的数据库。

# 配置参数
$targetFolder = "D:\SQLDBs"  # 替换为你的数据库文件所在文件夹
$sqlInstance = "localhost"   # 替换为你的SQL Server实例名（命名实例格式：localhost\INSTANCE_NAME）

# 获取所有MDF文件
$mdfFiles = Get-ChildItem -Path $targetFolder -Filter "*.mdf" -File

foreach ($mdf in $mdfFiles) {
    # 提取数据库名（移除.MDF后缀）
    $dbName = $mdf.BaseName
    # 拼接对应LDF文件路径
    $ldfPath = Join-Path -Path $targetFolder -ChildPath "$($mdf.BaseName).ldf"

    # 检查数据库是否已存在
    $checkDbQuery = "SELECT 1 FROM sys.databases WHERE name = N'$dbName'"
    $dbExists = Invoke-SqlCmd -ServerInstance $sqlInstance -Query $checkDbQuery -ErrorAction SilentlyContinue

    if ($dbExists) {
        Write-Host "数据库 $dbName 已存在，跳过"
        continue
    }

    # 根据LDF是否存在生成附加语句
    if (Test-Path $ldfPath) {
        $attachQuery = @"
CREATE DATABASE [$dbName]
ON 
    (FILENAME = N'$($mdf.FullName)'),
    (FILENAME = N'$ldfPath')
FOR ATTACH;
"@
    } else {
        # 仅在确认LDF无法恢复时使用此选项，可能丢失未提交事务
        $attachQuery = @"
CREATE DATABASE [$dbName]
ON 
    (FILENAME = N'$($mdf.FullName)')
FOR ATTACH_REBUILD_LOG;
"@
        Write-Warning "LDF文件 $ldfPath 不存在，将尝试重建日志附加数据库 $dbName"
    }

    # 执行附加操作
    try {
        Invoke-SqlCmd -ServerInstance $sqlInstance -Query $attachQuery -ErrorAction Stop
        Write-Host "成功附加数据库: $dbName"
    } catch {
        Write-Error "附加数据库 $dbName 失败: $_"
    }
}

使用说明 #

1. 替换$targetFolder和$sqlInstance为你的实际路径和实例名
2. 运行前确保：
   • 你拥有SQL Server的sysadmin权限
   • PowerShell已启用执行脚本权限（可通过Set-ExecutionPolicy RemoteSigned临时开启）
   • SQL Server服务账户对目标文件夹有读写权限

方案二：T-SQL脚本（需开启xp_cmdshell） #

若更习惯用T-SQL操作，可使用此方案，但需注意xp_cmdshell的安全风险，使用后建议关闭。

步骤1：开启xp_cmdshell #

-- 启用高级选项配置
sp_configure 'show advanced options', 1;
RECONFIGURE;
-- 开启xp_cmdshell
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

步骤2：批量附加脚本 #

DECLARE @folderPath NVARCHAR(500) = N'D:\SQLDBs';  -- 替换为目标文件夹路径
DECLARE @sqlBatch NVARCHAR(MAX) = N'';
DECLARE @mdfFileName NVARCHAR(500);
DECLARE @dbName NVARCHAR(128);
DECLARE @fullMdfPath NVARCHAR(500);
DECLARE @fullLdfPath NVARCHAR(500);

-- 创建临时表存储MDF文件列表
CREATE TABLE #MDFList (FileName NVARCHAR(500));

-- 读取文件夹内所有MDF文件名
INSERT INTO #MDFList
EXEC xp_cmdshell 'DIR "' + @folderPath + '\*.mdf" /B';

-- 遍历每个MDF文件
DECLARE fileCursor CURSOR FOR
SELECT FileName FROM #MDFList WHERE FileName IS NOT NULL;

OPEN fileCursor;
FETCH NEXT FROM fileCursor INTO @mdfFileName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @dbName = LEFT(@mdfFileName, LEN(@mdfFileName) - 4);
    SET @fullMdfPath = @folderPath + N'\' + @mdfFileName;
    SET @fullLdfPath = @folderPath + N'\' + @dbName + N'.ldf';

    -- 跳过已存在的数据库
    IF EXISTS (SELECT 1 FROM sys.databases WHERE name = @dbName)
    BEGIN
        PRINT N'数据库 ' + @dbName + N' 已存在，跳过';
        FETCH NEXT FROM fileCursor INTO @mdfFileName;
        CONTINUE;
    END

    -- 生成附加语句
    IF EXISTS (SELECT 1 FROM sys.dm_os_volume_stats(DB_ID('master'), 1) WHERE volume_mount_point + relative_path = @fullLdfPath)
    BEGIN
        SET @sqlBatch = @sqlBatch + N'
CREATE DATABASE [' + @dbName + N']
ON 
    (FILENAME = N''' + @fullMdfPath + N'''),
    (FILENAME = N''' + @fullLdfPath + N''')
FOR ATTACH;';
    END
    ELSE
    BEGIN
        SET @sqlBatch = @sqlBatch + N'
CREATE DATABASE [' + @dbName + N']
ON 
    (FILENAME = N''' + @fullMdfPath + N''')
FOR ATTACH_REBUILD_LOG;';
        PRINT N'警告：数据库 ' + @dbName + N' 的LDF文件不存在，将尝试重建日志';
    END

    FETCH NEXT FROM fileCursor INTO @mdfFileName;
END

CLOSE fileCursor;
DEALLOCATE fileCursor;

-- 执行批量附加语句
EXEC sp_executesql @sqlBatch;

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

步骤3：关闭xp_cmdshell（可选，增强安全性） #

sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
sp_configure 'show advanced options', 0;
RECONFIGURE;

关键注意事项 #

• 权限验证：确保SQL Server服务账户对目标文件夹有读写权限，否则会因文件访问失败导致附加失败
• 文件锁定：确认原服务器的SQL Server服务已停止，MDF/LDF文件未被其他进程占用
• 版本兼容：SQL Server 2019可直接附加2014版本的数据库文件，附加后数据库兼容性级别会自动升级为150（可通过ALTER DATABASE [DBName] SET COMPATIBILITY_LEVEL = 120改回2014级别）
• 日志重建风险：FOR ATTACH_REBUILD_LOG会重建日志文件，但会丢失未提交的事务，仅在LDF文件彻底无法恢复时使用

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