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的相关服务