Updating SQL Server Instance Collation without rebuilding Master Database

Rebuilding master database is one of the dreaded task for a SQL Server DBA whether they are novice or expert.

It is like fear of venturing into unknown without really knowing whats at other end of the road.

However with proper planning and documentation even difficult tasks can be conquered and so I will tell you about the scenario when I was forced to update SQL server collation without rebuilding master Database.

All our on-premises SQL server instances are on default SQL Server collation i.e. SQL_Latin1_General_CP1_CI_AS , however due to a human error one of the server got installed with Latin1_General_CP1_CI_AS.

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!!!

Advertisements