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

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

Save Password for RDP Connections in Windows 10

In the old environment we were able to configure RDP connections for user to automatic logon to special systems e.g. Servers where their needed software is running. In Windows 10 the security behavior has changed which by default does not allow to save passwords for an RDP connection. Also not via a text editor in the file itself. This is the setup we need to use to make this available again. (Source:  https://serverfault.com/questions/867467/rdp-file-with-embedded-password-asks-for-password ) Create a RDP Connection file as you did before and save at the destination you want to use, e.g. Desktop. Make sure the save credentials box is ticked to save the username. Set the local security policy to allow storing passwords open policy editor gpedit.msc Go to: Computer Configuration → Administrative Templates → Windows Components → Remote Desktop Services → Remote Desktop Connection Client Set the policy "Do not allow passwords to be saved" to the value "Disa...
Post a Comment
Read more

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