sql server升级打补丁
【1-4】参考自：https://blog.csdn.net/kk185800961/article/details/72436415
SQLServer怎么升级补丁？一直以来打算整理 SQL Server 补丁资料，刚好今天有事醒的早，于是把之前的资料整理一下。在入DBA这坑以来，开始那一两年，甚至只知道补丁安装最新 Service Package （SP）就行了，也曾一度混淆 SP 与 Cumulative Update（CU）。走过的坑，还是整理给后来人吧。
【1】sql server各个补丁版本的概念
首先了解几个定义：
RTM ： 表示 Release to Manufacturing ，这是产品的原始发布版本，当从光盘或 MSDN 下载的默认版本。不过现在下载 SQL Server 版本时，也有集成了 SP 的版本。
Hotfix ：需要修复的某个问题，每年 SQL Server 会出现许多bug或漏洞，这些问题d修复则被定义为 Hotfix。
Cumulative Update（CU）：累计更新包，由 Hotfix 组成。CU 每8个星期发布一次，所以会有 CU1、CU2……等，每个最新的CU版本都包含之前的CU中的Hotfix。 CU 的安装基于原始版本（RTM）或 SP。
Service Package （SP）： SP是集成 Hotfix 最多的包，这些 Hotfix是经过官方完整测试过的。SP 是 SQL Server 产品版本最大的一次补丁文件，每12至18月发布一次。
每一个最新的 CU 都包含之前的 CU 集成的问题修复，每一个 SP 也都包含之前 SP 包含的问题修复。现在看一张图，就知道 SQL Server 是怎么修复补丁了！
以 SQL Server 2014 为例
（为便于理解： RTM 相当于完整备份，SP 相当于差异备份，CU 相当于更小的差异备份，GDR 则相当于备份某张表）
由图可看，如果 SQL Server 2014 刚发布就我们就很快使用，那我们就经常遇到各种可能的问题，所以最好等上一两年再使用，当然有些问题可以忽略。但是，最好打上最新的补丁。
GDR （General Distribution Release）与 CU 不一样，每个最新的CU都包含基于 RTM/SP 以来的累计更新， GDR 仅仅包含严重的更新。
过去我只是用 SP 修复这些问题，但是即使安装最新的 SP 后，后来又确认了一些问题。如上图 SQL Server 2014 SP2 后，也就是现在（截至 2017-04-21），又确认出了 二十多个Hotfix（Cumulative Update 5 for SQL Server 2014 SP2）。也许不久， SQL Server 2014 将会把 SP2及最新的CU 的Hotfix合并为 SP3。
【2】打补丁测试
现在测试：
查看当前版本，好确定需要下载哪些补丁：
主版本为 SQL Server 2014 ，按最上面的图显示，先下载最新的 SP2 ：
SP2详细介绍：SQL Server 2014 Service Pack 2 release information
SP2下载地址： Microsoft? SQL Server? 2014 Service Pack 2 (SP2)
下载文件为 SQLServer2014SP2-KB3171021-x64-CHS.exe ，双机进行安装。
安装完成！再查看版本：
现在变成 SP2 了，已经安装完成，生产环境最好重启一次服务器。
【3】安装GDR（这一步可以省略）
接下来应该安装 Cumulative Update 5 ，此处为了测试，现在安装 SQL Server 2014 Service Pack 2 GDR。
GDR 详细介绍：MS16-136: Description of the security update for SQL Server 2014 Service Pack 2 GDR: November 8, 2016
GDR下载地址：SQL Server 2014 Service Pack 2 GDR 安全性更新 (KB3194714)
GDR下载文件为：SQLServer2014-KB3194714-x64.exe，双机进行安装，操作与安装 SP 一样！
【4】安装CU
现在接着安装 Cumulative Update 5 ，此 CU5 基于 SP2 ，因此前面先安装 SP2 。
CU5 详细介绍：Cumulative Update 5 for SQL Server 2014 SP2
CU5 下载地址：Microsoft? SQL Server? 2014 SP2 最新累积更新 SQL Server 2014 SP2 累积更新包 5 - KB4013098 （当时最新为 CU5）
CU5 下载文件为 SQLServer2014-KB4013098-x64.exe ，双机进行安装，操作与安装 SP 一样！
查看 windows 更新程序，可以看到 SP2、CU5、GDR 三个更新。
对于回滚，可以按反序逐个右键卸载！卸载后必须重启服务器！
若在卸载 Cumulative Update 5 的时候弹出以下提示，报表sharepoint相关，根据提示在SQL Server 2014 的安装包里面找到对应的文件就行。
测试完成！！
测试完成！！
附：
列表出自：Update Center for Microsoft SQL Server
2014详细包参考：https://support.microsoft.com/en-us/help/2936603/sql-server-2014-build-versions
更多详细参考：
Update Center for Microsoft SQL Server
Microsoft SQL Server Version List
【5】生产环境最佳实践（单实例）
【思路】
【基本步骤】
预计时间10-20分钟，事先测试好、准备好下列步骤的具体操作实现与脚本：
1) 提前把升级所需要的文件准备好（提前至少1天：不在停机时间操作）；确认当天凌晨全备均已备份，如果没有则手动全备。（提前至少1小时：不在停机时间操作）；安装好一个相同版本的命名实例
2) 备份登录名，备份服务器角色，获取附加脚本（以便快速恢复）信息
3) 关闭tcp/ip，以此断开所有业务，然后重启数据库服务（2分钟内）
4) 差异备份，数据库offline，避免故障丢失数据。关闭数据库服务（2分钟内）
5) 运行SP4补丁文件，进行安装，然后启动服务（10分钟内）
6) 进行数据库online（2分钟内）
7) 基本连接测试，TCPIP连接测试（2分钟）
8) 通知业务开启，连接操作
【故障预防】
（1）已做：新建了一个同版本命名实例，如果现有需要升级的实例升级出现问题无法启动，则快速利用备份还原、附加分离恢复业务。（只要服务器不是彻底起不来）
（2）已做：防止数据丢失，我做了全备+停机后的差异备，然后还用 offline（也可以换成分离） 的方式获取 mdf 和 ldf。
这样就有了双重保险，如果新实例起不来，则可以利用mdf/ldf 快速附加到操作前的新命名实例恢复。
如果mdf、ldf文件有问题，则可以利用备份还原恢复到操作前的新命名实例，但备份还原如果库大终究需要太多时间；这是备选方案
（3）已做：登录名、作业、附加语句等信息，我们获取这些信息，以便业务快速无感可以恢复，而无需去管其他事件；
基本缺陷：需要停机
致命缺陷：如果机器的操作系统或硬盘出现问题，则是致命问题
对应思考方案：如果是虚拟机，则停机后做一个快照，然后再进行基本步骤操作；但其实可能会需要很多时间，如果只是短短几分钟，务必要做一个
使用镜像、alwayson等技术，无感切换。但其实成本和复杂度高不少；
【正规流程】
（1）提前上机器查看，确定当天凌晨的全备均已备份
查看全备记录
--select
;with t1 as (
SELECT
bs.backup_set_id,
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS [Size],
CAST(DATEDIFF(second, bs.backup_start_date,
bs.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' [TimeTaken],
CASE bs.[type]
WHEN 'D' THEN 'Full Backup'
WHEN 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'TLog Backup'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential Partial'
END AS BackupType,
bmf.physical_device_name,
CAST(bs.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(bs.last_lsn AS VARCHAR(50)) AS last_lsn,
bs.server_name,
bs.recovery_model
,row_number() over(partition by bs.database_name order by backup_start_date desc) as rn
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
where bs.[type]='D'
)
手动运行全备
declare @bak_path varchar(300),@db_name varchar(300),@operate_type varchar(50)
declare @rn int,@rn_count int
declare @flag int
declare @msg varchar(4000)
-- set @bak_path='E:\DB_AUTO_BAK\'
-- create tempdb's new path
DECLARE @drive TABLE(drive_name VARCHAR(100),drive_size BIGINT)
INSERT INTO @drive
EXEC xp_fixeddrives
DECLARE @path VARCHAR(1000),@drive_max VARCHAR(100)
SELECT @drive_max=MAX(drive_name) FROM @drive
set @bak_path=@drive_max+':\DB_AUTO_BAK\'
select 'backup database '+name+' to disk='''+@bak_path+name+'_full_'+convert(char(8),getdate(),112)+'_'+replace(CONVERT(varchar(100), GETDATE(), 24),':','')+'.bak'' with stats=10'
from master.sys.databases
（2）备份登录名，备份服务器角色信息
登录名：导出到C盘
use master
go
if object_id('temp_login_info') is not null
drop table master..temp_login_info
SELECT 'CREATE LOGIN [' + p.name + '] '
+ CASE WHEN p.type IN ( 'U', 'G' ) THEN 'FROM windows '
ELSE ''
END + 'WITH ' + CASE WHEN p.type = 'S'
THEN 'password = '
+ master.sys.fn_varbintohexstr(l.password_hash)
+ ' hashed, ' + 'sid = '
+ master.sys.fn_varbintohexstr(l.sid)
+ ', check_expiration = '
+ CASE WHEN l.is_expiration_checked > 0
THEN 'ON, '
ELSE 'OFF, '
END + 'check_policy = '
+ CASE WHEN l.is_policy_checked > 0
THEN 'ON, '
ELSE 'OFF, '
END
+ CASE WHEN l.credential_id > 0
THEN 'credential = ' + c.name
+ ', '
ELSE ''
END
ELSE ''
END + 'default_database = '
+ p.default_database_name
+ CASE WHEN LEN(p.default_language_name) > 0
THEN ', default_language = ' + p.default_language_name
ELSE ''
END as sql
into master..temp_login_info
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l
ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c
ON l.credential_id = c.credential_id
WHERE p.type IN ( 'S', 'U', 'G' )
--AND p.name NOT IN ( 'sa')
AND p.name NOT LIKE '%##%'
AND p.name NOT LIKE '%NT SERVICE%'
AND p.name NOT LIKE '%NT AUTHORITY%'
and p.type_desc = 'sql_login'
go
EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE;
--启用执行CMD命令
EXEC SP_CONFIGURE 'xp_cmdshell', 1;
RECONFIGURE;
go
EXEC master..xp_cmdshell 'bcp "select * from master..temp_login_info" queryout c:\temp_login_info.sql -c -T'
EXEC SP_CONFIGURE 'xp_cmdshell', 0;
RECONFIGURE;
EXEC SP_CONFIGURE 'show advanced options', 0;
RECONFIGURE;
go
drop table master..temp_login_info;
服务器角色：导出到C盘
use master
go
if object_id('temp_login_srv') is not null
drop table master..temp_login_srv
SELECT
sql='exec master..sp_addsrvrolemember @loginame ='''+r.name+''', @rolename ='''+p2.name+''';'
into master..temp_login_srv
FROM sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.member_principal_id
INNER JOIN sys.server_principals p1 ON p1.principal_id = m.member_principal_id
INNER JOIN sys.server_principals p2 ON p2.principal_id = m.role_principal_id
where r.type_desc = 'sql_login' and r.name!='sa'
go
EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE;
--启用执行CMD命令
EXEC SP_CONFIGURE 'xp_cmdshell', 1;
RECONFIGURE;
go
EXEC master..xp_cmdshell 'bcp "select * from master..temp_login_srv" queryout c:\temp_login_srv.sql -c -T'
EXEC SP_CONFIGURE 'xp_cmdshell', 0;
RECONFIGURE;
EXEC SP_CONFIGURE 'show advanced options', 0;
RECONFIGURE;
go
drop table master..temp_login_srv;
附加脚本：导出到C盘
use master
go
if object_id('temp_attach_db') is not null
drop table master..temp_attach_db
select 'exec sp_attach_db @dbname='''+dbname+''',@filename1='''+mdf+''',@filename2='''+ldf+''';' as sql
into master..temp_attach_db
from (
select db_name(t1.database_id) as dbname,
max(case when type=0 then physical_name else '' end) as mdf,
max(case when type=1 then physical_name else '' end) as ldf
from sys.master_files t1 join sys.databases t2
on t1.database_id=t2.database_id and t1.database_id >4
--and t2.user_access=0 and t2.is_read_only=0 and t2.state=0
group by db_name(t1.database_id)
) t1
go
EXEC SP_CONFIGURE 'show advanced options', 1;
RECONFIGURE;
--启用执行CMD命令
EXEC SP_CONFIGURE 'xp_cmdshell', 1;
RECONFIGURE;
go
select * from master..temp_attach_db
EXEC master..xp_cmdshell 'bcp "select * from master..temp_attach_db" queryout c:\temp_attach_db.sql -c -T'
EXEC SP_CONFIGURE 'xp_cmdshell', 0;
RECONFIGURE;
EXEC SP_CONFIGURE 'show advanced options', 0;
RECONFIGURE;
go
drop table master..temp_attach_db;
（3）关闭数据库服务，以此断开所有业务（2分钟内）
使用sscm关闭tcp/ip协议，以便断开所有非本机连接
Net stop SQLSERVERAGENT
Net stop MSSQLSERVER
Net start MSSQLSERVER
测试 tcp/ip是否真的关闭，可以连接 127.0.0.1，它也是走的tcp/ip，它要是走不通就证明sql server 的tcp/ip 协议关闭了
（4）差异备份，数据库offline，避免故障丢失数据。（2分钟内）
-----《1》差异备份直接运行作业中的差异备份
-----《2》exec offline
declare @sql varchar(max)
set @sql=cast('' as varchar(max))
select @sql=@sql+cast('Alter database '+name+' set offline with rollback immediate;'+char(13)+char(10) as varchar(max))
from sys.databases where database_id >4 and user_access=0 and is_read_only=0 and state=0
exec(@sql)
------《3》CMD=》
Net stop SQLSERVERAGENT Net stop MSSQLSERVER
（5）运行SP4补丁文件，安装，启动服务（10分钟内）
双击已经放到 d:\dba_tools 下的补丁文件
然后记得把相关进程都关掉；这个GDI+ WINDOWS 多半是打开了一些界面如SSCM，perfmon，services等等
（6）进行数据库online（2分钟内）
declare @sql varchar(max)
set @sql=cast('' as varchar(max))
select @sql=@sql+cast('Alter database '+name+' set online with no_wait;'+char(13)+char(10) as varchar(max))
from sys.databases where database_id >4 and user_access=0 and is_read_only=0 and state=6
exec(@sql)
（7）基本连接测试，TCPIP连接测试（2分钟）
在ssmc中，重启tcp/ip协议，注意，它需要重启sql server引擎；
然后再使用ssms，sqlcmd 等 做 127.0.0.1的ip地址连接测试
【升级某个实例，会影响到其他实例吗？不会】
【数据库offline=》online可以吗？可以】
（1）测试数据
create database test1; go use test1; go create table t_test1( id int, num int, str1 varchar(10) ) go insert into t_test1 values(1,1,'a'); insert into t_test1 values(2,2,'b'); insert into t_test1 values(3,3,'c');
（2）offline操作
-----exec offline
declare @sql varchar(max)
set @sql=cast('' as varchar(max))
select @sql=@sql+cast('Alter database '+name+' set offline with rollback immediate;'+char(13)+char(10) as varchar(max))
from sys.databases where database_id >4 and user_access=0 and is_read_only=0 and state=0
exec(@sql)
（3）开始升级
（4）完成后重新启动该实例服务
Net start MSSQLSERVER
Net start SQLSERVERAGENT
启动
（5）online操作
--------exec online
declare @sql varchar(max)
set @sql=cast('' as varchar(max))
select @sql=@sql+cast('Alter database '+name+' set online with no_wait;'+char(13)+char(10) as varchar(max))
from sys.databases where database_id >4 and user_access=0 and is_read_only=0 and state=6
exec(@sql)
【可以只升级某个实例吗？可以】
【快速恢复，保存出附加脚本】
select 'exec sp_attach_db @dbname='''+dbname+''',@filename1='''+mdf+''',@filename2='''+ldf+''';'
from (
select db_name(t1.database_id) as dbname,
max(case when type=0 then physical_name else '' end) as mdf,
max(case when type=1 then physical_name else '' end) as ldf
from sys.master_files t1 join sys.databases t2
on t1.database_id=t2.database_id and t1.database_id >4
--and t2.user_access=0 and t2.is_read_only=0 and t2.state=0
group by db_name(t1.database_id)
) t1
