Saturday, January 20, 2018
MS SQL 2008 upgrade glitch
MS SQL 2008 upgrade glitch
Some time after SQL Server 2008 SP1, the upgrade logic went wonky. I lost my ability to upgrade past one of the post-SP1 hotfixes/cumulative updates because of an error 598, state 1, severity 25 bug. The June 2011 Patch Tuesday had an automatic hotfix for SQL Server that resurrected said bug: removing all post-SP1 hotfixes & upgrading to SP2 did not fix the issue. So I found this MS reference, and this blog post, and shutdown all the other servers that might be making SQL calls, and this is what worked for me (step 1 may not be necessary, given what I found)...
1. Per the DataZulu reference, I looked for registry entries in HKLMSoftwareMicrosoftMSSQLServerMSSQLServer: I had none. I made DefaultData & DefaultLog string entries, and pointed them where my master databases and log files were respectively.
2. Coped the following into C:fixit.sql
declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread NHKEY_LOCAL_MACHINE, NSoftwareMicrosoftMSSQLServerMSSQLServer, NDefaultData, @SmoDefaultFile OUTPUT
declare @SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread NHKEY_LOCAL_MACHINE, NSoftwareMicrosoftMSSQLServerMSSQLServer, NDefaultLog, @SmoDefaultLog OUTPUT
SELECT
ISNULL(@SmoDefaultFile,N) AS [DefaultFile],
ISNULL(@SmoDefaultLog,N) AS [DefaultLog]
3. Ran net start MSSQLSERVER /f /T3608 & sqlcmd -A -i c:fixit.sql -o c:sql.txt from the command line: the first command starts an admin-only SQL server; the second command will process the SQL fix DataZulu posted.
4. The file made by the second command (C:SQL.TXT) should list where SQL Server is trying to find stuff: in this case, it was a missing folder on E-drive. So I made the folder it wanted, and restarted the service.
5. The service started working properly, and created a 1KB MS_AgentSigningCertificate.cer file in that folder I made.
All this trouble, because I think Microsoft wanted to do something with a security certificate. (shakes head)
