SQL Server升级失败并返回错误 6528

  • 项目

本文可帮助你排查和解决安装累积更新 (CU) 或 Service Pack (SP) for Microsoft SQL Server 时发生的错误 6528。 运行数据库升级脚本时发生错误。

症状

为SQL Server应用 CU 或 SP 时，你注意到安装程序报告SQL Server错误日志中的以下错误消息之一：

SQL server failed in 'Script level upgrade' with the following error:
Error: 50000, Severity: 16, State: 127.
Cannot drop the assembly 'ISSERVER', because it does not exist or you do not have permission.
Error: 50000, Severity: 16, State: 127.
Cannot drop the assembly 'ISSERVER', because it does not exist or you do not have permission.

Creating function internal.is_valid_name
Error: 6528, Severity: 16, State: 1.
Assembly 'ISSERVER' was not found in the SQL catalog of database 'SSISDB'.

Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 6528, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous error log entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
SQL Server shutdown has been initiated.

原因

出现此问题的原因可能是尝试应用 CU 或 SP 的 SQL Server 实例缺少程序集 ISSERVER。

有关在 CU 或 SP 安装期间运行的数据库升级脚本的详细信息，请参阅 对应用更新时升级脚本失败进行故障排除。

解决方案

请按照以下步骤解决此问题：

  1. 使用跟踪标志 902 启动SQL Server。

  2. 使用以下查询检查程序集 ISSERVER 是否在数据库中 SSISDB ：

    Use SSISDB
GO
SELECT * FROM sys.assemblies WHERE name = 'ISSERVER'

    或者，可以通过在 SQL Server Management Studio (SSMS) 中展开数据库>SSISDB>可编程性>程序集>ISSERVER 来检查它。

  3. 检查位置 C：\Program Files\Microsoft SQL Server\<VersionNumber>\DTS\Bin，查看程序集 Microsoft.SqlServer.IntegrationServices.Server.dll 是否存在于 SQL 二进制文件夹中。

  4. 如果程序集位于该文件夹中，但在 sys.assemblies 视图中缺少作为条目，请使用以下查询重新创建它：

    DECLARE @asm_bin varbinary(max);
SELECT @asm_bin = BulkColumn
FROM OPENROWSET (BULK N'C:\Program Files\Microsoft SQL Server\<VersionNumber>\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll',SINGLE_BLOB) AS dll
CREATE ASSEMBLY ISSERVER FROM  @asm_bin  WITH PERMISSION_SET = UNSAFE
ALTER DATABASE SSISDB SET TRUSTWORTHY ON

    这一次程序集 ISSERVER 存在。

  5. 删除跟踪标志 902 并启动服务。