Changing Collation After Installation of SQL Server

A simple way to correct the collations in a few steps is outlined in this tip. It is recommended to create a backup of all databases (including system databases) before take administrative actions on a SQL Server instance. It is important to ensure that there is no fixed collation logic in columns or inside stored procedures, triggers, etc., otherwise the command below may report problems.

Step 1 - Determine the SQL Server Collation

Let's confirm the current SQL Server instance collation and all it databases including system databases collation before taking actions.

SQL Server Instance Collation

SQL Server Master Database Collation

SQL Server DBTest Database Collation

The server has the "Latin1_General_CI_AS" collation and we'll change it to "SQL_Latin1_General_CP1_CI_AI" for this test.

Step 3 - Open a Command Prompt and Navigate to the Binn Directory

Now we have to open a command prompt with administrative privileges and go to the BINN directory of Microsoft SQL Server, following the example below:

This picture shows the SQL Server Binn directory and "sqlservr.exe" that will be used in this test.

Step 4 - Apply a New SQL Server Collation

Execute the command below. A lot of information will appears and no user action is required, just close the prompt window after the execution ends.  The parameter "-s" is only necessary if more than one SQL Server instance exists on the target machine.

sqlservr -m -T4022 -T3659 -s"SQLEXP2014" -q"SQL_Latin1_General_CP1_CI_AI"

Parameters used:
[-m] single user admin mode
[-T] trace flag turned on at startup
[-s] sql server instance name
[-q] new collation to be applied

Please refer MSSQL Tips blog.