Yesterday on one of the Production SQL Server 2005 instance, the binaries of Client Components went missing all of the sudden. No SSMS, no OSQL. The front end application did a lot of batch processing using osql.exe. This way the issue with client components of SQL Server caused downtime !
Scanned through all the logs but could not find a trace of it. Even checked the Quarantine folder of the anti virus to check if the Anti Virus application went crazy. It was not to be. So informed my team to re-install Client Components of SQL Server 2005 on that instance.
Got a call at night from team that the client components is not progressing. How can I have a peaceful night when my day started with the DBA’s worst nightmare, Data Corruption!
The summary.txt for the failed installation readTo change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include SKUUPGRADE=1 parameter.
Going by the error message, now I launched the setup.exe with the SKUUPGRADE=1 parameter. But the setup said, it could not find anything that it could upgrade! This is a perfect dilemma. Neither it would allow a new install nor it would allow it to be upgraded.
Then I tried to trick SQL Server by copying the tools directory from a server which was healthy. Again SQL Server outsmarted me. Double click on sqlwb.exe, one flash screen and gone! No error messages any where and no clue what is happening.
Now the only option left was to REPAIR. Headed straight to “Add/Remove” programs applet in Control Panel and clicked on “Change” option on the SQL Server 2005 installation. In the next screeen selected only the “Client Components” and in the next screen selected the “Workstation Components“. Now the SQL Server Installation gave more options and extra Next buttons click. I was hopeful that it would fix the issue. No, again blank screen with an option to click “Finish“.
Then I remembered one thing. The SQL Server Installation wizard internally calls several .msi files for each component like Database Services, SSIS, SSRS, Client Tools etc. Good idea. Navigated to the Tools\Setup folder in the installation media and launched SqlRun_Tools.msi which is the installer for Client Components. Voila ! Files started getting copied and yes, the Client Components were installed successfully.
All the Start Menu items came back and clicked on SQL Server Management Studio (SSMS). I was able to connect to the instance and seemed like everything was normal. Being a DBA, how can I quit SSMS without issuing a single command? Clicked on “New Query” button and I was in for a surprise. SSMS popped up this message.
What? Cannot find template file for the new query? Please, I have had enough. Quickly peeped into the server which had SSMS working fine. It has a blank file named SQLFile.sql and a 1 KB file called Sql.vsdir. These two files were missing on this server and simply pasted these files under C:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\sqlworkbenchprojectitems\Sql folder.
Thats it. The New Query window opened and issued a command for my satisfaction. Finally, it executed with no errors.
With a sense of accomplishment, dived on the bed to catch at least a couple of hours of sleep.