What does SQL 2005 do with a dll provided when you register an assembly?

Question

We inherited a legacy SQL 2005 database. Two assemblies were setup to be used by triggers in one of the databases (ouch). The folder/files were delted by mistake. They were installed by a previous employee from his desktop folder, and did not get checked into source control (double ouch). Now that the dll's are gone, we are concerned about a reboot. Does SQL 2005 copy/rename/store those dll's in such a way that would mean a reboot of the server would not create a problem?

Answer 1

You're in luck, if the original deployed assemblies are just plain missing you can still export the assembly binary as a hex string.

1.Open SSMS and navigate to Programmability -> Assemblies. You should see your Registered Assembly, I've highlighted mine in red.

2.Right click on the assembly and script it to a new query window:

3.When you've done that you'll see something like this:

CREATE ASSEMBLY [Microsoft.SqlServer.Types]
AUTHORIZATION [sys]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000...[snipped]
WITH PERMISSION_SET = UNSAFE

The line with FROM 0x4D5A90000... is the actual assembly encoded as a large hex string. See below how to export back to file format.

Make sure you also script any functions, triggers etc that depend on this assembly as well. You can find them out by doing a "View Dependencies" on the assembly in SSMS.

You should be able to reboot the server with impunity because the assembly is stored and loaded from one of the system database tables (who's name I can't recall off-hand but the assembly is visible via the sys.assembly_files system view) and not on the file system.

However if you don't feel like taking a reboot leap of faith then do the export as described above, then recreate on another SQL 2005 instance to make sure that everything is intact.

How to export registered assemblies to files

Based on an answer I found in this thread you can export your registered assemblies back to regular files. If Visual Studio was used to deploy the assembly there could also be some source code objects stored in SQL server, which of course is a bonus. For example wheh I queried my dev server after deploying from VS2010 I found this:

SELECT * FROM sys.assembly_files

As you can see there's a few files associated with SqlServerProject2 (*assembly_id = 65540*). We can export all of them by running this script:

DECLARE @assembly_id int, @name nvarchar(260), @content varbinary(MAX)
DECLARE @ObjectToken INT, @outputdir nvarchar(20)

-- Get assembly id from querying sys.assembly_files
SET @assembly_id = 65540                  -- IMPORTANT: SET THIS VALUE
SET @outputdir = 'D:\Data\Assemblies\'    --            AND THIS PATH

DECLARE assy_cursor CURSOR FOR
  SELECT name, content
  FROM sys.assembly_files 
  WHERE assembly_id = @assembly_id 

OPEN assy_cursor
FETCH NEXT FROM assy_cursor INTO @name, @content

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @name = @outputdir + REPLACE(@name, '\', '-')
  print 'Saving: ' + @name  
  EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
  EXEC sp_OASetProperty @ObjectToken, 'Type', 1
  EXEC sp_OAMethod @ObjectToken, 'Open'
  EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @content
  EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @name, 2
  EXEC sp_OAMethod @ObjectToken, 'Close'
  EXEC sp_OADestroy @ObjectToken

  FETCH NEXT FROM assy_cursor INTO @name, @content
END
CLOSE assy_cursor
DEALLOCATE assy_cursor

You will need to set these two variables:

SET @assembly_id = 65540
SET @outputdir = 'D:\Data\Assemblies\'

The @outputdir path will need to be somewhere that SQL Server has permissions to write to.

When you execute the script you should end up with one or more files. If you don't have all of the source code then you can always decompile the outputted assembly using .NET Reflector.

Note: OLE automation (the sp_OAxxxxx stored procedures) is disabled by default in SQL Server but you can enable it by executing:

use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO