Change SQL Server 2016 default collation after installation

I have had to change the default collation and the collation of SQL Server 2016 and did not want to re-install. It was a fresh setup but the backup and maintenance stuff was already setup.

So I searched for a way to change the collation of SQL Server and the System DBs on an easy way.

And luckily I found this blog entry from Giampaolo TUCCI (https://www.informaticapressapochista.com/windows/sql-server-2016-change-sql-server-collation-installation/).

As described there it was in general very easy.

❗ My Server was a fresh install. In other cases do a good Backup in advance before you continue ❗

A downtime is needed as you need to stop SQL Services.

So this is the approach:

• Stop all SQL Services
• start a cmd and go to the SQL Server BINN directory. Default should be C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn>
• run the following command: sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP1_CI_AS"
  After q set the collation you want to have.
  T are trace flags
  m is single user mode
  in case you have a named instance use s flag
• Finally you should see a message like this:
  2020-03-06 14:28:45.34 spid4s      The default collation was successfully changed.
  2020-03-06 14:28:45.67 spid4s      Recovery is complete. This is an informational message only. No user action is required.
• Then press ctrl+c to stop the SQL service
• Start the services again and check if the collation is as expected.

Enjoy your weekend guys.