How to change server level collation for a SQL Server Instance

By:   |   Comments (34)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | > SQL Server Configurations


Problem

I was doing an install of SQL Server and after the install I was told that we were supposed to use a different collation setting for the instance.  In this tip I will explain step by step how to change the server level collation setting for an existing SQL Server instance.

Solution

Before moving ahead, let's discuss what the collation setting is used for as per books online

"Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales. For example, in an ORDER BY clause, an English speaker would expect the character string 'Chiapas' to come before 'Colima' in ascending order. However, a Spanish speaker in Mexico might expect words beginning with 'Ch' to appear at the end of a list of words starting with 'C'. Collations dictate these kinds of sorting and comparison rules. The Latin_1 General collation will sort 'Chiapas' before 'Colima' in an ORDER BY ASC clause, whereas the Traditional_Spanish collation will sort 'Chiapas' after 'Colima'."

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation.  It is not mandatory that we change the default server level collation, because you can specify a different collation level when you create users databases, but you need to remember to specify this when creating user databases.

To change the default SQL Server collation you can simply rebuild the system databases. When you rebuild the master, the model, msdb and tempdb system database are actually dropped and recreated in their original location. If a new collation is specified in the rebuild statement the system databases are rebuilt using that collation setting. Any user modifications to these databases will be lost, so it is important to backup any of this information you wish to retain. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.  The tempdb database is recreated each time SQL Server is restarted, so there is nothing in that database that you will need to retain.

Changing the server-level collation does not change the collation of existing user databases, but all newly created user databases will use the new collation by default.

NOTE: DO NOT MAKE ANY CHANGES IN PRODUCTION WITHOUT PROPER TESTINGS IN LOWER-LIFE CYCLE ENVIRONMENTS

Steps to change server level collation of a given SQL Server Instance

Step 1

First check the existing SQL Server collation setting of your instance. Run the command below to get the collation value of your SQL Server instance. 

SELECT SERVERPROPERTY(N'Collation')

Find the collation settings

Step 2

You can see in the above screenshot that the collation setting is "SQL_Latin1_General_CP1_CI_AS". For our example we want to change this to "SQL_Latin1_General_CP1_CI_AI". As I mentioned, we have to rebuild our system databases to change the server level collation and put this new collation value in the rebuild command.

Make sure to record all server level settings before rebuilding the system databases to ensure that you can restore the system databases to their current settings. Record all server-wide configuration values by running the below commands and save the output.  If this was a brand new setup and you haven't made any changes to the system databases you don't need to worry about collecting this data.

SELECT * FROM sys.configurations;
-- OR
EXEC SP_CONFIGURE

Step 3

Create and prepare all scripts related to jobs, maintenance plans, logins and their access levels. You can generate scripts by selecting all jobs in object explorer in SSMS and right click on your selection then choose the "script as" option to create the script for all jobs.  You can do similar steps to generate scripts for alerts and operators as well. The below screenshot to generate scripts for all your jobs.

Generate scripts of all Jobs

Next is to secure your logins, passwords and their access levels. You can use sp_help_revlogin stored procedure to create a script for all logins so they can be recreated easily.

Step 4

Detach all user databases before rebuilding your system databases. If you leave databases attached they will be detached and will be found in the database folder.

Step 5

Now its time to rebuild your system databases. This operation will recreate your master database and all existing settings will be reset. Run the below command from a Windows command prompt. Make sure to run this command from the directory where you have placed your SQL Server setup files. Once you press enter, a separate window will appear to show you the progress bar. Once the rebuild is done, that window will disappear.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MANVENDRA /SQLSYSADMINACCOUNTS=gourang\hariom /SAPWD= M@nVendr4 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

Rebuild system databases

Once the rebuild operation is complete, check the server collation to verify whether this change is successful or not. As we can see in the screenshot below, the server collation has changed to SQL_Latin1_General_CP1_CI_AI. At this point we cannot restore any of the system databases, because doing so will revert back to the previous collation setting. So we will need to use the scripts that were created to recreate logins, jobs, etc...

check collation after Rebuild system databases

Step 6

Attach all user databases which were detached in Step 4. If you have any issues, take a look at this tip How to fix database attach error in SQL Server 2008R2.

Step 7

Now change the collation settings of all user databases. It's not necessary to change the collation settings for the user databases, it totally depends on your requirement.

Run the commands below to change the collation settings of your user databases. 

ALTER DATABASE DBName collate SQL_Latin1_General_CP1_CI_AI

Sometimes the command fails to execute and you get this error:

Msg 5075, Level 16, State 1, Line 1 The object 'CK_xxxx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

In that case you may need to export all data and recreate the database with the new collation settings.

Step 8

Now run all of the scripts which were created in Step 3 to restore jobs, alerts, logins, operators, etc...  Also don't forget to change the server level configuration settings which were captured in Step 2.

Now your instance is ready to use the new server level collation.

Next Steps
  • Follow this process to change the server level collation of any SQL Server instance. This process could get quite complex if you have made changes to the system databases and also have user databases, so make sure you script out any objects or data you need to recreate and that you also have good backups of all your databases to avoid any data loss in the case of an issue or failure.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, August 10, 2023 - 2:24:48 AM - mhd Back To Top (91480)
Thanks
Saved My day

Thursday, July 15, 2021 - 7:26:02 AM - Tore Back To Top (89006)
!! In SQL 2019 all the settings in Master etc are removed, not sure why that has been changed since 2016, but its a big pain in the but, luckily I extracted all the important information first. !!

Saturday, November 7, 2020 - 3:37:19 AM - Manvendra Deo Singh Back To Top (87770)
Hi David,

Not sure you have tested these comments in SQL 2016 or not. I am writing similar tips on SQL 2019 and addressing all your points in that tips with screenshots to demonstrate that all configurations from system dbs will be reset along with user db details like their collation will not be changed automatically but you need to change it manually post server level collation change.

Thursday, September 17, 2020 - 2:44:33 PM - Pablo Back To Top (86499)
Hello, i have a problem with a collation server....i've installed a new instance with Modern_Spanish_CI_AS collation, then i've restored user databases with Modern_Spanish_CI_AS and restored msdb too with same collation (for jobs), then i had a problem with collations (error 468),so i' ve rebuild system databases with binary sqlsrv.exe(i forgot drop users before)so, now i have a new problem when i trying to access to login properties (error 468 again)
Can i resolve this issue??
SELECT DATABASEPROPERTYEX(N'master', N'Collation') => Modern_Spanish_CI_AI
SELECT SERVERPROPERTY(N'Collation') => Modern_Spanish_CI_AS

Friday, February 21, 2020 - 8:32:59 AM - Terrence Holmes Back To Top (84688)

ensure you have backed up your server prior to running the reinstall script.

Wednesday, August 14, 2019 - 9:46:38 AM - Greg Robidoux Back To Top (82058)

Hi David,

Could you share more details about the steps you took for the collation change as you mentioned in your comment.  I had someone test this with SQL 2016 and 2017 and ended up with different results.  After changing the collation, the data in the master and msdb databases was no longer present and the users databases were not longer attached.  If you are interested in writing an article about this, feel free to enter info in this form https://www.mssqltips.com/contribute/ and we will get you details about how to contribute and payment details.

Thanks
Greg

Monday, June 24, 2019 - 2:16:00 AM - David Ingleton Back To Top (81565)

In SQL Server 2016 performing this process works a bit differently to that described.

1 - The master database is not rebuilt back to factory settings.  All current master database settings are retained after performing this process.  eg all user databases will still exist in sys.databases table.  All user stored procedures will still exist and all configuration will still exist after performing this process.  All logins are retained as well.

2 - You do not need to detach user databases (although I STRONGLY recommend you back them all up prior to the change)

3 - All user databases WILL have their collation changed (right down to the column level) as well as system databases.  If you dont want all user database to have their collation changed then you should reconsider this approach.  Remember for those databases you do not want the collation changed for you can restore them from the backup you took before performing this change process.  If you have Integration Services Catalog then SSISDB will have collation changed.  This will likely break the ISC.  You must restore this database back to pre collation change state.  If you have SSRS then ReportServer databases will have their collation changed and I recommend you restore these databases back to the pre collation change state.

4 - You do not need to change collation of all user databases.  This process will change them all for you.

5 - All msdb configuration will be retained.  The msdb is not restored back to factory settings when you perform this process.

SQL Server 2016 makes this process much easier to perform even after you have configured your instance of SQL Server and created user databases.

Friday, February 22, 2019 - 11:11:20 AM - Tom Back To Top (79091)

You the man!

Thursday, November 15, 2018 - 4:29:35 AM - Hamideh Back To Top (78254)

 Thank you for your great article.

I changed the server collation of my system based on the instrcture but I have a problem how to recover or change server level configuration settings which were captured in step 2.

I did below setps for saving server level configuration settings

  1. SSMS--> new query --> execute this script  SELECT * FROM sys.configurations;
  2. Select all the output--> Righ click-->  "Save Result as.."
  3. The file saves with .csv format

Tuesday, October 9, 2018 - 11:00:19 AM - Nick R Back To Top (77905)

I did it this way:

1) stop all SQL server services (in task manager)

2) open CMD as admin

3) cd \Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn (may be MSSQL12 etc.)

4) sqlservr -m -T4022 -T3659 -s "MSSQLSERVER" -q "Finnish_Swedish_CI_AS" (MSSQLSERVER is your local instance name)

5) restart machine

Wednesday, June 13, 2018 - 5:36:29 AM - Stelian Coltan Back To Top (76206)

 Thank you for your help, very good tip.

Worked for me on SQL Server 2017

Changing Collation to SQL_Latin1_General_CP1_CS_AS

 

Have a great day!

Stelian

 

Thursday, October 6, 2016 - 1:41:28 AM - Prathmesh Back To Top (43499)

Hello Manvendra,

Just wanted to say a big thanks to you for posting this step by step instruction. I followed this and was able to successfully change the collation of SQL Server 2012 SP3 instance. Did not encounter a single issue. Everything went smoothly, thanks to your detailed instructions.

I did not need to update my databases as they were the correct collation, only the instance collation needed to be changed.

A few pointers for any other users who might be attempting this -

1. In Step 5, I did not get any seperate command window after issuing the command. Once the command finished, it just returned back to the prompt.

2. Maintenance Plans need to be exported out by opening the SSMS in administrator mode and logging into integration services.

3. After running the EXEC sp_help_revlogin, the Script output gets generated in the messages window.


4. The sp_help_revlogin does not export out access levels for the users, they will need to be done seperately.

Thanks,

Prathmesh

 

Sunday, March 27, 2016 - 11:49:24 PM - Manvendra Back To Top (41067)

 Thanks Raj,

 I have performed this activity on a named instances that is why I have used /INSTANCENAME=Manvendra. Put your instance name for /INSTANCENAME. If you are doing this activity for default instance you can use /INSTANCENAME=MSSQLSERVER

Monday, March 21, 2016 - 1:32:38 AM - Raj Back To Top (41010)

 Thanks a ton  Manvendra , this worked like a charm.

Only thing different was to replace the servername with /INSTANCENAME=MSSQLSERVER

thanks to PQuenneville too.

 

Thursday, August 13, 2015 - 9:05:36 AM - Manvendra Back To Top (38439)

Use either /UImode or /Q or /QS. You will get user interface in UIMOde where as /Q is for quite installation.

Wednesday, August 12, 2015 - 1:56:45 AM - Satya Back To Top (38426)

Hi, I tried to change the SQL Instance collation as like above,:

I received below error message, can you please help here

The /UIMode setting cannot be used in conjunction with /Q or /QS

Thursday, July 9, 2015 - 2:21:25 AM - Basil Nhlanhla Back To Top (38159)

Thank you for sharing my problem is solved Manvendra Singh

Sunday, May 24, 2015 - 3:31:53 AM - Veer Back To Top (37266)

Does not work with SQ Server 2008 Standard Version.

Monday, April 27, 2015 - 5:05:11 AM - Manolis Back To Top (37038)

Thanks a lot, it spared me a few hours reinstallation and an unhappy customer. I had the server up and running in less than a quarter of an hour. Yes, it was a new and clean installation, but still it helped me a lot. SQL SERVER 2008 R2 SP3.

Friday, December 19, 2014 - 3:35:50 PM - Tobbe Back To Top (35693)

Thanks for sharing!

Solved my problem.

Tuesday, October 7, 2014 - 9:43:38 AM - leocuba Back To Top (34863)

Muchas gracias ha sido genial su inforamción

 

Thursday, August 21, 2014 - 10:10:32 AM - Balaji Back To Top (34221)

Thanks......

It helped a lot... :)

Thursday, August 7, 2014 - 6:05:47 PM - Rafael Matias Silva Back To Top (34048)

Great post. I used your information to modify the SQL Collation after installation and it worked perfectly.

Thank you for the information!

Rafael Matias Silva

Tuesday, June 24, 2014 - 7:13:18 AM - Guddu Back To Top (32371)

Not able to change collation through above solution. tried exact same as give above but no luck 

please help

Sunday, June 15, 2014 - 4:22:33 AM - Masoud Back To Top (32248)

tanks a lot

it works like a miracle ;)

Thursday, May 29, 2014 - 4:46:28 PM - J Back To Top (31988)

I found I had to make myself owner of all ldf and mdf files on the database server, otherwise setup.exe fails silently (even if /QUIET is not specified) and the only place to get the logs for what happened is Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt

 

Good luck!

Wednesday, March 12, 2014 - 4:08:41 PM - PQuenneville Back To Top (29728)

Brilliant Manvendra, once I opened command prompt in Administrator mode and used /INSTANCENAME=MSSQLSERVER it worked!  

As others have said, you have the clearest explanation on this subject.

Best Regards  from a Happy Canuck!

Friday, January 17, 2014 - 9:35:19 AM - kumar Back To Top (28119)

Please suggest how to select collation during installation of SQL Server 2008 R2.

Tuesday, January 14, 2014 - 11:39:50 PM - J Richards Back To Top (28077)


We had to add one more parameter to this cmd line to run:

/IAcceptSQLServerLicenseTerms

 

Wednesday, September 11, 2013 - 9:33:38 AM - zimi Back To Top (26728)

do i need to refresh or restart sql server? i have followed the steps as shown exactly, but not winning.

i am using sql 2012.

please assist.

many thanks.

Wednesday, July 17, 2013 - 1:13:51 AM - Mukul Sharma Back To Top (25869)

Hi Manvendra, such a useful article. Great Job Brother.

Friday, July 12, 2013 - 5:57:28 AM - Nitin L Back To Top (25810)

Very Nice :)

Thursday, July 11, 2013 - 12:31:03 PM - Nitin Back To Top (25802)

Great article with clear steps Manvendra.

Rgds,
Nitin

Monday, July 8, 2013 - 9:52:05 PM - Deepak Kumar Back To Top (25750)

Greate Article, Manvendra!! Thanks for sharing the knowledge as always.

 

 

 














get free sql tips
agree to terms