SQL Server DBA: How to change server collation

The Server Collation acts as the default collation for all the system databases on that instance of SQL Server and also for the newly created user databases.

The Collation for an instance is specified during the setup of SQL Server, whereas this can be changed at any point of time by rebuilding the master database and specifying the new collation.

This operation will overwrite the system databases and hence it is strongly recommended to have a complete system backup before proceeding with this activity.

Before you proceed,

Make sure you have backup of all user database, jobs, logins, maintenance plans, etc..
Drop / Detach all user databases
Rebuild Master database by specifying new collation

For SQL Server 2005:

Check the current Collation of the server by running the below script

SELECT SERVERPROPERTY('collation') AS [Server Collation]

Navigate to the setup path using command prompt and run the below query by changing the parameters

start /wait setup.exe /qb INSTANCENAME=SQL2005 REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=yourSApassword SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

This will start the GUI for setup

Once the Installation of Prerequisites is completed, you will be presented with the below screen

Click "Yes"

Once this configuration is completed, it will automatically close the GUI.

Now you can verify the change of collation by executing the below commands

SELECT SERVERPROPERTY('collation') AS [Server Collation]

For SQL Server 2008, SQL Server 2008 R2, SQL 2012,

Check the current Collation of the server by running the below script

SELECT SERVERPROPERTY('collation') AS [Server Collation]

Navigate to the setup path using command prompt and run the below query by changing the parameters

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName
/SQLSYSADMINACCOUNTS=adminaccount /[ SAPWD= StrongPassword ]
/SQLCOLLATION=CollationName