A couple of hours ago, the Backup team sent an email that they are not able to backup databases on an instance using Tivoli Data Protection (TDP). I did not pay much attention to it because they raise this concern every alternate day and when they re-run it the backup completes successfully. But when my team member showed me the error message in TDP, it looked different. “I’ll take it” was my knee-jerk reaction.
After opening TDP GUI, clicking on the SQL Server it was throwing the below error message.
This instance was running on SQL Server 2005 but the error message is stating Version = 10.0.0.0, which means SQL Server 2008. Quickly glanced through the Add/Remove Programs applet and could not locate Native Client Tools for SQL Server 2008. But without fire there will not be any smoke! For sure someone had installed the Client Tools for SQL Server 2008 and then uninstalled it. DBA told me that the server was rebooted last night and could see many Error messages in the Event Log prior to that. Some smart guys had done the installation under the credentials of the Service Account! Well, it had to be fixed now else no backups for those databases.
Since it was referring to Microsoft.SqlServer.Smo of version 10, I looked for it in the Global Assembly Cache (GAC), under C:\WINDOWS\ASSEMBLY. But it was not listed there.
The client tool assemblies for SQL Server 2008 are located on C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder. This folder had only one file Microsoft.SqlServer.Types.dll. This confirmed that the server was meddled with last night. I had two options to fix this issue. To install SQL Server 2008 SMO package from this link or to register the assemblies back into GAC manually. Since installing the SMO package does much more than putting back the assemblies, I chose to stay away from it. Instead planned to move the assemblies to GAC myself.
From a working server which had SQL Server 2008 SMO installed, I copied the following files to C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder.
Microsoft.SqlServer.ConnectionInfo.dll Microsoft.SqlServer.ConnectionInfoExtended.dll Microsoft.SqlServer.Dmf.Adapters.dll Microsoft.SqlServer.Dmf.dll Microsoft.SqlServer.DmfSqlClrWrapper.dll Microsoft.SqlServer.Management.Collector.dll Microsoft.SqlServer.Management.CollectorEnum.dll Microsoft.SqlServer.Management.Sdk.Sfc.dll Microsoft.SqlServer.PolicyEnum.dll Microsoft.SqlServer.RegSvrEnum.dll Microsoft.SqlServer.ServiceBrokerEnum.dll Microsoft.SqlServer.Smo.dll Microsoft.SqlServer.SmoExtended.dll Microsoft.SqlServer.SqlEnum.dll Microsoft.SqlServer.SqlWmiManagement.dll Microsoft.SqlServer.SString.dll Microsoft.SqlServer.Types.dll Microsoft.SqlServer.WmiEnum.dll Microsoft.SqlServer.WmiEnum.dll
Only copying these files is not sufficient, they need to registered. Unlike the other DLLs these files cannot be registered using regsvr32.exe (located under C:\WINDOWS\SYSTEM32). They have to be moved into GAC using the gacutil.exe located under C:\WINDOWS\assembly\GAC folder and then registered using regasm.exe located in the .Net framework folder. From the command prompt registered these files one by one using the below command.
gacutil /i "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Then from the command prompt navigated to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 folder and registered the assemblies using the below command.
regasm "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
Assuming that the issue would have been fixed by now, I happily opened TDP. Now it was telling me that Microsoft.SqlServer.SqlClrProvider assembly was missing.
I had copied all the assemblies from the working server and registered them. This assembly was not available in the regular location. Where else this Microsoft.SqlServer.SqlClrProvider.dll could be? The best place to look for was the C:\WINDOWS\ASSEMBLY folder on the working server. The files in this folder cannot be copied using the Windows Explorer. They need to be copied from the command prompt. After doing dir /s in the ASSEMBLY folder I was able to locate this file under C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider folder. Copied this file to the C:\ on that server and from there copied it to the C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies folder on the affected server. Then registered this file by following the process explained earlier.
Expecting few more surprises I opened the TDP GUI yet again. Not this time, I was in for a pleasant surprise! It had worked and TDP showed up the databases. It was great sense of accomplishment after 60 minutes of juggling!