In this article we will see how we can change the collation of a SQL Server post installation. To achieve this we need to rebuild the master database. While rebuilding the master database, the process gives an option to change the collation. Let us understand the process step by step and change the collation of an already installed SQL Server.
Step 1: Take a backup of all your system databases and user databases.
Step 2: Script out all Logins.
In this example we will change the collation from ‘SQL_Latin1_General_CP1_CI_AI’ to ‘SQL_Latin1_General_CP1_CI_AS’
Step 3: Stop the SQL Server Service.
Step 4: Via Command prompt locate to the Binn directory of the SQL Server
Step 5: Run the below command
sqlservr -m -T4022 -T3659 -s”INST2012_1″ -q”SQL_Latin1_General_CP1_CI_AS”
[-m] Single User Mode
[-T] Trace flag turned on at startup
[-s] SQL Server Instance Name
[-q] New Collation
Step 6: Restart SQL Server and check the collation