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?
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\'
@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