Change SQL Server Collation – Back to Basics

One of my most favorite things in the world is the opportunity to deal with extremely varying database and environment requirements. Many vendors and databases seem to have a wide swath of different requirements. Some of the reasons for these requirements are absurd and some are not. That is a discussion for a different day.

When dealing with vendors, sometimes you get good documentation and requirements for the app. If you happen across one of these opportunities, you should consider buying a lottery ticket. Most of the time, the requirements and documentation are poorly assembled and then suffer from linguistic shortcomings.

What do you do when you run into poor documentation from a vendor? The typical answer would be to either call them or make a best guess (even if you call them, you are likely stuck with a best guess anyway). Then what do you do when you find that your best guess was completely wrong? Now it is time to back pedal and fix it, right?

When that mistake involves the server collation setting, the solution is simple – right? All you need to do is uninstall and reinstall SQL Server. That is the common solution and is frankly a horrific waste of time. This article will show some basics around fixing that problem quickly without a full reinstall.

I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.

Reinstall Prohibited

I am not a huge fan of wasting time doing something, especially if there is a more efficient way of achieving the same end result. I am not talking about cutting corners. If you cut corners, you likely just end up with having more work to do to fix the problems your sloppiness will have caused. That to me is not the same end result.

Having run into a bit of a problem with a vendor recently (with lacking requirements), I found myself with a server that was installed with the wrong collation instead of what the vendor wanted (never-mind they said nothing of it until a month after the server was setup and ready for them to use). The vendor needed the collation fixed immediately (basically it needed to be fixed yesterday). I really did not want to do a reinstall of the server and the sysadmins were just about to click through the uninstall and redo the install.

Oy Vey! Everybody hold up just a second here! First things first – verify with certainty there is good reason to need to change the server collation. It is perfectly legit to give the vendor the third degree here. Make sure they understand why they need the change. If they can answer the questions satisfactorily, then proceed with the change.

Next, just because the vendor says you have to uninstall/reinstall (or reboot) the server to make a certain change, does not mean they know what they are talking about. I have run into too many cases where the vendor thinks you must reboot the server to change the max memory setting in SQL Server (not true for sure).

Sure, common myth would say that you must reinstall SQL Server in order to change the default server collation. That is not entirely accurate. Reinstall is just one option that exists.

In the case of this vendor, they required that the SQL_Latin1_General_CP850_CS_AS collation be used. The server was set for SQL_Latin1_General_CP1_CI_AS. So, let’s see how we can change the collation without a reinstall.

The first thing to do is to confirm the collation we have set.

We can see from these results that indeed the collation is wrong and we need to change it in order to comply with the request from the vendor. Next we will need to stop the SQL Server services.

I think that is pretty clear there what to do. As a reminder, the preferred method to stop and start SQL Server services is via the SQL Server Configuration Manager. We won’t do every start/stop from here for this article for good reason.

Once the services are stopped, then we need to open an administrative command prompt and navigate to the SQL Server binn directory as shown here.

This is for a default instance on SQL Server 2017. If you have a named instance or a different version of SQL Server, you will need to navigate the instance folder structure for your instance.

Next is where the magic happens. We enter a command similar to this:

Here is a quick summary of those flags in this command:

[-m] single user admin mode
[-T] trace flag turned on at startup
[-q] new collation to be applied

There are more such as -s available in books online for your perusal.

If you are curious what is up with those Trace Flags, pretty simple. TF4022 is to bypass startup procs. TF3659 on the other hand is supposed to write errors to the error log (at least in theory).

When the script starts, you will see something like the next two screens:

In the first, you can see that it says it is attempting to change the collation. In the second, just before the completion message, it states that the default collation was successfully changed. Let’s close this command prompt window and then go start SQL Server and validate the change.

And that is a successful change. See how easy that is? This effort takes all of maybe 5 minutes to complete (validation, starting, stopping and so on). I would take this over a reinstall on most days.

Now that we have changed the collation, all I need to do is repeat the process to set the collation back to what it was originally (in my test lab) and make sure to bookmark the process so I can easily look it up the next time.

There is a bit of a caveat to this. On each change of the collation, I ran into permissions issues with my default logging directory (where the sql error logs are written). I just needed to reapply the permissions and it was fine after that (SQL Server would not start). That said, the permissions issue was not seen on the box related to the change for the vendor. So just be mindful of the permissions just in case.

The Wrap

Every now and again we have to deal with a sudden requirements change. When that happens, we sometimes just need to take a step back and evaluate the entire situation to ensure we are proceeding down the right path. It is better to be pensive about the course of action rather than to knee jerk into the course of action. Do you want to spend 5 minutes on the solution or 30-40 minutes doing the same thing? Changing collation can be an easy change, or it can be one met with a bit of pain doing a reinstall (more painful if more user databases are present). Keep that in mind and keep cool when you need to make a sudden change.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.

18 Comments - Leave a comment
  1. AG says:

    I don’t see a mention of how or if, existing databases are affected.
    IIRC, changing server collation only affects new databases. I think it is worth a mention.

    • Jason Brimhall says:

      I didn’t show the impact to my existing databases on the test instance because I felt it out of scope. That said, I had a few user databases on the instance with the default collation and no other special collations (e.g. column collation). The database collation for those databases will indeed change. However, there will be a data impact and you must test your data and figure out how to resolve those issues.

    • Hi AG. As far as Instance-level collation goes, that also controls variable names, cursor names, and GOTO labels. Also, technically speaking, it’s the [model] database’s collation that is the default for newly created databases :-).

      Regarding existing databases, there is quite a bit that is affected, one or two things that are ignored, and some additional maintenance that might need to be done depending on circumstances. For a thorough description of this operation, please see:

  2. Blair Christensen says:

    One thing to note: this was a brand new install with no data in the database. Thus the only concerns were changing the system database(s) to rely on the new collation. This is a great solution, but its also an extremely rare occurrence.

    Most of the time a collation change involves a data port from one’s old system to a new one like when you’re trying to move data from a 20-year-old SQL Server on the original (case sensitive collation) to the current default collation. That process isn’t nearly this easy.

  3. Chris says:

    Hi Jason,

    I’ve been researching collation changes and I’ve noticed that this article seems to have been plagiarised over on MSDN Blogs:

    Images are identical as is some of the text.

  4. Adam Newcombe says:

    Hi Jason,

    Thanks for this, very useful.

    Can I clarify on the difference between this solution and the rebuild of the master database?

    Is it based upon user databases present or not?


  5. Alan says:

    I’ve tried this method a couple of time and it’s never worked for me. I get the message that it was successful but then the instance won’t start as it is in single user mode and I’m not able to find what process holds the connection.

    Any ideas where I’m going wrong?

    • Jason Brimhall says:

      If you are using the command prompt to start SQL Server, the SQL process from that window will need to be stopped. That process will be started in single user mode.

      • Alan says:

        Thanks, I’ll give another test.

        • Carlos Farfan says:

          The procedure to restore the Database collate works fine; but the collate of the Server does not change and damages the option to edit the user login properties through the microsoft managment studio the error I get is “Cannot resolve the collation conflict between” SQL_Latin1_General_CP1_CI_AS “and” Modern_Spanish_CI_AS “of the equal operation to. (.Net SqlClient Data Provider) “, someone knows how to solve this impasse

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

April 2018
« Mar   May »

Welcome , today is Monday, April 6, 2020