to change collation for system database SQL Server 2017

All we need is an easy explanation of the problem, so here it is.

We are using fresh SQL Server 2017 CU23 Standard Edition database with "SQL_Latin1_General_CP1_CI_AS". Now, I want to change all system databases (master , model , temp, msdb) to another collation. how to change this without installing SQL Server ?

thanks,

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The only way to change the collation for the instance itself (instance-level and system DBs plus user databases) without reinstalling is an undocumented command/option. The "rebuilding system databases" option referred to in the other answers is effectively reinstalling, even if it isn’t in a literal sense, nor does that approach update any user databases to match the new collation.

I posted a detailed analysis of this undocumented approach, and the implications of making any collation changes to an instance and/or database, here:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

There are quite a few areas that could be affected, and it depends greatly on if you are talking about just one particular database, or the instance and one or more of the databases on that instance, and if you have any VARCHAR data containing characters with values of 128 – 255, and if you are switching sensitivities (e.g. case-sensitive to case-insensitive, or vice-verse).

With regards to the ALTER DATABASE ... COLLATE ... option:

  1. That does not work on system databases.
  2. That is not permitted if you have one of many dependencies (e.g. TVFs returning string columns, computed column returning string types, etc).
  3. For user databases where it is permitted, it only changes the system tables (e.g. sys.objects, sys.schemas, etc); it does not change any user tables (columns or indexes)

Method 2

The Microsoft Books Online for Set or Change the Database Collation explain how to do so, specifically how to in SSMS in the section Using SQL Server Management Studio. Alternatively you can script it as well like so:

ALTER DATABASE DatabaseName  
COLLATE French_CI_AS;

If neither of these options work for system databases, then it’s not possible to do so without running the installer or rebuilding the system databases.

Either way, I would advise being careful with changing the collation of the system databases as you can run into a number of issues if not done properly. For example, a case sensitive collation that needs to interact with a case insensitive context can clash and produce errors. Also using the COLLATE function to compare between two different collation contexts can result in cardinality estimate issues.

Also, please note that if you set the model database to a collation different from the server’s collation then all newly created databases will take the same collation as the model database and ignore the server’s collation.

Method 3

You can change the instance and system database collation with the setup rebuild database action. This will recreate the system databases with the specified collation without re-installing the binaries. The system databases after the rebuild will essentially be a fresh install so any changes to system objects (logins, jobs, etc.) will be lost.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply