一、概述

一台SQLSERVER 2012企业版的数据库需要迁移到另一台机器上，具体情况如下：

• 登陆账号众多，有数百个。
• job众多，有数百个。
• DB库的数量多，数据大，DB总大小达10T多，DB数量90多个（不包括系统库）。
• 系统库、SSISDB库也需要迁移。

• 目标库SQLSERVER安装版本需要与源相同包括小版本号，因为涉及到系统库的迁移。
• 目标库SQLSERVER安装的组件需要与源相同。

设置的目标库的共享备份目录，这样就可以把源库直接备份到目标机器上。

Y-- this turns on advanced options and is needed to configure xp_cmdshellEXEC sp_configure 'show advanced options', '1'RECONFIGURE-- this enables xp_cmdshellEXEC sp_configure 'xp_cmdshell', '1' RECONFIGUREEXEC XP_CMDSHELL 'net use Y: \\192.168.15.98\sharebackup /user:DB98\用户名 密码'EXEC XP_CMDSHELL 'Dir Y:' RECONFIGURE;GO1.2 指导原则：

数据库迁移步骤：

1． 分离（detach）或备份（backup）数据库；

2． 将旧实例上的系统数据库（master, msdb, tempdb, model）复制到新服务器的SQL Server数据文件路径，建议使用与旧实例相同的文件路径；（master库采用restore的方法，msdb，model采用直接文件替换的方法）

3． 在新的实例上面更改master数据库里记录的系统数据库文件路径（如果新旧实例的系统数据库文件相同，则跳过此步）；

4． 启动新实例；

5． 附加（attach）或恢复（restore）用户数据库；

6． 完成迁移。

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 directorySET @path = '\\192.168.15.98\sharebackup\' -- specify filename formatSELECT @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 databasesAND state = 0 -- database is onlineAND 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_cursor2.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 directorySET @path = '\\192.168.15.98\sharebackup\' -- specify filename formatSELECT @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 databasesAND state = 0 -- database is onlineAND 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_cursor2.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',NORECOVERYGORESTORE 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',NORECOVERYGO2.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',RECOVERYGORESTORE 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',RECOVERYGO2.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 directorySET @path = '\\192.168.15.98\sharebackup\' -- specify filename formatSELECT @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 databasesAND state = 0 -- database is onlineAND 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_cursor2.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 replaceGO

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'

--1.Baselinerestore 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.TMProjectrestore 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.AutomationProjectDBrestore 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.Historicalrestore 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'GO4.2 diff_restore_recovery

--1.Baselinerestore 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.TMProjectrestore 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.AutomationProjectDBrestore 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.Historicalrestore 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'GO4.3 SSISDB master key的恢复4.3.1 源库service master key和master key的的备份

source库--SSISDB：--1、备份service master keyUSE master; GO BACKUP SERVICE MASTER KEY TO FILE = 'H:\98key\service_master_ key' ENCRYPTION BY PASSWORD = 'P@assword123'GO--2、备份master keyUSE SSISDBGObackup master key to file = 'H:\98key\SQL_masterkey' encryption by password = 'P@assword123' GO4.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'forceGO--如上面已经成功还原，下面可不需要操作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的相关服务