How to change collation for a SQL Instance

In this post we will see how to change your SQL instance collation. To do this we have to do the rebuild of master database with new collation. During the rebuild process all user database will be detached and all system database will be dropped and recreated. 

Before proceeding with rebuild, we have to record the below

-Instance wide configurations

-Service Pack and Hotfixes

-Take a backup of all the user and system database (TempDB not required).

-Run SP_HELP_REVLOGIN and make a note of all the user permissions as well

-Script out all agent jobs, maintenance plans and all server objects.

It’s always recommended to change the collation in Test Environment and do a proper validation before implementing in production.  Now let’s move to the demonstration.

First make a note of current collation

                SELECT SERVERPROPERTY(‘COLLATION’)

Now run the below command from command prompt.  Note this should be executed from the path where SQL setup files are placed. Make all the required changes as per your requirement. In my case

                Instance Name : SQL2012

                Sysadmin Account : NAVEEN-PC\NAVEEN

                Password : 123456789

                New SQL Collation : SQL_Latin1_General_CP1_CI_AS

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQL2012 /SQLSYSADMINACCOUNTS=NAVEEN-PC\NAVEEN /SAPWD=123456789 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

 

Once the above command is executed successfully, connect to your SQL Instance and check the collation.

                SELECT SERVERPROPERTY(‘COLLATION’)

Now apply service pack, hotfixes, run sp_help_revlogin script output, restore all user database, map all the permissions, create all agent job and that’s it. We are done

Cheers,

Naveen