SQL SERVER 数据库升级 *** 作流程

目的：生产环境变更，需要将SQL2008数据库升级为SQL2019版本（高低版本数据库都是专业版），做法搭建目标环境，将目标环境 *** 作系统改成Windows Server2019，安装SQLServer2008（为了使生产环境的备份能够正常还原到目标环境中，后续平滑升级数据库版本）

生产环境：Windows Server 2008 +SQL Server 2008

目标环境：Windows Server 2019 +SQL Server 2008（需升级成2019）

一、前期准备

1、将生产环境中的系统数据库+业务数据全部完整备份

2、将目标环境中的服务器名称设置成与生产环境一致、数据实例名称也一样设置成相同的（若不同会踩很多坑）

      例如你生产环境的服务器名称、实例名称叫A，那么你在目标环境中也将服务器名称、实例名称设置成A

3、系统盘符最好也一致，涉及到系统数据库原始路径

二、数据库还原

1、系统数据库还原

      （1）、进入单用户模式还原数据库

                  ①、 使用netstat -ano |findstr 1433  查看連接數據庫的進程。

                  ②、 停止SqlServer所有服务

                  ③、 以管理员運行cmd，单用户启动SqlServer服务

執行：net start MSSQLSERVER /m 

  ④、 恢复master数据库，使用SSMS（单用户下用管理员账号登录）

          运行以下SQL语句：

RESTORE DATABASE master FROM 
DISK='G:\master\master_backup_Test.bak' with replace

  注：只有Master數據庫在管理員模式下恢復。其他數據庫無需。恢復master后，

sql服務終止，需要重新啟動，以恢復其他數據庫。由于恢复的master数据库里记载其他数据库的路径和现在的路径不一致，这时候重新启动SQLSERVER会失败，必须用跟踪标志3608来启动    net start MSSQLSERVER /f  /m  /T3608

                用sqlcmd连接修改其他数据库的文件路径到现有的正确路径

                sqlcmd /E /S  數據庫名稱(服務器名)

                還原Master數據庫后，其他系統數據庫文件路徑改為原數據庫路徑，

                為保證數據庫正常運行，需修改為現數據庫文件正確路徑。

                用下面的语句修改各个系统数据库的文件路径：

--resource数据库
ALTER DATABASE  mssqlsystemresource  MODIFY 
FILE(NAME=DATA,FILENAME=' 
D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf
)
GO
  
ALTER DATABASE mssqlsystemresource MODIFY FILE(NAME=LOG,FILENAME=' c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf')
GO

--msdb数据库
ALTER DATABASE msdb MODIFY 
FILE(NAME=MSDBData,FILENAME='c:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf')
GO

ALTER DATABASE msdb MODIFY 
FILE(NAME=MSDBLog,FILENAME='c:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf')
GO

--model数据库
ALTER DATABASE model MODIFY 
FILE(NAME=modeldev,FILENAME='c:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf')
GO

ALTER DATABASE model MODIFY 
FILE(NAME=modellog,FILENAME='c:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf')
GO
--tempdb数据库
ALTER DATABASE tempdb MODIFY 
FILE(NAME=tempdev,FILENAME='c:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\tempdb.mdf')
GO

ALTER DATABASE tempdb MODIFY 
FILE(NAME=templog,FILENAME='c:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\templog.ldf')
GO

                 ⑤、恢复msdb数据库

                        关闭SQL Agent 用restore命令恢复msdb

                 ⑥、退出 sqlcmd 指令：exit

                 ⑦、關閉 SQLserver服務

                        sqlcmd指令：NET STOP MSSQLSERVER  或在配置管理中手動關閉

                 ⑧、正常模式啟動MSSQLSERVER(非單用戶模式)

        sqlcmd指令：NET START MSSQLSERVER 或在配置管理器中手動啟動

                 ⑨、在sqlcmd中執行：

--恢復msdb數據庫
RESTORE DATABASE msdb FROM DISK='c:\lab\msdb.bak'
WITH move 'MSDBData' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf',
MOVE 'MSDBLog' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf' ,REPLACE
--恢复model数据库
RESTORE DATABASE model FROM DISK='c:\lab\model.bak'
WITH move 'modeldev' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf',
MOVE 'modellog' 
TO 'C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf' ,REPLACE

              最后：

                     退出 sqlcmd Exit

                     關閉 SQLSERVER服務：NET STOP MSSQLSERVER

                     正常模式啟動SQLSERVER：NET START MSSQLSERVER

2、同步计算机名称及数据库实例名称

select @@servername
select serverproperty('servername')
 
sp_dropserver '数据库服务器名称'
go 
sp_addserver '计算机名称','local'
go

3、还原业务数据库（此步骤简单不做详细说明）

    （1）、在目标环境中建立与生产环境中名称一致的业务数据库

    （2）、将生产环境中的业务数据库完整备份依次还原到目标环境中

三、数据库版本升级

1、安装SQL SERVER 2008 SP4补丁版本（10.0.16000）

     注：SQL server 2008不支持直接升级到2019需要安装补丁，然后分段升级（比较繁琐）

     升级完成用以下指令检测版本

select @@version

2、升级数据库至SQL SERVER 2016 SP2(13.0.5026)

3、升级至SQL SERVER 2019（15.0.2000），升级时需勾选卸载报表服务。

      注 SQL Server 2008报表服务跟SQL Server2019的不兼容所以需要卸载报表服务

 

至此数据升级 *** 作已完成

四、报表服务安装及报表数据库还原 

1、报表服务安装详细步骤

https://docs.microsoft.com/zh-cn/sql/reporting-services/install-windows/install-reporting-services?view=sql-server-ver15

2、报表数据库还原

 五、测试功能是否正常

数据库还原之后相关检测工作
1	还原之后数据库各账号是否能够正常使用
2	系统数据库还原之后各项配置参数是否有异常
3	在SQLserver配置管理器中开启SQLserver依赖的各项服务及配置启动TCP/IP协议
4	链接服务器是否正常运行（测试连接服务器的增刪改及在分布式事物中是否正常运行）
5	各个代理JOB是否正常运行
6	发布订阅是否能够正常创建及运行
7	有些链接服务器采用服务器名称创建、所以注意生产环境中系统盘下Host文件中的映射需要导入目标环境
8	配置并开启分布式事物协调器（DTC服务）
9	其他补充（欢迎大家提出补充）

 六、还原过程中常见异常处理方式

（1）服务器更换完成之后链接服务器密码失效问题

错误代码：15466   解密过程中出错

解决方法：ALTER SERVICE MASTER KEY FORCE REGENERATE，重新生成本地服务器的主密钥

在移机前先备份服务器主秘钥（Service Master Key）  BACKUP SERVICE MASTER KEY TO FILE = 'C:\SMK.bak'       ENCRYPTION BY PASSWORD = 'AnyPasswordYouLike' RESTORE SERVICE MASTER KEY FROM FILE = 'C:\SMK.bak'       DECRYPTION BY PASSWORD = 'AnyPasswordYouLike'  FORCE   (注：如密码不符合密码策略，则将以上passwork设置为繁琐密码)

相关解释： a、服务器主密钥（Service Master Key）,位于层次结构的最顶端，并且在安装SQL Server时自动创建，用于加密系统数据、链接的服务器登录名以及数据库主密钥。在第一次通过SQL Server使用服务主密钥来加密证书、数据库主密钥或链接的服务器主密码时，服务主密钥会自动生成，并且使用SQL Server服务账户的Windows证书来生成它。如果必须改变SQL Server服务账号，微软建议使用SQL Server配置管理器，因为这个工具将执行生成新服务主密钥需要的合适的解密和加密方法，而且可以使加密层次结构保持完整。服务主密钥也用于加密其下的数据库主密钥。 　　b、数据库主密钥（Database Master Key）,用于加密证书，以及非对称密钥和对称密钥。所有数据库都可以只包含一个数据库主密钥，在创建它时，通过服务主密钥对其加密。创建非对称密钥时，可以决定在加密非对称密钥对应的私钥是否包含密码。如果示包含密码，将使用数据库主密钥来加密私钥。

（2）job无法执行，提示未在数据库中启用Service Broker 消息传递功能

错误代码：14650  未啟用Service Broker消息传递功能

解决方法：在SERVER配置管理器中查看ServiceBorker是否开启，如果已开启但还是报14650错误那么执行以下语句 SELECT name,is_broker_enabled FROM sys.databases WHERE name = 'msdb' 查看is_broker_enabled的值是否為1 若不等于1则执行以下语句： ---执行以下语句前先关闭 SQL Agent  USE master； GO ALTER DATABASE MSDB SET ENABLE_BROKER; GO 再执行 SELECT name,is_broker_enabled FROM sys.databases WHERE name = 'msdb' 此时is_broker_enabled =1 在执行相关JOB看看错误是否消失

（3）SQL Server Agent（已停用Agent XPs）。

1.检查SQL Server Agent服务是否打开 2  sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE WITH OVERRIDE GO 执行完刷新数据库

（4）刪除distribution（发布订阅系统数据库）

在目标环境中生产环境的distribution库无法直接还原上去 故需要删除distribution库，并且还原之后删除原有的发布订阅进行新建，然后重新创建发布订阅生产新的distribution库，手动删除报错，所以用以下指令进行删除 use master; go exec sp_dropdistributiondb @database = N'distribution' go

 

（6）恢复数据库后，某些Job或管理计划无法删除或执行

在目标环境中某些还原后的job或维护计划提示与数据库连接失败，导致无法执行或删除，需要进行删除重建，所以执行以下SQL语句 use [msdb]  declare @job_name varchar(100)  set @job_name = N'BackupBSMASTER.每天晚上差異備份' --注：job_name为维护计划对 ?的job name  delete sysmaintplan_log from  sysmaintplan_subplans as subplans   inner  join sysjobs_view as syjobs on subplans.job_id = syjobs.job_id   inner  join sysmaintplan_log on  subplans.subplan_id =sysmaintplan_log.subplan_id  where (syjobs.name = @job_name) --删除代理的作业 delete  sysjobschedules from  sysjobs_view v   inner join  sysjobschedules o on  v.job_id=o.job_id  where  v.name=@job_name --删除子计 ¦E delete  sysmaintplan_subplans from  sysmaintplan_subplans  as subplans  inner join sysjobs_view as  syjobs  on subplans.job_id = syjobs.job_id  where  (syjobs.name = @job_name) --删除作业 delete from  msdb.dbo.sysjobs_view where name = @job_name  --刪除維護計劃 1.查看"维护计划"对象的ID use msdb select * from sysmaintplan_plans select * from sysmaintplan_log select * from sysmaintplan_subplans 2.根据ID号删除相应 ªº"维护计划" delete from sysmaintplan_log where plan_id = '' delete from sysmaintplan_subplans where subplan_id = '' delete from sysmaintplan_plans where id = ''