To change SQL server collation without rebuilding the instance


A few days ago, I came across an issue where we had to change the SQL server instance collation and we were just asking the clients to rebuild the system databases to do so in the previous cases. But this time the client could not afford to do that and asked for a plan B.

In the case, they required to change the collation from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP850_CS_AS.

To START off with the action plan we STOPPED the SQL service. 😀

Post which we started the SQL Server in single-user mode from the command prompt by navigating the command prompt to the Binn directory,

sqlservr -m -T4022 -T3659 -q"SQL_Latin1_General_CP850_CS_AS"

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

TF4022 is to bypass startup procs.
TF3659 on the other hand is supposed to write errors to the error log

Thus we had the collation for the system and the user databases changed from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP850_CS_AS. Also verified the same by querying for collation property from sys.databases.

Hope this helps !! Happy collating !!

Just as a caution, note that this approach is not a supported/ recommended approach of sorting way out for this issue and thus please make sure this action plan is implemented on any production environments. This is just for an immediate fix at ones own risk.


Comments (0)

Skip to main content