Yes, this is possible. The actual binary representation of the assemblies live
in the SQL catalog for your server. Namely, if you run a join between
sys.assembly_files and sys.assemblies you can get to all the information you
need. The assemblies binary is in the content column of the sys.assembly_files
view.
But in order to extract the binary representation from SQL Server and into a
file on disk you will have to write some .NET code that needs to run on the
same database where the assemblies you refer to are located now. In Visual
Studio start a SQL CLR project and add a class to it with the following code:
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Permissions;
namespace ExtractSqlAssembly {
[PermissionSet(SecurityAction.Demand, Unrestricted = true, Name = "FullTrust")]
public partial class SaveSqlAssembly {
[SqlProcedure]
public static void SaveAssembly(string assemblyName, string path) {
string sql = @"SELECT AF.content FROM sys.assembly_files AF JOIN sys.assemblies A ON AF.assembly_id = A.assembly_id where AF.file_id = 1 AND A.name = @assemblyname";
using (SqlConnection conn = new SqlConnection("context connection=true")) {
using (SqlCommand cmd = new SqlCommand(sql, conn)) {
SqlParameter param = new SqlParameter("@assemblyname", SqlDbType.VarChar);
param.Value = assemblyName;
cmd.Parameters.Add(param);
cmd.Connection.Open(); // Read in the assembly byte stream
SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
SqlBytes bytes = reader.GetSqlBytes(0);
// write the byte stream out to disk
FileStream bytestream = new FileStream(path, FileMode.CreateNew);
bytestream.Write(bytes.Value, 0, (int)bytes.Length);
bytestream.Close();
}
}
}
}
}
Then build the project and deploy it to your database. Make sure that the CLR
Enabled configuration option is enabled on the SQL Server. This is probably
already enabled, since you have assemblies on it. In case clr execution is not
enabled you can run the following code on SSMS to enable it:
sp_configure 'clr enabled', 1
go
reconfigure
go
One more thing that you need to be aware of is the by default SQL server may
not allow you to write to the disk from the .NET code. If you get a FileIO
security error when you run the code above by calling the stored procedure in
SSMS, you will need to configure the proper permission set for the
assembly. You can do this via SSMS: right-click the new assembly and look at
the Permission Set in the Properties dialog. Set it to External Access. Now you
should be able to export your assemblies by running the following code in SSMS:
exec SaveAssembly 'AssemblyName', 'f:pathoassemblyname.dll'
Hope this works for you...
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…