基于网络映射的跨服务器数据库迁移问题求助

一、备份脚本问题分析与修复 #

核心问题 #

1. UNC路径格式错误：原脚本中@backupPath使用\YourNetworkDrive\BackupFolder\，缺少一个起始反斜杠，正确格式应为\\YourNetworkDrive\BackupFolder\。
2. SQL Server服务账户权限不足：SQL Server服务运行的账户（而非你登录SSMS的账户）需要拥有网络共享文件夹的读写权限，否则无法写入备份文件。
3. 无错误捕获机制：备份过程中若遇到数据库只读、文件占用、权限问题等，脚本不会抛出错误，导致后续数据库备份中断但无提示，看起来只完成了10%。
4. 未排除特殊状态数据库：原脚本仅排除系统库，但部分数据库可能处于READ_ONLY、RESTORING等状态，无法正常备份。

修复后的备份脚本 #

DECLARE @backupPath NVARCHAR(1000)
-- 注意：必须使用完整UNC路径，且SQL Server服务账户需有该共享的读写权限
SET @backupPath = '\\YourNetworkDrive\BackupFolder\' 

DECLARE @db_name NVARCHAR(255)
DECLARE @backupFile NVARCHAR(1000)
DECLARE @errorMsg NVARCHAR(MAX)

-- 排除系统库+特殊状态数据库
DECLARE db_cursor CURSOR FOR
    SELECT name 
    FROM sys.databases 
    WHERE state_desc = 'ONLINE' 
      AND name NOT IN ('master', 'tempdb', 'model', 'msdb')
      AND is_read_only = 0 -- 排除只读数据库
      AND is_in_standby = 0 -- 排除处于备用状态的数据库

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @backupFile = @backupPath + @db_name + '_' + CONVERT(VARCHAR(20), GETDATE(), 112) + '.bak'
        -- 添加初始化、压缩选项，避免备份文件叠加
        BACKUP DATABASE @db_name 
        TO DISK = @backupFile 
        WITH INIT, COMPRESSION, STATS = 10 -- 显示备份进度百分比

        PRINT '成功备份数据库: ' + @db_name
    END TRY
    BEGIN CATCH
        SET @errorMsg = '备份数据库 ' + @db_name + ' 失败: ' + ERROR_MESSAGE()
        PRINT @errorMsg
        -- 可选择将错误写入日志表，方便后续排查
        -- INSERT INTO BackupLog (DBName, ErrorMsg, BackupTime) VALUES (@db_name, @errorMsg, GETDATE())
    END CATCH

    FETCH NEXT FROM db_cursor INTO @db_name
END

CLOSE db_cursor
DEALLOCATE db_cursor

额外注意事项 #

• 确认SQL Server服务账户权限：打开服务管理器，找到SQL Server服务，查看登录账户，在共享文件夹上给该账户分配读写权限。
• SQL Server服务无法识别用户映射的驱动器，必须使用UNC路径。

二、恢复脚本问题分析与修复 #

核心问题 #

1. 逻辑判断完全颠倒：原脚本判断目标服务器是否已存在该数据库，存在才执行恢复，但实际迁移场景是目标服务器大多没有对应数据库，导致脚本直接跳过恢复操作。
2. 未检查备份文件是否存在：脚本没有验证@restoreFile对应的备份文件是否真的存在，导致即使文件不存在也可能执行无效SQL。
3. 未指定数据/日志文件路径：新旧服务器的数据库文件路径可能不同，直接恢复会因路径不存在失败，且无错误提示。
4. 无错误捕获机制：恢复失败时不会输出错误信息，导致看起来“执行成功”但无实际操作。

修复后的恢复脚本 #

DECLARE @backupPath NVARCHAR(1000)
SET @backupPath = '\\YourNetworkDrive\BackupFolder\' -- 直接使用共享UNC路径，避免复制文件

DECLARE @db_name NVARCHAR(255)
DECLARE @restoreFile NVARCHAR(1000)
DECLARE @sql NVARCHAR(MAX)
DECLARE @errorMsg NVARCHAR(MAX)
DECLARE @fileList TABLE (
    LogicalName NVARCHAR(128),
    PhysicalName NVARCHAR(260),
    [Type] CHAR(1),
    FileGroupName NVARCHAR(128),
    Size NUMERIC(20,0),
    MaxSize NUMERIC(20,0),
    FileId BIGINT,
    CreateLSN NUMERIC(25,0),
    DropLSN NUMERIC(25,0),
    UniqueId UNIQUEIDENTIFIER,
    ReadOnlyLSN NUMERIC(25,0),
    ReadWriteLSN NUMERIC(25,0),
    BackupSizeInBytes NUMERIC(20,0),
    SourceBlockSize INT,
    FileGroupId INT,
    LogGroupGUID UNIQUEIDENTIFIER,
    DifferentialBaseLSN NUMERIC(25,0),
    DifferentialBaseGUID UNIQUEIDENTIFIER,
    IsReadOnly BIT,
    IsPresent BIT,
    TDEThumbprint VARBINARY(32)
)

-- 从备份文件名中提取数据库名（假设备份文件格式为DBName_YYYYMMDD.bak）
DECLARE restore_cursor CURSOR FOR
    SELECT DISTINCT SUBSTRING(name, 1, CHARINDEX('_', name)-1) AS DBName
    FROM sys.dm_os_file_stats(NULL, NULL)
    WHERE physical_name LIKE @backupPath + '%.bak'

OPEN restore_cursor
FETCH NEXT FROM restore_cursor INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @restoreFile = @backupPath + @db_name + '_' + CONVERT(VARCHAR(20), GETDATE(), 112) + '.bak'
    
    -- 检查备份文件是否存在
    IF EXISTS (SELECT 1 FROM sys.dm_os_file_stats(NULL, NULL) WHERE physical_name = @restoreFile)
    BEGIN
        BEGIN TRY
            -- 获取备份文件中的逻辑文件信息
            INSERT INTO @fileList
            EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @restoreFile + '''')

            -- 生成恢复语句，自动替换文件路径为目标服务器路径
            SET @sql = 'RESTORE DATABASE [' + @db_name + '] FROM DISK = ''' + @restoreFile + ''' WITH REPLACE, RECOVERY, '
            SELECT @sql = @sql + 'MOVE ''' + LogicalName + ''' TO ''D:\SQLData\' + LogicalName + '''' + ','
            FROM @fileList

            -- 移除最后一个逗号
            SET @sql = LEFT(@sql, LEN(@sql)-1)

            EXEC sp_executesql @sql
            PRINT '成功恢复数据库: ' + @db_name

            -- 清空临时表，准备下一个数据库
            DELETE FROM @fileList
        END TRY
        BEGIN CATCH
            SET @errorMsg = '恢复数据库 ' + @db_name + ' 失败: ' + ERROR_MESSAGE()
            PRINT @errorMsg
            DELETE FROM @fileList
        END CATCH
    END
    ELSE
    BEGIN
        PRINT '未找到备份文件: ' + @restoreFile
    END

    FETCH NEXT FROM restore_cursor INTO @db_name
END

CLOSE restore_cursor
DEALLOCATE restore_cursor

额外注意事项 #

• 替换D:\SQLData\为目标服务器的数据库文件存储路径，确保该路径存在且SQL Server服务账户有读写权限。

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