SQLSERVER 2012迁移实施方案
一台SQLSERVER 2012企业版的数据库需要迁移到另一台机器上，具体情况如下：
-
登陆账号众多，有数百个。
-
job众多，有数百个。
-
DB库的数量多，数据大，DB总大小达10T多，DB数量90多个（不包括系统库）。
-
系统库、SSISDB库也需要迁移。
1.1 前提
1.1.1 基础信息
| 源库IP | 源库主机名 | 目标IP | 主机名 | 备注 |
|---|---|---|---|---|
| 192.168.15.14 | DB14 | 192.168.15.98 | DB98 | SQLSERVER 2012-->2012 |
1.1.2 安装目标库的SQLSERVER
-
目标库SQLSERVER安装版本需要与源相同包括小版本号，因为涉及到系统库的迁移。
-
目标库SQLSERVER安装的组件需要与源相同。
1.1.3 设置目标备份目录
设置的目标库的共享备份目录，这样就可以把源库直接备份到目标机器上。
Y-- this turns on advanced options and is needed to configure xp_cmdshell
EXEC sp_configure 'show advanced options', '1'
RECONFIGURE
-- this enables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '1'
RECONFIGURE
EXEC XP_CMDSHELL 'net use Y: \\192.168.15.98\sharebackup /user:DB98\用户名 密码'
EXEC XP_CMDSHELL 'Dir Y:'
RECONFIGURE;
GO
1.2 指导原则：
数据库迁移步骤：
1． 分离（detach）或备份（backup）数据库；
2． 将旧实例上的系统数据库（master, msdb, tempdb, model）复制到新服务器的SQL Server数据文件路径，建议使用与旧实例相同的文件路径；（master库采用restore的方法，msdb，model采用直接文件替换的方法）
3． 在新的实例上面更改master数据库里记录的系统数据库文件路径（如果新旧实例的系统数据库文件相同，则跳过此步）；
4． 启动新实例；
5． 附加（attach）或恢复（restore）用户数据库；
6． 完成迁移。
二、 备库及恢复业务库
2 .1 全量备份用户库--192.168.15.14
DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
-- specify database backup directory
SET @path = '\\192.168.15.98\sharebackup\'
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName WITH STATS=10, COMPRESSION
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
2.2 差异备份用户库--192.168.15.14
DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
-- specify database backup directory
SET @path = '\\192.168.15.98\sharebackup\'
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.DIF'
BACKUP DATABASE @name TO DISK = @fileName with DIFFERENTIAL,STATS=10, COMPRESSION
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
2.3 全量恢复业务库语法样例
单独恢复新位置示例
RESTORE DATABASE FineReport FROM DISK = 'E:\sharebackup\FineReport.BAK'
WITH MOVE 'FineReport' TO 'E:\data\FineReport.mdf',
MOVE 'FineReport_log' TO 'E:\data\FineReport_log.ldf',NORECOVERY
GO
RESTORE DATABASE BCCPortal FROM DISK = 'E:\sharebackup\BCCPortal.BAK'
WITH MOVE 'HS' TO 'E:\data\HS.mdf',
MOVE 'HS_log' TO 'E:\data\HS_log.ldf',NORECOVERY
GO
2.4 差异恢复业务库语法样例
差异单独恢复新位置并RECOVERY示例：
RESTORE DATABASE FineReport FROM DISK = 'E:\sharebackup\FineReport.DIF'
WITH MOVE 'FineReport' TO 'E:\data\FineReport.mdf',
MOVE 'FineReport_log' TO 'E:\data\FineReport_log.ldf',RECOVERY
GO
RESTORE DATABASE BCCPortal FROM DISK = 'E:\sharebackup\BCCPortal.DIF'
WITH MOVE 'HS' TO 'E:\data\HS.mdf',
MOVE 'HS_log' TO 'E:\data\HS_log.ldf',RECOVERY
GO
2.5 系统库的备份与恢复
2.5.1 全量备份系统库
DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name
-- specify database backup directory
SET @path = '\\192.168.15.98\sharebackup\'
-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name IN ('master','model','msdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
2.5.2 还原系统库
1、启数据数据库至单用户模式
2、还原master库
还原顺序：master----->msdb------->model
1）把数据库启动到单用户模式，方法：服务启动参数加“-m”
2）restore master db 把帐户信息restore 回来
restore database master from disk ='H:\sharebackup\master.BAK'with replace
GO
3、还原msdb库
restore database msdb_one from disk ='H:\sharebackup\msdb.BAK'with
move 'MSDBData' TO 'E:\temp\MSDBData.mdf',
move 'MSDBLog' TO 'E:\temp\MSDBLog.ldf'
GO
停止SQLSERVER服务后把msdb当作普通库还原成msdb_one(可以自定义)，还原成功后，把msdb_one分离，然后把目标库的msdb文件用分离出来的msdb_one替换掉。
4、还原model库
restore database model_one from disk ='H:\sharebackup\model.BAK'with replace,
move 'modeldev' TO 'E:\temp\model.mdf',
move 'modellog' TO 'E:\temp\modellog.ldf'
GO
停止SQLSERVER服务后把model当作普通库还原成model_one(可以自定义)，还原成功后，把model_one分离，然后把目标库的model文件用分离出来的model_one替换掉。
注意事项：
当master库无法正常启动后，需要注意查看SQLSERVER的日志，查看tempdb设置是否正确（如tempdb过大，所在磁盘的存储空间过小），导致master库无法启动，修改其路径的方法如下：
---源库系统库之后，记得修改tempdb的路径，不然服务启不起来。
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = 'F:\DATA\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = 'F:\DATA\tempdb.ldf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev02], FILENAME = 'F:\DATA\tempdb02.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev03], FILENAME = 'F:\DATA\tempdb03.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev04], FILENAME = 'F:\DATA\tempdb04.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev05], FILENAME = 'F:\DATA\tempdb05.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev06], FILENAME = 'F:\DATA\tempdb06.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev07], FILENAME = 'F:\DATA\tempdb07.ndf');
ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev08], FILENAME = 'F:\DATA\tempdb08.ndf');
GO
三、监听端口的确认
原服务器的监听端口是1433，所以迁移后，需要确认目标服务器的端口
exec sys.sp_readerrorlog 0, 1, 'listening'
四、脚本
4.1 full_restore_recovery_with norecovery
--1.Baseline
restore database Baseline from disk ='H:\sharebackup\Baseline.BAK' with replace,norecovery,STATS=10,
move 'Baseline_1' TO 'G:\DATA\Baseline_1.mdf',
move 'Baseline_log' TO 'G:\DATA\Baseline_log.ldf',
move 'Baseline_2' TO 'G:\DATA\Baseline_2.ndf',
move 'Baseline_3' TO 'G:\DATA\Baseline_3.ndf',
move 'Baseline_4' TO 'G:\DATA\Baseline_4.ndf',
move 'Baseline_5' TO 'G:\DATA\Baseline_5.ndf',
move 'Baseline_01_data' TO 'G:\DATA\Baseline_01_data.ndf',
move 'Baseline_02_data' TO 'G:\DATA\Baseline_02_data.ndf',
move 'Baseline_03_data' TO 'G:\DATA\Baseline_03_data.ndf'
GO
--2.TMProject
restore database TMProject from disk ='H:\sharebackup\TMProject.BAK' with replace,norecovery,STATS = 10,
move 'TMProject' TO 'G:\DATA\TMProject.mdf',
move 'TMProject_log' TO 'G:\DATA\TMProject_log.ldf'
GO
--3.AutomationProjectDB
restore database AutomationProjectDB from disk ='H:\sharebackup\AutomationProjectDB.BAK' with replace,norecovery,STATS = 10,
move 'AutomationProjectDB' TO 'E:\DATA\AutomationProjectDB.mdf',
move 'AutomationProjectDB_log' TO 'E:\DATA\AutomationProjectDB_log.ldf'
GO
--4.Historical
restore database Historical from disk ='H:\sharebackup\Historical.BAK' with replace,norecovery,STATS = 10,
move 'Historical_1' TO 'E:\DATA\Historica.mdf',
move 'Historical_log_1' TO 'E:\DATA\Historical_log_1.ldf',
move 'Historical_2' TO 'E:\DATA\Historica02.ndf',
move 'Historical_3' TO 'E:\DATA\Historica03.ndf',
move 'Historical_4' TO 'E:\DATA\Historica04.ndf'
GO
4.2 diff_restore_recovery
--1.Baseline
restore database Baseline from disk ='H:\sharebackup\Baseline.DIF' with recovery,STATS=10,
move 'Baseline_1' TO 'G:\DATA\Baseline_1.mdf',
move 'Baseline_log' TO 'G:\DATA\Baseline_log.ldf',
move 'Baseline_2' TO 'G:\DATA\Baseline_2.ndf',
move 'Baseline_3' TO 'G:\DATA\Baseline_3.ndf',
move 'Baseline_4' TO 'G:\DATA\Baseline_4.ndf',
move 'Baseline_5' TO 'G:\DATA\Baseline_5.ndf',
move 'Baseline_01_data' TO 'G:\DATA\Baseline_01_data.ndf',
move 'Baseline_02_data' TO 'G:\DATA\Baseline_02_data.ndf',
move 'Baseline_03_data' TO 'G:\DATA\Baseline_03_data.ndf'
GO
--2.TMProject
restore database TMProject from disk ='H:\sharebackup\TMProject.DIF' with recovery,STATS = 10,
move 'TMProject' TO 'G:\DATA\TMProject.mdf',
move 'TMProject_log' TO 'G:\DATA\TMProject_log.ldf'
GO
--3.AutomationProjectDB
restore database AutomationProjectDB from disk ='H:\sharebackup\AutomationProjectDB.DIF' with recovery,STATS = 10,
move 'AutomationProjectDB' TO 'E:\DATA\AutomationProjectDB.mdf',
move 'AutomationProjectDB_log' TO 'E:\DATA\AutomationProjectDB_log.ldf'
GO
--4.Historical
restore database Historical from disk ='H:\sharebackup\Historical.DIF' with recovery,STATS = 10,
move 'Historical_1' TO 'E:\DATA\Historica.mdf',
move 'Historical_log_1' TO 'E:\DATA\Historical_log_1.ldf',
move 'Historical_2' TO 'E:\DATA\Historica02.ndf',
move 'Historical_3' TO 'E:\DATA\Historica03.ndf',
move 'Historical_4' TO 'E:\DATA\Historica04.ndf'
GO
4.3 SSISDB master key的恢复
4.3.1 源库service master key和master key的的备份
source库--SSISDB：
--1、备份service master key
USE master;
GO
BACKUP SERVICE MASTER KEY TO FILE = 'H:\98key\service_master_ key'
ENCRYPTION BY PASSWORD = 'P@assword123'
GO
--2、备份master key
USE SSISDB
GO
backup master key to file = 'H:\98key\SQL_masterkey' encryption by password = 'P@assword123'
GO
4.3.2 目标库上还原service master key和master key
target库：
--step1.
RESTORE SERVICE MASTER KEY
FROM FILE = 'H:\sharebackup\98key\service_master_key'
DECRYPTION BY PASSWORD = 'P@assword123'
force
GO
--如上面已经成功还原，下面可不需要操作
alter service master key force regenerate
--step 2.
Restore master key from file ='H:\sharebackup\98key\SQL_masterkey'
Decryption by password = 'P@assword123'
Encryption by password = 'P@assword123'
force
--Msg 33094, Level 16, State 1, Line 1
--An error occurred during Service Master Key decryption
--Msg 15466, Level 16, State 9, Line 1
--An error occurred during decryption.
--step3.
open master key decryption by password = 'P@assword123'
alter Master Key Add encryption by Service Master Key
五、常见问题
1、源库差异备份报错，注册表是调哪个值？
[HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\BCDRAGENT]
"USEVSSCOPYBACKUP"="TRUE"
六、割接注意事项
1、差异恢复完成后，需要SSIS的master key恢复，不然无法导出project、无法新增凭据
2、修改主机名和IP地址后，SERVER服务的TCP/IP属性中的IP地址，需要手动更新，不然将无法连接SQLSERVER服务
-
确保系统主机名与实列中的主机一致
在SQLServer 数据库中，当修改了操作系统的主机名时，数据库实例所用的主机名不会自动更新，所以要手动重新配置，否则各种配置依旧会使用旧的主机名，导致各种脚本，程序异常。 处理过程如下：
--查看数据库中的主机名
select @@servername;
--删除旧的主机名
sp_dropserver '原主机名';
--添加新的主机名：
sp_addserver '现主机名','local';
最后重启数据库服务即可；可以再次查询
select @@servername
以验证结果。
3、检查SQLSERFVER的相关服务
喜欢请赞赏一下啦^_^
微信赞赏
支付宝赞赏
