SQL SERVER 数据库升级操作流程

已于 2024-01-18 08:36:22 修改
阅读量1.5w 收藏 55
点赞数 13
分类专栏： SQL工作遇到問題 文章标签： 数据库开发 数据库架构
于 2021-08-10 10:45:26 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_24886681/article/details/119561447
版权
本文详细介绍了如何将运行在Windows Server 2008上的SQL Server 2008数据库迁移到Windows Server 2019并升级至SQL Server 2019的过程。包括前期准备、数据库还原、版本升级、异常处理等关键步骤，涉及系统数据库的恢复、服务配置、业务数据库还原以及升级过程中可能遇到的问题和解决办法。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

目的：生产环境变更，需要将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、报表服务安装详细步骤

安装 SQL Server Reporting Services - SQL Server Reporting Services (SSRS) | Microsoft Learn

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 = ''

SQL Server升级的两种方式
ElServer的博客
10-01 2346
SQL Server是一种关系型数据库管理系统，随着技术的不断发展，升级SQL Server成为了许多组织和企业的需求之一。在进行升级之前，我们需要了解SQL Server升级的两种常见方式，以及相应的源代码示例。无论选择哪种升级方式，都建议在升级之前进行充分的备份，并在升级过程中监控和测试以确保数据的完整性和系统的稳定性。在新服务器上运行SQL Server升级向导。安装程序将自动执行必要的操作，例如更新系统数据库、升级用户数据库等。安装程序将提供不同的升级选项，例如直接升级、新实例升级等。
SQLSERVER 升级版本的方法
weixin_30572613的博客
09-07 7217
1. 以SQLSERVER2014为例说明 SQLSERVER升级版本的方法, 也适用于evaluation 版本超过180天之后的处理. 2. 打开所有的应用 看到有一个 sqlserver2008的安装中心 还有一个 sqlserver 2014 的安装中心 这里显然 选择 sqlserver2014 的安装中心.(虽然是废话 但是我自己也点错过) 3. 选择维护 然后选择版本升级...
SQLServer数据库升级[归纳].pdf
10-12
SQLServer数据库升级[归纳].pdf
SQL Server数据库升级与迁移：平稳过渡的艺术
2401_85761762的博客
07-28 1011
在数据库的生命周期中，升级和迁移是不可避免的操作。无论是升级到SQL Server的新版本，还是迁移到新的服务器或云环境，都需要谨慎处理以确保数据的完整性和业务的连续性。本文将详细介绍SQL Server数据库版本升级和迁移的注意事项，并提供相应的代码示例。
MySQL数据库的版本升级详解
最新发布
zhangxueyi的专栏
03-07 1737
MySQL数据库的版本升级需要谨慎规划，以确保数据安全和服务连续性
SqlServer 数据库通用升级方法
dbyoung的Delphi专栏
04-11 1361
sqlserver 数据库通用升级方法
sqlserver 数据库版本升级方案
lvting666的博客
04-08 1176
@Slf4j public class DatabaseUtil { private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; private static String DATABASE_URL = "jdbc:sqlserver://localhost:1433;DatabaseName=xxx1"; // 原始数据源 private static Str.
sql数据库升级oracle,ORACLE数据库升级步骤
weixin_39699121的博客
04-08 156
一、对ORACLE数据库进行备份二、确认ORACLE数据库的Home目录安装路径三、关闭数据库：SQLPLUS/NOLOGCONN / AS SYSDBASHUTDOWN IMMEDIATE停止所有的ORACLE运行服务四、解压安装包,将安装包改成用户为用户所有,之前已是就不用改了.chown -R oracle:oinstall Disk1chown -R oracle:oinstall Dis...
mysql数据库升级sqlserver_教你轻松学会升级SQLServer数据库系统_MySQL
weixin_34401781的博客
02-05 442
bitsCN.com当数据库服务器变得十分繁忙导致性能下降时，你会怎么办?购买更多的硬件升级你的服务器，还是重新考虑数据库服务器设计使得数据库平台具备良好的可升级性呢?如果你在一个相当大的商店工作过，你很可能偶尔遇到过这些问题。有时答案是简单地查看一下当前的应用以及改进一些设计来提高性能。分析阶段在你决定升级你的数据库环境之前，你应该制定一个性能标准，然后，对查询和模式做些修改，对照你制定的标准看...
SQLserver 数据库表结构对比工具
01-17
《SQL Server数据库表结构对比工具详解》 在SQL Server数据库管理中，确保数据库表结构的一致性至关重要。尤其是在多环境、多版本之间进行同步时，表结构的差异可能导致应用程序出现问题或者数据丢失。为了应对这一...
50种方法巧妙优化你的SQLServer数据库.doc
04-24
标题中的“50种方法巧妙优化你的SQLServer数据库”指的是针对SQL Server数据库进行性能提升的各种策略和技巧。这些方法旨在改善数据库的响应时间、吞吐量以及资源利用率，以满足日益增长的业务需求。 描述中提到的...
SQL_SERVER_2008升级SQL_SERVER_2008_R2两种办法
01-17
今天将由于需要就将我的SQL 2008升级到SQL 2008 R2. 说到为什么要升级是因为，从另一台机器上备份了一个数据库，到我的机器上还原的时候提示“System.Data.SqlClient.Sqlerror:该数据库是在运行版本10.50.2500的服务器上备份的，该版本与此服务器（运行版本10.00.1600）不兼容。请在支持该备份的服务器上还原数据库，或者使用与此服务器兼容的备份。” 经过一番查证得知： 所谓的10.00.1600其实就是SQL 2008 10.50.1600其实就是SQL 2008 R2 10.50.2500其实就是SQL 2008 R2 SP1 这样一来基本就明了了。 我得升级自己的数据库到SQL 2008 R2 SP1。 这篇文章就把我先升级到SQL 2008 R2的过程详细记录下来，下一篇文章会介绍升级R2到R2 SP1. 第一步：准备安装程序。 首先SQL 2008是安装好的 因此只需要下载SQL 2008 R2安装程序，下载地址如下：http://care.dlservice.microsoft.com/dl/download/1/E/6/1E626796-588A-495C-917B-321093FB98EB/2052/SQLFULL_x86_CHS.exe?lcid=2052&ptype=pcare 第二步：升级开始。SHOW TIME!!! 1、如果是从SQL2008升级到SQL2008R2那么只能选择左侧“安装”对应右侧“从SQL SERVER 2000，SQL 2005或SQL 2008升级”这个选项来升级。 大家也注意到有一个“维护”但是这个选项是“从SQL 2008其他例如EXPRESS版本升级” 在此，要区分清楚。 2、安装程序支持规则检查。通过之后就继续下一步。 3、输入产品密钥，这个地方，你的密钥是什么版本的，装完之后你的数据库就是什么版本：像企业版，开发版等等等。 4、许可条款必须接受，不然人家不让用啊~~~ 5、安装程序支持文件。 6、程序支持规则安装完成后程序会自动检测安装的文件是否满足需求。 7、当你的服务器中只有一个实例的时候，你很难听到实例这个词，人们都直接叫他“数据库”了。只有在服务器上安装的实例数目超过两个的时候你才会听到“实例”二字比较多。 但是“实例”肯定是学习数据库过程中必须熟知的东东！！！ 此处，选择你想要升级的实例。下一步就行了。 8、以前安装过的组件（component）都会出现在这里，不用管，以前有的，现在当然也要有，闭着眼睛点击“下一步”吧！ 9、因为是升级安装，所以你以前的实例名字是什么，这里还是什么。继续“下一步”。 10、不废话，“下一步”。 11、是否发送错误报告，随便，下一步。 12、“下一步”。 13、点击“升级”按钮进行升级，中途等待大约20-30分钟就完成了。 升级过程中如图： 14、安装成功要求重新启动。重启吧没的说！！ 15、重启完毕，启动SSMS(SQL SERVER MANAGEMENT STUDIO)在登陆界面上就会立马察觉到不同，SQL 2008变成了SQL 2008 R2. 16、连接上数据库引擎，在左侧导航栏就可以看到你数据库的版本了。这次变成了10.50.1600。如果还要升级到10.50.2500。那就继续安装一个升级补丁吧！ 至此，10.00.1600升级10.50.1600已经成功完成。 总结一下： 10.00.1600就是SQL SERVER 2008 10.50.1600就是SQL SERVER 2008 R2 这篇博文里讲的是升级安装，如果你要进行全新安装，请按照如下操作： 一、卸载SQL SERVER 2008，一定要卸载，数据库不像其他常用的应用程序（那些程序会自动检测是否有旧版本，然后给你卸载并升级）。 另：对SQL来说，如果你安装好之后，再进行一次安装的话，实际上安装的是另外一个实例。 二、卸载完之后就安装就行了。执行的是安装---全新安装。 其他细节参考我的博文：SQL SERVER 2008安装（在实例、权限等方面，SQL 2008和SQL 2008 R2是一样的）。
SQL Server升级：两款工具解决应用程序问题.
02-24
SQL Server升级：两款工具解决应用程序问题.
SQLSERVER数据库升级脚本图文步骤
12-15
只能远程协助的方式。我特意做了一个脚本，用电话指导客户在SSMS里执行一下脚本就可以了 1.0的数据库跟1.1的数据库的区别是1.1的数据库里的其中一个[CT_OuterCard]表比1.0的多了6个字段，其他所有表都一样 还有存储过程增加了很多，其他都没有改变 首先，先在公司的服务器数据库上生成存储过程脚本，数据库是1.1版本的，下面的图片里没有说明的，都是默认设置，下一步即可 选中数据库-》右键—》任务-》生成脚本 当然，如果你的数据库里有自定义函数的话，也可以勾选函数，如果我们的数据库没有函数，所以。。。 保存到新建查询窗口 这一步做完了，然后编写下面的SQL脚本 代码如下:
SQL Server数据库优化.pdf
09-19
在如今的数据处理领域，SQL Server数据库系统作为微软推出的关系数据库管理系统（RDBMS），由于其强大的功能和稳定性被广泛应用于商业信息系统。然而，随着企业业务数据量的不断增长，SQL Server数据库的性能往往会...
sql Server 数据库对比工具
08-09
SQL Server 数据库对比工具是IT领域中用于管理和比较SQL Server数据库的重要软件。这些工具能够帮助数据库管理员和开发人员高效地进行数据库之间的差异分析，确保数据的一致性和完整性。以下是对这些知识点的详细...
SQLServer2008附加SQLServer2005数据库
08-03
例如，从 SQL Server 2005 升级到 SQL Server 2008 的过程中，如何将原有的 SQL Server 2005 数据库顺利附加到 SQL Server 2008 上就是一个常见的需求。本文将详细介绍如何在 SQL Server 2008 上成功附加 SQL Server...
SQL SERVER数据库升级手册
03-02 137
背景 最近接手很多项目都跟数据库升级有关。感触还是颇深，写个心得，供大家参考，如果有疑问欢迎留言。 为什么升级？ ...
升级sql server数据库 功能规则
weixin_42402228的博客
07-14 98
升级SQL Server数据库 功能规则 SQL Server是一种关系型数据库管理系统，由Microsoft开发。随着技术的不断发展，SQL Server也在不断升级，以提供更加稳定、高效的数据库服务。在进行SQL Server数据库升级时，需要遵循一定的功能规则，以确保数据库升级过程顺利进行，并且不影响现有系统的正常运...
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值