Exporting CLR Assemblies from SQL Server back to .dll files

By:   |   Comments (9)   |   Related: > Common Runtime Language


You have imported an assembly into a SQL Server database to use the CLR functions and stored procedures it contains. However later, you lost the original .dll file and you would like to create the .dll file again from what's in the database. Scripting the assembly using the create script option doesn't help, because it scripts out the assembly as binary sequence instead of creating the .dll file.  In this tip, we look at how you can recreate the .dll file.


There are several possibilities how to achieve the goal and export the assembly back to a .dll file. This tip will show one of the possible ways by using a simple SQL CLR stored procedure that we pass the assembly name and path where we would like to store the .dll file created again.

Getting Information About Assemblies

To be able to export the assemblies, we need to get information about them and of course their binary representation. Information about assemblies and their files are stored in two system table. In the sys.assemblies table information is stored about the assemblies itself, like name, id, permission set, timestamp of creation or latest modification. As a single assembly, can be represented by multiple physical files, a second system table sys.assembly_files exists. This table contains information about individual assembly files and also contains their content (binary representation) in a varbinary(max) field.

To get file names and their binary content for particular assembly we can use the below T-SQL code:

FROM sys.assemblies a
INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id 
 a.name = 'Microsoft.SqlServer.Types'     

CLR Stored Procedure to save assembly

As mentioned above we will use a CLR Stored procedure to do the job.

First we create a new SqlConnection to SQL Server with connection string "context connection=true". This creates the new connection in the context of the current session from which the CLR stored procedure is being called.

Once the connection is created we can create a new SqlCommand which will execute the above T-SQL query to fetch all file names and binary content for the provided assembly name. In a loop we will iterate though all the rows returned by the SqlDataReader and save the binary data for each file using FileStream.

public class AssemblyExporter
    public static void SaveAssembly(string assemblyName, string destinationPath)
        string sql = @"SELECT af.name, af.content FROM sys.assemblies a INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id WHERE a.name = @assemblyname";
        using (SqlConnection conn = new SqlConnection("context connection=true"))   //Create current context connection
            using (SqlCommand cmd = new SqlCommand(sql, conn))
                SqlParameter param = new SqlParameter("@assemblyname", SqlDbType.VarChar);
                param.Value = assemblyName;
                cmd.Connection.Open();  //Open the context connetion
                using (SqlDataReader reader = cmd.ExecuteReader())
                    while (reader.Read()) //Iterate through assembly files
                        string assemblyFileName = reader.GetString(0);  //get assembly file name from the name (first) column
                        SqlBytes bytes = reader.GetSqlBytes(1);         //get assembly binary data from the content (second) column
                        string outputFile = Path.Combine(destinationPath, assemblyFileName);
                        SqlContext.Pipe.Send(string.Format("Exporting assembly file [{0}] to [{1}]", assemblyFileName, outputFile)); //Send information about exported file back to the calling session
                        using (FileStream byteStream = new FileStream(outputFile, FileMode.CreateNew))
                            byteStream.Write(bytes.Value, 0, (int)bytes.Length);

Creating Assembly and Registering Stored Procedure

Once we build the AssemblyExporter assembly, we have to create the assembly in a SQL Server database and create the CLR Stored procedure. As the stored procedure access resources outside of SQL Server (it is saving the assembly files to disk), the assembly needs to be created with EXTERNAL_ACCESS permission set and the database has to be set to TRUSTWORTHY.

USE [TestDB]
CREATE ASSEMBLY [AssemblyExporter]
FROM 'C:\SQLCLR\AssemblyExporter.dll'
CREATE PROCEDURE [AssemblyExporter].[usp_SaveAssembly] (
 @assemblyName [nvarchar](128),  --Assembly Name
 @destinationPath [nvarchar](256) --Destination path for export
EXTERNAL NAME [AssemblyExporter].[AssemblyExporter].[SaveAssembly]

Testing Export

Once the assembly and CLR stored procedure created, we can test it as follows passing in the name of the assembly we want to recreate and the path where we want the .dll file created.

EXECUTE [AssemblyExporter].[usp_SaveAssembly] 
   @assemblyName = 'Microsoft.SqlServer.Types'
  ,@destinationPath = 'C:\SQLCLR'

Please note, the current implementation accesses the disk drive under the credentials of the account the SQL Server service is running. Therefore to be able to export the assembly, the SQL Server service account needs appropriate access to the destination directory.

Next Steps
  • Take a look on MSDN on sys.assemblies (Transact-SQL) and sys.assembly_files (Transact-SQL).
  • Don't forget to check CLR Stored Procedures on MSDN.
  • You can download a complete sample Visual Studio 2013 project here. For easier deployment create a C:\CLRSQL directory prior building the project. Project has a post build action which automatically copies compiled AssemblyExporter.dll file to this directory for easier deployment. You can use the script in AssemblyExporter.sql for easy creation of the assembly and CLR stored procedure.
  • You can further elaborate on the sample and update it so the assembly is saved under the credentials of current user instead of the SQL Server service account. For this purpose take a look on the SqlContext.WindowsIdentity Property on MSDN.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Pavel Pawlowski Pavel Pawlowski is Lead BI Specialist in Tieto with focus on Microsoft SQL Server and Microsoft BI platform.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, April 30, 2021 - 1:34:44 AM - Pavel Pawlowski Back To Top (88630)

the exact format for the AS EXTERNAL NAME depends how you create your assembly.

if you put the AssemblyExporter class inside AssemblyExporter name space (name space is automatically added to newly created classes in VS), then you will need you statement AS EXTERNAL NAME [AssemblyExporter].[AssemblyExporter.AssemblyExporter].[SaveAssembly].

If you put the AssemblyExporter class out of any name space, then it will be enough EXTERNAL NAME [AssemblyExporter].[AssemblyExporter].[SaveAssembly].

The format for EXTERNAL NAME is: [AssemblyName].[NameSpace.Class].[Method].

Thursday, April 29, 2021 - 2:03:05 PM - Phillip Back To Top (88625)
To create the procedure, I had to run the following:

CREATE PROCEDURE [AssemblyExporter].[usp_SaveAssembly] (
@assemblyName [nvarchar](128), --Assembly Name
@destinationPath [nvarchar](256) --Destination path for export
AS EXTERNAL NAME [AssemblyExporter].[AssemblyExporter.AssemblyExporter].[SaveAssembly]

Tuesday, January 8, 2019 - 1:57:27 AM - Jeff Back To Top (78658)


Help me.

Severity Code Description Project File Line Suppression State

ErrorCS0103The name 'FileMode' does not exist in the current context

Error CS0103 The name 'Path' does not exist in the current context

Error CS0246 The type or namespace name 'FileStream' could not be found (are you missing a using directive or an assembly reference?)

Error CS0246 The type or namespace name 'FileStream' could not be found (are you missing a using directive or an assembly reference?)

Wednesday, July 25, 2018 - 6:57:51 PM - Sergio Back To Top (76830)

 Hi I created the CLRSQL folder in c: then i run the sql commands from the assemblyexporter zip and run the last command wich is EXECUTE [AssemblyExporter].[usp_SaveAssembly] 

   @assemblyName = 'MDLUpdateTrigger'

  ,@destinationPath = 'C:\CLRSQL'


But nothing creates the dll. Please help

Wednesday, January 15, 2014 - 7:51:54 AM - Pavel Pawlowski Back To Top (28089)

If you are interested only in classes, methods and their signatures, then you can use the Object Browser in Visual Studio. Simply choose View/Object Broser, and then click the three dots next to the Browse field and select your .dll.

If you would like to analyze also the sources, then you will have to refactor it e.g. using Redgate's .NET Reflector or free ILSpy.

Wednesday, January 15, 2014 - 2:08:45 AM - zorro-cool Back To Top (28080)

Very interesting article! Thank you! It's interesting for me are there any ways to get any information about the source code of the dll (name and signature of methods e. g.) and to obtain this info in your clr?

Monday, January 6, 2014 - 12:20:40 PM - Pavel Pawlowski Back To Top (27969)

@Lyndon, exactly for this purposes it is very useful. You can do a security review of third party code in assemblies for which you do not have sources.

Monday, January 6, 2014 - 10:33:20 AM - Lyndon Back To Top (27968)

It might also be useful if you want to see the code of the functions in someone else's CLR assembly. Dump it out to a dll and then open it up in reflector.

Monday, January 6, 2014 - 5:05:45 AM - Graham Bull Back To Top (27964)

While it's useful to know that you can do this, you should never actually need  to do this. If you've lost the original DLL, then you can just go off and build a new one. If you haven't got the source code any more, then there's a failure in your processes.

get free sql tips
agree to terms