I recently had a very simple and interesting error received while I was working with Docker and Persistence Storage for my upcoming SQL Server Performance Tuning Practical Workshop. The error was related to script upgrade mode and the fix was extremely simple. Let us see the entire story today.
Earlier on this blog, I wrote a blog post about SQL SERVER – Docker Volume and Persistent Storage. During the blog post, I explained how with the help of the docker we can instantly upgrade our SQL Server instantly. It was a fantastic feature as if you think when we have to update SQL Server with the latest update, we have to re-install SQL Server and it takes a lot of time. However, with the help of Volume, we can easily upgrade SQL Server in just within a minutes.
Fix Error for Upgrade Mode
Recently, I attempted to upgrade my SQL Server CU to the latest version of SQL Server and instantly I got the following error:
Login failed for user ‘sa’. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)
Here is the screenshot of the error.
Well, the solution if this issue is very simple. I just waited for about 1 minute and attempted to log in again and I was able to login immediately. Whenever we are using Docker and Volume, our data remains on the persistent storage but our core engine is updated. Once SQL Server comes up with the new update, it runs few of the upgrade scripts for your database. While the scripts are running the database even though looks up it does not allow you to connect with it.
In most of the cases, the script upgrade takes 1 minute or 2 minutes of time. If you wait for that much and try to log in, your error will go away. Well, if you are here searching for the solution to this problem. I am very confident by the time you finished reading this blog post the error is gone. Just go ahead and try to login again and it should work.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Thanks for this blog. I’ve found it to contain a lot of helpful articles on SQL. And just as you documented, the solution was to wait for a couple minutes while SQL completed upgrading.
i’ve just encountered a similar error when accessing SQL server installed on Linux as part of a POC, currently on test environment.
the workaround you’ve suggested is great for a personal account trying to connect the instance, but what about application account in production usage?
there must be some way to prevent from those upgrade processes running automatically
are you familiar with any other solution?