Thursday, June 16, 2011

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 HKLM\Software\Microsoft\MSSQLServer\MSSQLServer: 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 N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT
declare @SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @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)

No comments:

Post a Comment