Change SQL Collation without rebuilding system databases

Change SQL Collation without rebuilding system databases

How many of you want to change the SQL Server Collation without rebuilding the system databases?

In this post, I will explain a method using which the server collation can be easily changed and is through a undocumented start up parameter.
Since this is undocumented, I highly recommend to use this with extra care and with your own risk.

These method uses -q startup parameter while starting the SQL Server service.

Method 1:

1. Detach all user databases
2. Make backup of all other required database and server objects
3. Stop SQL Server Service
4. Start the SQL Server Service using command Prompt
   net start "SQL Server (<Instance Name>)" /m /T4022 /T3659 /q"<New Collation Name>"
   
5. Re-start (Stop and Start) the SQL Services Normally without any startup parameters
6. Check for the change in Server Collation
   
7. Attach the user databases back

Method 2:

1. Detach all user databases
2. Make backup of all other required database and server objects
3. Stop SQL Server Service
4. Start the SQL Server Service using command Prompt
   sqlservr -m -T4022 -T3659 -q"<New Collation Name>"
   
   
   
5. Now check for the message "Recovery is completed" and then press CTRL+C to Stop the SQL Server service
6. Start the SQL Services Normally without any startup parameters
7. Check for the change in Server Collation
   
8. Attach the user databases back

Note:

1. If you do not detach the user databases before changing the server collation then the process will change the collation for all the databases.
2. Trace flag 3659 allows logging all errors to sql server logs
3. Trace flag 4022 forces SQL Server to skip startup stored procedures (if any)
4. Startup Parameter “-m” forces single user mode.
5. Startup Parameter  -q” rebuilds all databases and objects to the specified collation, without reinstalling the instance or rebuilding system databases.