一次 SQL Server 2019 数据库迁移实战

前言

最近迁移了一套 SQL Server 2019 数据库，原服务器使用 Windows Server 2019，新服务器换成 Windows Server 2025，数据库版本保持不变。

这次主要涉及 2 个数据库，一个只迁移结构，另一个需要连同数据一起迁移。另外，还要处理业务账号、权限和自动备份任务。

本文记录一下整个过程。

迁移之前，先确认源端配置

首先检查 SQL Server 的版本、排序规则和身份验证模式。

SELECT
    @@SERVERNAME AS ServerName,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('Collation') AS Collation,
    SERVERPROPERTY('IsIntegratedSecurityOnly') AS WindowsAuthOnly;

源端使用 SQL Server 2019 Standard，默认实例 MSSQLSERVER，实例排序规则为 Chinese_PRC_CI_AS，采用混合身份验证模式。

接着检查数据库清单：

SELECT
    name,
    state_desc,
    compatibility_level,
    collation_name,
    recovery_model_desc
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

检查时发现，部分数据库的排序规则与实例不同。

因此，新服务器安装时保持实例排序规则一致，而数据库自身的排序规则和兼容级别则按照源端保留。

再检查数据库文件：

SELECT
    DB_NAME(database_id) AS DatabaseName,
    name AS LogicalName,
    type_desc,
    physical_name,
    CAST(size * 8.0 / 1024 AS DECIMAL(18, 2)) AS SizeMB
FROM sys.master_files
WHERE database_id > 4
ORDER BY database_id, file_id;

这一步主要是确认数据库文件位置、大小，以及目标服务器的磁盘空间是否足够。

新服务器怎么安装和配置？

新服务器使用 Windows Server 2025 Datacenter，安装 SQL Server 2019 Standard。

安装时选择数据库引擎和复制组件，使用默认实例 MSSQLSERVER，身份验证模式选择 Mixed Mode。

数据库文件目录重新规划：

C:\Program Files\Microsoft SQL Server\
    SQL Server 程序及系统数据库

D:\MSSQL\Data\
    用户数据库数据文件

D:\MSSQL\Log\
    用户数据库日志文件

D:\MSSQL\TempDB\
    TempDB 文件

D:\MSSQL\Backup\
    数据库备份

这台服务器有 4 个逻辑 CPU，安装时配置了 4 个 TempDB 数据文件，每个初始大小 256MB、自动增长 256MB，日志文件初始大小 512MB。

MAXDOP 设置为 4，max server memory 设置为 12800MB。

安装完成后，通过 SSMS 检查数据库实例：

SELECT
    @@VERSION AS VersionInfo,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('Collation') AS Collation;

再检查几个关键参数：

SELECT
    name,
    value_in_use
FROM sys.configurations
WHERE name IN (
    'max degree of parallelism',
    'cost threshold for parallelism',
    'max server memory (MB)',
    'min server memory (MB)'
);

同时检查默认数据和日志目录：

SELECT
    SERVERPROPERTY('InstanceDefaultDataPath') AS DefaultDataPath,
    SERVERPROPERTY('InstanceDefaultLogPath') AS DefaultLogPath;

实例正常启动，参数和路径也符合预期后，开始迁移数据库。

只迁移数据库结构，怎么做？

这次 APP_DB 不需要业务数据，因此采用 SSMS 的 Generate Scripts 功能导出数据库结构。

操作路径：

数据库 → Tasks → Generate Scripts

对象选择：

Script entire database and all database objects

然后进入 Advanced，主要确认以下选项：

生成的脚本保存为 Unicode 格式的 .sql 文件，再复制到目标服务器执行。

这里要注意，生成数据库结构脚本不等于只包含 CREATE TABLE。

索引、约束、视图、存储过程、函数、触发器以及部分用户和权限语句，都可能出现在脚本中。

本次执行过程中就遇到了：

Msg 15151
Cannot find the user 'legacy_user',
because it does not exist or you do not have permission.

原因是脚本包含了针对数据库用户的授权语句，但目标端还没有对应的用户。

这类问题需要单独处理 Login 和 User，不能因为大部分脚本执行成功，就认为所有对象和权限已经完整迁移。

怎么确认数据库结构迁移完整？

脚本执行完成后，先统计对象数量。

USE [APP_DB];
GO

SELECT
    type_desc,
    COUNT(*) AS ObjectCount
FROM sys.objects
WHERE is_ms_shipped = 0
  AND type IN ('U', 'V', 'P', 'FN', 'IF', 'TF', 'TR')
GROUP BY type_desc
ORDER BY type_desc;

本次目标端检查结果：

再检查索引和约束：

USE [APP_DB];
GO

SELECT 'INDEX' AS ObjectType, COUNT(*) AS ObjectCount
FROM sys.indexes i
JOIN sys.tables t
    ON i.object_id = t.object_id
WHERE t.is_ms_shipped = 0
  AND i.index_id > 0
  AND i.is_primary_key = 0
  AND i.is_unique_constraint = 0

UNION ALL

SELECT 'PRIMARY KEY', COUNT(*)
FROM sys.key_constraints
WHERE type = 'PK'

UNION ALL

SELECT 'FOREIGN KEY', COUNT(*)
FROM sys.foreign_keys

UNION ALL

SELECT 'UNIQUE CONSTRAINT', COUNT(*)
FROM sys.key_constraints
WHERE type = 'UQ'

UNION ALL

SELECT 'CHECK CONSTRAINT', COUNT(*)
FROM sys.check_constraints

UNION ALL

SELECT 'DEFAULT CONSTRAINT', COUNT(*)
FROM sys.default_constraints;

目标端检查到 62 个主键、4 个唯一约束、211 个默认约束，以及其他索引对象。

这些数量需要和源端采用相同 SQL 对比，才能判断是否存在遗漏。

由于 APP_DB 只需要结构，还要确认没有业务数据：

USE [APP_DB];
GO

SELECT
    COALESCE(SUM(p.rows), 0) AS TotalRows
FROM sys.partitions p
JOIN sys.tables t
    ON p.object_id = t.object_id
WHERE p.index_id IN (0, 1)
  AND t.is_ms_shipped = 0;

最终返回：

TotalRows
---------
0

说明目标端用户表没有业务数据，符合本次迁移要求。

另一套数据库需要连同数据一起迁移

SCHEDULER_DB 需要保留原有数据，因此采用完整备份恢复。

先在源端执行：

BACKUP DATABASE [SCHEDULER_DB]
TO DISK = N'E:\SQLBackup\SCHEDULER_DB_full.bak'
WITH
    INIT,
    COMPRESSION,
    CHECKSUM,
    STATS = 10;
GO

备份完成后，将文件复制到目标服务器。

恢复前先检查备份集：

RESTORE VERIFYONLY
FROM DISK = N'D:\MSSQL\Backup\SCHEDULER_DB_full.bak'
WITH CHECKSUM;
GO

再查看逻辑文件名：

RESTORE FILELISTONLY
FROM DISK = N'D:\MSSQL\Backup\SCHEDULER_DB_full.bak';
GO

这一步很重要。

因为源服务器数据库文件位于 E: 盘，而新服务器规划到了 D:\MSSQL。

如果直接按照原路径恢复，就会出现：

The path specified by
"E:\Program Files\Microsoft SQL Server\..."
is not in a valid directory.

所以恢复时需要使用 WITH MOVE。

下面的逻辑文件名为脱敏示例，实际执行时必须以 RESTORE FILELISTONLY 返回的结果为准。

RESTORE DATABASE [SCHEDULER_DB]
FROM DISK = N'D:\MSSQL\Backup\SCHEDULER_DB_full.bak'
WITH
    MOVE N'SCHEDULER_DB'
        TO N'D:\MSSQL\Data\SCHEDULER_DB.ndf',
    MOVE N'SCHEDULER_DB_log'
        TO N'D:\MSSQL\Log\SCHEDULER_DB_log.ldf',
    RECOVERY,
    STATS = 10;
GO

恢复成功后检查数据库状态：

SELECT
    name,
    state_desc,
    compatibility_level,
    collation_name
FROM sys.databases
WHERE name = N'SCHEDULER_DB';

结果显示数据库 ONLINE，兼容级别为 100，排序规则为 Chinese_PRC_CI_AS。

再统计表数据：

USE [SCHEDULER_DB];
GO

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    SUM(p.rows) AS RowCount
FROM sys.tables t
JOIN sys.schemas s
    ON t.schema_id = s.schema_id
JOIN sys.partitions p
    ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)
GROUP BY s.name, t.name
ORDER BY s.name, t.name;

相关业务表的数据已经恢复。

数据库迁移完了，账号为什么还会报错？

数据库迁移完成后，业务方使用客户端连接新服务器，出现：

Login failed for user 'app_user'. (18456)

开始检查发现，目标端缺少对应的 Login。

创建账号并处理数据库 User 映射后，业务已经可以正常连接，两个数据库的基本读写权限检查也全部返回 1。

原以为账号问题处理完了，结果业务继续操作时又出现：

CREATE TABLE permission denied in database 'APP_DB'.

接着又报：

The specified schema name "dbo"
either does not exist or you do not have permission to use it.

当时第一反应是缺少 DDL 权限，于是开始检查 CREATE TABLE 和 Schema 权限。

但仔细想想，问题有点不对。

源服务器上业务一直正常运行，为什么迁移之后会连续缺少这么多权限？

与其继续一个个补授权，不如直接看看源服务器上这个账号到底拥有什么权限。

最后发现，源端账号竟然是 sysadmin

先在源服务器检查 Login：

USE master;
GO

SELECT
    name,
    type_desc,
    is_disabled,
    default_database_name
FROM sys.server_principals
WHERE name = N'app_user';

账号正常，默认数据库指向另一套业务库。

接着查询服务器角色：

SELECT
    m.name AS LoginName,
    r.name AS ServerRole
FROM sys.server_role_members rm
JOIN sys.server_principals r
    ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals m
    ON rm.member_principal_id = m.principal_id
WHERE m.name = N'app_user';

结果很直接：

LoginName    ServerRole
-----------  ----------
app_user     sysadmin
app_user     dbcreator

原来源端业务账号不仅是 dbcreator，还是 sysadmin。

这就解释了之前为什么会连续出现权限问题。

源端账号本来就有整个 SQL Server 实例的管理权限，而目标端最初只配置了普通业务账号的读写权限。

两边的权限根本不在一个级别。

同时，在源端查询两个业务数据库的数据库用户、数据库角色及显式授权时，没有找到单独的 app_user 记录。

这也不奇怪。

对于 sysadmin 成员，SQL Server 在数据库中通常以 dbo 身份执行操作，不需要依靠普通数据库 User 的显式授权来获得权限。

所以之前只检查数据库级权限，实际上漏掉了最关键的服务器角色。

把源端权限恢复到目标服务器

既然本次要求与源端保持一致，就在确认授权范围后，将原有服务器角色恢复到目标端。

USE master;
GO

ALTER SERVER ROLE [sysadmin]
ADD MEMBER [app_user];

ALTER SERVER ROLE [dbcreator]
ADD MEMBER [app_user];
GO

这里需要说明一下：

sysadmin 已经包含 dbcreator 所需的管理能力，保留两个角色成员关系只是为了与源端配置一致。

但 sysadmin 的权限范围是整个 SQL Server 实例，并不是只对两个业务数据库生效。

因此，这种做法适用于本次经过确认的原权限恢复，不建议把它作为普通业务账号的默认授权方式。

执行完成后检查：

SELECT
    sp.name AS LoginName,
    IS_SRVROLEMEMBER('sysadmin', sp.name) AS IsSysadmin,
    IS_SRVROLEMEMBER('dbcreator', sp.name) AS IsDbcreator,
    sp.is_disabled
FROM sys.server_principals sp
WHERE sp.name = N'app_user';

目标端返回：

LoginName    IsSysadmin    IsDbcreator    is_disabled
-----------  ------------  -------------  -----------
app_user     1             1              0

至此，源端最关键的服务器角色已经恢复。

目标端账号的默认数据库与源端不同，可以根据业务连接方式调整：

ALTER LOGIN [app_user]
WITH DEFAULT_DATABASE = [APP_DB];
GO

默认数据库只影响登录后未明确指定数据库时的初始上下文，不决定账号是否具有 sysadmin 权限。

最后，还需要让业务端重新建立连接，实际测试建表等操作，才能确认之前的 DDL 报错已经消除。

自动备份任务也不能漏

数据库迁移完成后，还需要处理 SQL Server Agent。

因为数据库备份恢复或结构脚本迁移，并不会自动把原服务器上的 Agent Job 一起迁移。

这次针对两个业务数据库，分别重新配置了自动备份任务。

备份策略如下：

每个任务分成三个步骤：

01_FULL_BACKUP
      ↓
02_VERIFY_BACKUP
      ↓
03_DELETE_OLD_BACKUPS

完整备份使用压缩和校验和：

DECLARE @BackupFile NVARCHAR(4000);

SET @BackupFile =
    N'D:\MSSQL\Backup\APP_DB\APP_DB_FULL_'
    + CONVERT(CHAR(8), GETDATE(), 112)
    + N'_'
    + REPLACE(CONVERT(CHAR(8), GETDATE(), 108), ':', '')
    + N'.bak';

BACKUP DATABASE [APP_DB]
TO DISK = @BackupFile
WITH
    COMPRESSION,
    CHECKSUM,
    STATS = 10;

备份验证步骤使用 RESTORE VERIFYONLY 检查本次生成的备份文件。

历史备份清理步骤则删除超过 7 天的备份文件，避免长期积累占满磁盘。

两个任务创建完成后，分别手动执行一次：

USE msdb;
GO

EXEC dbo.sp_start_job
    @job_name = N'APP_DB_FULL_DAILY_0200';
GO

EXEC dbo.sp_start_job
    @job_name = N'SCHEDULER_DB_FULL_DAILY_0230';
GO

接着检查任务历史：

SELECT
    j.name AS JobName,
    h.step_id,
    h.step_name,
    CASE h.run_status
        WHEN 0 THEN 'FAILED'
        WHEN 1 THEN 'SUCCEEDED'
        WHEN 2 THEN 'RETRY'
        WHEN 3 THEN 'CANCELED'
        WHEN 4 THEN 'IN PROGRESS'
    END AS RunStatus,
    h.message
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h
    ON j.job_id = h.job_id
WHERE j.name IN (
    N'APP_DB_FULL_DAILY_0200',
    N'SCHEDULER_DB_FULL_DAILY_0230'
)
ORDER BY h.instance_id DESC;

实际检查中，两套数据库的三个步骤均显示 SUCCEEDED。

最后检查备份历史：

SELECT TOP (20)
    bs.database_name,
    bs.backup_finish_date,
    CAST(
        bs.backup_size / 1024.0 / 1024
        AS DECIMAL(18, 2)
    ) AS SizeMB,
    bs.has_backup_checksums,
    bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf
    ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name IN (
    N'APP_DB',
    N'SCHEDULER_DB'
)
  AND bs.type = 'D'
ORDER BY bs.backup_finish_date DESC;

两个数据库都能查询到新生成的完整备份记录，并且启用了备份校验和。

需要注意，RESTORE VERIFYONLY 不能代替实际恢复演练，7 天保留策略也还需要后续观察定时执行情况。

最后总结

这次 SQL Server 迁移，数据库结构和数据恢复都比较顺利，反而是账号权限花了不少时间。

一开始只检查了数据库 User 和显式授权，后来才发现源端账号属于 sysadmin。权限模型没有迁移完整，自然会不断出现新的报错。

以后再做 SQL Server 迁移，除了数据库本身，Login、SID、服务器角色、数据库权限和 Agent Job 也得一起检查。

这些东西不在业务表里，但少了任何一项，都可能影响业务正常使用。