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:
The line with
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
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:
As you can see there's a few files associated with
You will need to set these two variables:
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