I recently had an Sql Server 2014 r2 server I needed to migrate to Sql Server 2019 r2 with a set of CLR assemblies on it. My plan is to detach each database and reattach them on the new box. The assemblies are transferred along with the database, but they won’t run. I went through the following steps to run them.

Step 1. Click on Programmability, Assemblies under the Database in Sql Server Management Studio.

Step 2: Right click and choose Script Assembly as Create to New Query Editor Window

you’ll get something that looks like this

CREATE ASSEMBLY [Microsoft.SqlServer.Types]

from   XXXXXX

with permission_set=unsafe

Step #3, run the query select * from sys.assemblies you’ll get something that looks like this

Step #4 You have to make the assemblies trusted. Run the following code. Substitute the XXXXX where indicated and the clr_name from above

–run from create assembly after from copied to @asmbin
USE master;
GO
DECLARE @clrName nvarchar(4000) = ‘microsoft.sqlserver.types, version=15.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil’
DECLARE @asmBin varbinary(max) = XXXXXXXX
DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES(‘SHA2_512’, @asmBin);

EXEC sys.sp_add_trusted_assembly @hash = @hash,
@description = @clrName;

go

Step #5. Here’s the query to list the trusted assemblies (I put XXXX for hash,  you can take that out)

SELECT ‘XXXX’ as hash, description, create_date
FROM sys.trusted_assemblies

run as

SELECT hash, description, create_date
FROM sys.trusted_assemblies

with these results