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

posted @ 2022-11-19 13:27 爱学习滴小朋友阅读(26) 评论(0) 编辑收藏举报

刷新页面返回顶部

MR.刘的日常点滴

SQLSERVER 2012迁移实施方案

一、概述

1.1 前提

1.1.1 基础信息

1.1.2 安装目标库的SQLSERVER

1.1.3 设置目标备份目录

1.2 指导原则：

二、备库及恢复业务库

2 .1 全量备份用户库--192.168.15.14

2.2 差异备份用户库--192.168.15.14

2.3 全量恢复业务库语法样例

2.4 差异恢复业务库语法样例

2.5 系统库的备份与恢复

2.5.1 全量备份系统库

2.5.2 还原系统库

三、监听端口的确认

四、脚本

4.1 full_restore_recovery_with norecovery

4.2 diff_restore_recovery

4.3 SSISDB master key的恢复

4.3.1 源库service master key和master key的的备份

4.3.2 目标库上还原service master key和master key

五、常见问题

六、割接注意事项

公告

MR.刘的日常点滴

SQLSERVER 2012迁移实施方案

一、概述

1.1 前提

1.1.1 基础信息

1.1.2 安装目标库的SQLSERVER

1.1.3 设置目标备份目录

1.2 指导原则：

二、 备库及恢复业务库

2 .1 全量备份用户库--192.168.15.14

2.2 差异备份用户库--192.168.15.14

2.3 全量恢复业务库语法样例

2.4 差异恢复业务库语法样例

2.5 系统库的备份与恢复

2.5.1 全量备份系统库

2.5.2 还原系统库

三、监听端口的确认

四、脚本

4.1 full_restore_recovery_with norecovery

4.2 diff_restore_recovery

4.3 SSISDB master key的恢复

4.3.1 源库service master key和master key的的备份

4.3.2 目标库上还原service master key和master key

五、常见问题

六、割接注意事项

公告

二、备库及恢复业务库