Updating SQL Server Instance Collation without rebuilding Master Database

Whether a SQL Server DBA is a newbie or an experienced, rebuilding the master database is one of the most dreaded tasks.

It’s similar to the fear of walking into the unknown without knowing what’s on the other side of the road.

Even difficult tasks may be conquered with careful planning and documentation, as I will demonstrate when I was forced to update SQL server collation without rebuilding the master database.

All of our on-premises SQL server instances use the default SQL Server collation, SQL Latin1 General CP1 CI AS, however one of them was installed with Latin1 General CP1 CI AS owing to a human error.

To update SQL Server collation there are two ways
1. Uninstall and Install again with intended collation
2. Update SQL Server collation with below steps.

Step 1: Open Command Prompt with administrative permissions and type:
sc queryex type: service state: all | find /i “SQL Server”

Step2:
NET STOP “MSSQLServer”

Step3:
Traverse to the BINN directory of Microsoft SQL Server, following the example below:
C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\

Step 4: Apply a New SQL Server Collation
sqlservr -m -T4022 -T3659 -q”SQL_Latin1_General_CP1_CI_AS “

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

Step 5:
Start SQL Services

NET START “MSSQLServer”

And Voila you are ready to Rock!!!

Advertisement