As a part of my DBA activities, I do a lot of SQL Server installations every week. Most of the time, I install the instance with the default collation. A collation is a configuration setting in SQL Server that determines how the database engine should read the data. SQL Server has huge list of collations for handling different languages. Now I may have a habit of hitting the Next button when I am on the collation page, simply ignoring it.

Recently, however,  I had a requirement from one application team to have a different collation. As usual, I ignored the collation setting during installation. As a result, the SQL Server installed with the default collation. After finishing the installation, I realized that this SQL instance did not have the required collation. So, what should I do now? I only have a limited time left for the delivery of this instance.

Either I should uninstall and install from scratch, which could take hours. Is there an easier way to change the instance collation which could save my time? I know it's just a matter of right clicking and making a change if I need to change that Database collation setting, however, at the instance level it's bit tricky.

Yes, there is an easier and faster way to change the instance collation. You can change the collation by rebuilding the system databases.  That could be easily possible with the help of the setup.exe command. Below, I will show you an example how to change the collation from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CI_AI.

Take care of following steps if user databases are available:

1. Backup user databases
2. Detach user databases
3. Script maintenance plans, LOGINS and jobs because rebuilding the databases will reset everything.
4. Run setup.exe command from a command prompt as shown below
5. Attach the user databases
6. Run scripts from step no. 3

This article will not cover steps 1, 2, and 3. You should be able to complete those before attempting this procedure. Below we will see how to rebuild your system databases.

Step 1: Check the Instance collation

We can check the collation for the instance through SSMS. As you can see, it shows SQL_Latin1_General_CP1_CI_AS.

-- check instance collation
SELECT convert(sysname, serverproperty(N'collation')) AS [Instance Collation]
-- check current database collation
SELECT name as [DB Name], collation_name FROM sys.databases

Step 2: Run setup.exe

Open a command prompt. Change to the path where the setup files are located. By default they should be this location: C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017

I want to change the collation from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CI_AI. In that case, I need to enter the required collation name in below command i.e. SQL_Latin1_General_CP1_CI_AI. Note that the action parameter is REBUILDDATABASE.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME="SQL\ILEARNSQL" /SQLSYSADMINACCOUNTS=SQL\Charaya /SAPWD= iLearnSQLiLearnSQL /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

/QUIET – perform silent installation
/ACTION– Rebuild the system databases
/INSTANCENAME – Default Instance is “MSSQLSERVER” OR Named Instance "SQL\ILEARNSQL" [used in above example]
/SAPWD – Provide strong password for SA login; Enable SA Account if it Disabled
/SQLCollation – Provide the new collation name [example SQL_Latin1_General_CP1_CI_AI]
/SQLSYSADMINACCOUNTS – Provide account name which has admin rights in SQL server [example windows authentication: SQL\Ilearnsql]

Setup will take few minutes. It should complete successfully.

Step 4: Verify the Change

We can verify the collation from the log file. It should be at C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20200621_135900. We can also verify the collation setting through the SSMS with the same query as above. You will notice the collation has changed when I re-run the query.

-- check instance collation
SELECT convert(sysname, serverproperty(N'collation')) AS [Instance Collation]
-- check current database collation
SELECT name as [DB Name], collation_name FROM sys.databases

Once this is done, don't forget to attach your databases and run your scripts that you generated before running setup.

Conclusion

You can change the SQL Server collation at instance level with single setup command by rebuilding system databases. There is no need to uninstall the software completely. Also, it will save lot of time & effort.

Note: DO NOT run directly in your production environment; try it on lower environment first.