Rebuilding server level collation setting


You have just completed setting up a new development database server for a new project. After that, everyone in the team now starts working on implementing their design and starts to create their objects. When the time came for them to start adding test data, Chinese characters cannot be stored to the database. The system the development team are working on needs to store data in Chinese, Katakana and English characters at the same time.


The collation setting in your current setup must not be compatible with using Chinese or Katakana characters. Therefore you need to change the server level collation settings in your SQL Server. When doing an installation of a new instance of SQL Server you need to understand the code page, sorting, case sensitivity and accent sensitivity requirements of that application that is going to use the database.


SQL Server collation specifies the set of rules that will determine what types of characters will be stored in the database, for sorting and when you need to compare characters or strings. This is important because this is what specifies what code page to be used. Setting the wrong collation in your SQL Server may force you to re-install your SQL Server instance as this is a complex procedure.


There is a way to change your server collation settings without the need to re-install your SQL Server. This article is a personal recount of how I was able to update our server collation settings when the above scenario happened to our development team by following the steps below.

  • Disconnect all current users. Let the development team that you need to at least 15-20 minutes to fix the issue. This was how long I had to fix our server when it happened.
  • Script all your logins, scheduled jobs, and other server objects you may have. This will serve as a backup that you will need to run later.  In my case, I had some legacy DTS packages so I had export them one by one and saved it somewhere.
  • Detach all your user database
  • Stop the SQL Server instance
  • Verify your SQL Server authentication mode. If you are using mixed mode authentication, you will need the ‘sa’ account password on hand.
  • Open the command window.
  • Issue the following command, see my screenshot below, please take note also of the directory path where you need to run the command.
Command Window

Command Window

If you are using mixed mode authentication in your security settings, you need to enter the ‘sa’ account password. The /instancename is your SQL Server instance name. The /SQLCollation parameter should contain the correct collation you need to set.

After the above command is successful, you will need to put all your system objects back because all the SQL Server system database are newly setup. So your earlier script for all your logins, scheduled jobs and other objects will come in handy now because you need to put them all back. Then attach your user database and verify everything is in order. If you have DTS packages which was the case for me, I needed to import them one by one again.

After that you can check your server collation setting and it should be the correct one now. And everyone can get back to work and you can get another cup of coffee.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali -

Meels on Wheels

Meels for breakfast, lunch and dinner.

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment