Changing SQL Server Collation In Simple Steps

  • Article

Here is the simpler steps to change the SQL Server Instance Collation, Please note once the collation is changed we may need to modify the query according to the need as the collation it was referring will change. 

Steps:

  1. Open the CMD in elevated privilege

  2. Check the services

    sc queryex type= service state= all | find /i "SQL Server"

  3. Stop SQL Server NET STOP "SQL Server (SQLEXPRESS2008R2)"

  4. Move to Binn Directory from CMD 
    EXAMPLE: D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\Binn

  5. type DIR and Check SQL Server.exe is listed  

  6. Apply a New SQL Server Collation

    sqlservr -m -T4022 -T3659  -s"SQLEXPRESS2008R2"  -q"SQL_Latin1_General_CP1_CI_AS"

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

    4022

    Used to bypass automatically started (startup) procedures, this is a subset of startup option –f. 
    TIP: Each SP consumes one worker thread while executing so you may prefer to make one startup procedure that calls others.

    3659

    https://spaghettidba.com/2011/05/20/trace-flag-3659/

    After a lot of research, I found a reference to this flag in a script called AddSelfToSqlSysadmin, written by Ward Beattie, a developer  in the SQL Server product group at Microsoft.
    The script contains a line which suggests that this flag enables logging all errors to error log during server startup.

  7. SQL Server may start in single user mode, if command prompt doesn't end press ctrlX, this will prompt Y/N to shutdown SQL Server, Choose Y

    2018-08-01 17:12:30.25 spid7s      The default collation was successfully change
d.
2018-08-01 17:12:30.26 spid7s      Recovery is complete. This is an informationa
l message only. No user action is required.
2018-08-01 17:12:42.01 Logon       Error: 18461, Severity: 14, State:  1.
2018-08-01 17:12:42.01 Logon       Login failed for user 'NT AUTHORITY\NETWORK S
ERVICE'. Reason: Server is in single user mode. Only one administrator can conne
ct at this time. [CLIENT: <local machine>]
2018-08-01 17:13:41.91 Logon       Error: 18461, Severity: 14, State:  1.
2018-08-01 17:13:41.91 Logon       Login failed for user 'NT AUTHORITY\NETWORK S
ERVICE'. Reason: Server is in single user mode. Only one administrator can conne
ct at this time. [CLIENT: <local machine>]
Do you wish to shutdown SQL Server (Y/N)? Y

  8. Start SQL and verify the collation

    NET START "SQL Server (SQLEXPRESS2008R2)"
 
 
D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS2008R2\MSSQL\Binn>
 NET  START "SQL Server (SQLEXPRESS2008R2)"
The SQL Server (SQLEXPRESS2008R2) service is starting.
The SQL Server (SQLEXPRESS2008R2) service was started successfully.

Note: The steps mentioned in this Wiki is only for educational purposes only. The supported method to change the collation is mentioned here:
Set or Change the Server Collation - SQL Server | Microsoft Learn