SQL Server 2008R2 到 2012 数据库迁移完整指南
目录
- 一、迁移原理说明
- 1.1 跨版本兼容性:
- 1.2 文件路径映射:
- 1.3 权限控制机制
- 二、完整迁移步骤
- 步骤1:准备目标服务器环境
- 2.1.1 创建目录结构:
- 2.1.2 设置文件夹权限:
- 步骤2:传输备份文件
- 2.2.1 从本地PC远程登录目标云服务器
- 步骤3:获取逻辑文件名(关键步骤)
- 2.3.1 执行SQL查询:
- 2.3.2 记录关键信息:
- 步骤4:执行数据库还原
- 步骤5:验证迁移结果
- 步骤6:设置兼容级别(可选)
- 步骤7:空间监控
- 2.7.1 PowerShell磁盘监控
- 三、故障排除指南
- 四、最佳实践建议
- 4.1 迁移后操作
一、迁移原理说明
1.1 跨版本兼容性:
SQL Server 支持从低版本向高版本迁移(如 2008R2 → 2012)
还原过程自动升级数据库文件格式;
兼容级别保留110(SQL Server 2012)或可降级至100(SQL Server 2008R2 模式)
1.2 文件路径映射:
- 备份文件包含 原始逻辑文件名 和 物理路径 。
- 目标服务器路径不同时,需要 MOVE 重定向 。
1.3 权限控制机制
SQL Server服务账户(NT Service\MSSQLSERVER)需要文件系统完全控制权限;
icacls 命令设置继承权限(OI=对象继承,CI=容器继承,F=完全控制)
- REPLACE 选项作用
- a. 强制覆盖现有数据库
- b. 忽略备份集元数据校验
- c. 解决"备份集与现有数据库不同"错误
二、完整迁移步骤
步骤1:准备目标服务器环境
2.1.1 创建目录结构:
以管理员身份运行CMD
/PowerShell
mkdir C:\SQLData # 数据文件存储
mkdir C:\SQLLog # 日志文件存储
mkdir C:\SQLBackups # 备份文件存储
2.1.2 设置文件夹权限:
icacls "C:\SQLData" /grant "NT Service\MSSQLSERVER:(OI)(CI)F"
icacls "C:\SQLLog" /grant "NT Service\MSSQLSERVER:(OI)(CI)F"
icacls "C:\SQLBackups" /grant "Everyone:(OI)(CI)M"
权限说明:
(OI
):对象继承 - 应用于文件
(CI
):容器继承 - 应用于子文件夹
F
:完全控制权限
M
:修改权限
步骤2:传输备份文件
推荐方法 -
远程桌面传输:
2.2.1 从本地PC远程登录目标云服务器
- 访问源服务器共享路径,如:
\\192.168.1.57\bak
- 复制
Database.bak
到C:\SQLBackups\
替代方法 - PowerShell 网络传输:
在目标服务器执行:
$cred = Get-Credential -UserName '源服务器管理员'
Copy-Item -Path "\\192.168.1.57\bak\DMS-QA.bak" `-Destination "C:\SQLBackups\" `-Credential $cred
步骤3:获取逻辑文件名(关键步骤)
2.3.1 执行SQL查询:
RESTORE FILELISTONLY
FROM DISK = 'C:\SQLBackups\DMS-QA.bak';
输出示例:
LogicalName PhysicalName Type
DMS-QA E:\Data\DMS-QA.mdf D
DMS-QA_Log F:\Log\DMS-QA.ldf L
2.3.2 记录关键信息:
- 数据文件逻辑名:
DMS-QA
- 日志文件逻辑名:
DMS-QA_Log
步骤4:执行数据库还原
USE master;
GO-- 设置单用户模式(断开所有连接)
IF DB_ID('DMS-QA') IS NOT NULL
BEGINALTER DATABASE [DMS-QA] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
END-- 执行还原操作
RESTORE DATABASE [DMS-QA]
FROM DISK = 'C:\SQLBackups\DMS-QA.bak'
WITH MOVE 'DMS-QA' TO 'C:\SQLData\DMS-QA.mdf', -- 使用步骤3获取的逻辑名MOVE 'DMS-QA_Log' TO 'C:\SQLLog\DMS-QA.ldf', -- 使用步骤3获取的逻辑名REPLACE, -- 强制覆盖现有数据库STATS = 5, -- 每完成5%显示进度RECOVERY; -- 使数据库立即可用-- 恢复多用户访问
ALTER DATABASE [DMS-QA] SET MULTI_USER;
GO
步骤5:验证迁移结果
- 检查数据库状态:
SELECT name, state_desc,compatibility_level
FROM sys.databases
WHERE name = 'DMS-QA';
预期输出:ONLINE
- 验证文件位置:
SELECT name,physical_name AS [当前路径],CASE type WHEN 0 THEN 'C:\SQLData\' + name + '.mdf'WHEN 1 THEN 'C:\SQLLog\' + name + '.ldf' END AS [预期路径]
FROM sys.master_files
WHERE database_id = DB_ID('DMS-QA');
步骤6:设置兼容级别(可选)
-- 保持2008R2兼容模式
ALTER DATABASE [DMS-QA]
SET COMPATIBILITY_LEVEL = 100; -- 或使用2012新特性
ALTER DATABASE [DMS-QA]
SET COMPATIBILITY_LEVEL = 110;
- 兼容级别对照
100
=SQL Server 2008/R2
110
=SQL Server 2012
步骤7:空间监控
2.7.1 PowerShell磁盘监控
while ($true) {$disk = Get-Volume -DriveLetter C[PSCustomObject]@{Time = Get-Date -Format "HH:mm:ss"FreeGB = [math]::Round($disk.SizeRemaining/1GB, 2)TotalGB = [math]::Round($disk.Size/1GB, 2)PercentFree = [math]::Round(($disk.SizeRemaining/$disk.Size)*100, 2)}Start-Sleep -Seconds 300 # 每5分钟检查一次
}
三、故障排除指南
问题现象 | 诊断方法 | 解决方案 |
---|---|---|
还原失败 | EXEC xp_readerrorlog; | 查看详细错误日志 |
权限拒绝 | icacls "C:\SQLData" | 重新执行权限命令 |
文件损坏 | RESTORE VERIFYONLY FROM DISK='C:\SQLBackups\DMS-QA.bak'; | 重新获取备份 |
空间不足 | Get-Volume -DriveLetter C | 清理磁盘或扩容 |
用户孤立 | EXEC sp_change_users_login 'Report'; EXEC sp_change_users_login 'Auto_Fix', '用户名'; |
四、最佳实践建议
4.1 迁移后操作
-
立即执行完整备份:
BACKUP DATABASE [DMS-QA] TO DISK='C:\SQLBackups\DMS-QA_FULL.bak'
-
更新统计信息:
EXEC sp_updatestats
-
重建索引:
ALTER INDEX ALL ON [Schema.Table] REBUILD
-
安全加固:
REVOKE CONNECT FROM PUBLIC; -- 限制公共访问 DROP USER [旧服务器用户]; -- 清理孤立用户
-
性能监控:
-- 开启基础监控 SELECT * FROM sys.dm_os_performance_counters WHERE counter_name IN ('Page life expectancy', 'Batch Requests/sec');
迁移成功标志:数据库状态为
ONLINE
,应用程序可正常连接,数据验证通过。
本方案仅为记录本人从 SQL Server 2008R2 迁移到 SQL Server 2012 的一些过程关键步骤。仅做为参考。迁移成功与否视个人而定,不保证每个人均一次成功。
按照此方案操作,理论上可在30分钟内完成从 SQL 2008R2 到 SQL 2012 的无损迁移。
如文档整理存在疏漏之处,欢迎提出!