Couple of month’s back I had posted about an error while trying to backup the database using TDP. The same issue occurred again but this time on a different server. The error message was same as earlier.
Unlike last time I did not have do any guess work as to why this error started all of the sudden. Couple of days back the Application Support team had tried re-installing SQL Server client components and also applied some updates to Visual Studio components. From the Event Logs it looked like the installation did not go well and they “somehow” got their application working. The Event Log was filled with messages like this.
Event Source: .NET Runtime Optimization Service Event Category: None Event ID: 1101 Date: 12/16/2010 Time: 8:30:25 AM User: N/A Computer: ServerName Description: .NET Runtime Optimization Service (clr_optimization_v2.0.50727_64) - Failed to compile: Microsoft.SqlServer.SQLTaskConnectionsWrap, Version=22.214.171.124, Culture=neutral, PublicKeyToken=89845dcd8080cc91 . Error code = 0x80070002
But the changes made in the SMO components did not go well with the TDP application. To fix this issue I did not have to take the longer route like I did last time. Since the database backups had failed for two consecutive days, it was a High Severity Incident and I could do *anything* to fix it. The option was to either reinstall TDP for SQL Server or Install the SMO components which the TDP application was referring to. Since the former is out of scope for the DBA team, I chose the latter.
All I had to do was to download and install Microsoft SQL Server System CLR Types and Microsoft SQL Server 2008 Management Objects from here. As soon as these were installed Microsoft.SqlServer.Smo Version 10.0.0.0 assembly was available in C:\WINDOWS\ASSEMBLY folder. As expected TDP started working fine and I got the license to spend my Saturday on something other than SQL Server related issues 😀