CLR function migration how to extract code from assembly

Question

I need to migrate instance of SQL Server and found that I don't have a source code for a few CLR functions anywhere, I see functions in assembly and know their logic:

select * from sys.assembly_modules

Is it possible to extract source code from it, so I will store it in newly added TFS.

Actually now main task is to have same CLR functions on a new server, so probably just migrating assemblies will do the job? Or this will require do some extra steps?

And on step2 I will probably will try to recreate source code if reverse engineering won't work.

Thanks

Read: https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/deploying-clr-database-objects?view=sql-server-ver15#deploying-the-assembly-to-production-servers — David Browne - Microsoft, Jul 01 '21 at 23:34
Does this answer your question? [Extracting a .NET Assembly from SQL Server 2005](https://stackoverflow.com/questions/4103406/extracting-a-net-assembly-from-sql-server-2005) — Dale K, Jul 01 '21 at 23:37
Probably, to meet your requirements, you'll need to create another CLR assembly with code to extract the CRL assemblies; recommended lectures is commend above by @DaleK or this article [Exporting CLR Assemblies from SQL Server back to .dll files](https://www.mssqltips.com/sqlservertip/3126/exporting-clr-assemblies-from-sql-server-back-to-dll-files/) — Antonio Leonardo, Jul 01 '21 at 23:43

score 0 · Answer 1 · answered Jul 01 '21 at 23:33

An SQLCLR function is part of an assembly. An assembly can contain multiple functions. It can (in theory) be made up of multiple files, although that is unusual.

You can get the actual DLL files with this

SELECT
  a.name AssemblyName,
  f.name FileName,
  f.content Dll
FROM sys.assemblies a
JOIN sys.assembly_files f ON f.assembly_id = a.assembly_id
-- if you want to filter to certain SQLCLR functions
/*
WHERE a.assembly_id IN (
    SELECT *
    FROM sys.assembly_modules m
    WHERE m.object_id = OBJECT_ID(N'YourFunction')
)
*/

You can de-compile the DLLs with your favorite .NET decompiler.

If you want to just transfer them, you can commit them to source control as CREATE ASSEMBLY FROM followed by the binary data

score 0 · Answer 2 · answered Jul 02 '21 at 16:57

Making Charlieface's second recommendation more explicit, just transfer the assembly. You can do this through SSMS by choosing the assembly in Object Explorer and selecting "Script Assembly as..." from the context menu. Of course, since that just uses SMO on the back end, you can also do this with a little scripting. Here's a quick-and-dirty powershell script (`Get-DbaDatabase coming from the always delightful dbatools module).

$db = Get-DbaDatabase -SqlInstance yourServer -Database yourDB;
foreach ($a in ($db.Assemblies | where IsSystemObject -eq $false)){
    $a.Script();
}

CLR function migration how to extract code from assembly

2 Answers2