Skip to main content

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.
Labels:

Comments

Post a Comment

Popular posts from this blog

Robocopy and special characters

When working international there will some day someone ask you to mirror a folder to different locations. And for sure we all know robocopy and how to use it. Else go to this  https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/robocopy Luckily robocopy is now integrated to Windows you can directly work with it. But it is struggeling with special characters of my differnt countries customers. So I found  now this superuser.com entry  (I love this QA page system and the users behind it) and it helped me to solve it. 1. Add "chcp 65001" to your batch file, chcp means change code page and 65001 is UTF-8 2. Save the batch file as UTF-8 without BOM. I used notepad++ for it. It can convert the file during edit directly Remark: The original poster at superuser.com users VARs for the folder names but I did not need it for my case and also did not used it as it was a very very simple script.
Post a Comment
Read more

Install Windows Feature with Powershell

I have sometimes situations where I need to rollout a similar setup of Windows Features from a live system to a development system. Therefore the Powershell cmdlt Install-WindowsFeature is very useable. Also in our case the sxs folder is needed. Here my example how we use it: Install-WindowsFeature -ConfigurationFilePath C:\temp\DeploymentConfigTemplate.xml -Source C:\PSEMEAIT\sxs\
Post a Comment
Read more

What are you doing here?

Hi guys, if anyone really finds this address it might happen only by an acident. But I assume not by purpose. I will not try to advertise this blog or do anything else. This is more a droplocation for information I collected during my daily work as an IT Admin for a bigger company. I mainly work in a VMWare environment with Windows Servers. So these might be the main topics you find in here. Anyhow. If you really find something usefull just tell me. CU maybe in future. Cheers Flying C
Post a Comment
Read more