SQL Server 2008 R2 Error: Login failed for user ''. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)

I started experiencing above error with my SQL Server 2008 R2 instance when trying to connect and spent about 2 hours for sorting it out. It said that the instance was being upgraded but I was 100% sure that no upgrades were added. I searched for this error and almost all who have experienced the same had accepted that this was due to an upgrade and almost all cases had automatically been resolved within few minutes.

As the next step, error log was checked and found the reason;

2013-10-06 18:30:15.89 spid7s      Error: 5133, Severity: 16, State: 1.
2013-10-06 18:30:15.89 spid7s      Directory lookup for the file "D:\Databases\Relational\2008R2\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 3(The system cannot find the path specified.).
2013-10-06 18:30:15.89 spid7s      Error: 1802, Severity: 16, State: 1.
2013-10-06 18:30:15.89 spid7s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2013-10-06 18:30:15.89 spid7s      Error: 912, Severity: 21, State: 2.
2013-10-06 18:30:15.89 spid7s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting.

As per the log, server still looks an old path for databases. I had changed the default database path recently but not today. However now it started looking the old path. Reason could be hibernating the machine without shutting down. Now how can I instruct SQL Server not to look for this path?

As per the search I made, there are two locations to be checked;

1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<<instance name>>\Setup
• DefaultData string value
• DefaultLog string value
2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<<instance name>>\MSSQLServer
• SQLDataRoot string value

In my case, path in the first key was wrong. Issue was resolved once the string value is updated with the correct path. You may experience the same and this could be the solution ....