Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to change server level collation for a SQL Server Instance


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

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


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.


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, October 06, 2016 - 1:41:28 AM - Prathmesh Back To Top

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

 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

 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

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

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 09, 2015 - 2:21:25 AM - Basil Nhlanhla Back To Top

Thank you for sharing my problem is solved Manvendra Singh


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

Does not work with SQ Server 2008 Standard Version.


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

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

Thanks for sharing!

Solved my problem.


Tuesday, October 07, 2014 - 9:43:38 AM - leocuba Back To Top

Muchas gracias ha sido genial su inforamción

 


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

Thanks......

It helped a lot... :)


Thursday, August 07, 2014 - 6:05:47 PM - Rafael Matias Silva Back To Top

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

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

tanks a lot

it works like a miracle ;)


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

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

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

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


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

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

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


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

Very Nice :)


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

Great article with clear steps Manvendra.

Rgds,
Nitin


Monday, July 08, 2013 - 9:52:05 PM - Deepak Kumar Back To Top

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

 

 

 


Learn more about SQL Server tools