change server level collation for a SQL Server Instance

Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation.  It is not mandatory that we change the default server level collation, because you can specify a different collation level when you create users databases, but you need to remember to specify this when creating user databases.

To change the default SQL Server collation you can simply rebuild the system databases. When you rebuild the master, the model, msdb and tempdb system database are actually dropped and recreated in their original location. If a new collation is specified in the rebuild statement the system databases are rebuilt using that collation setting. Any user modifications to these databases will be lost, so it is important to backup any of this information you wish to retain

Step 1

First check the existing SQL Server collation setting of your instance. Run the command below to get the collation value of your SQL Server instance.

SELECT SERVERPROPERTY('Collation')

Step 2

Make sure to record all server level settings before rebuilding the system databases to ensure that you can restore the system databases to their current settings.  

Step 3

Take backup of all user and server databases.

Take the backup of all jobs, maintenance plans, logins and their access levels

To take all jobs backup in OBJECT EXPLORER window select job folder & in right side OBJECT EXPLORE DETAIL window it displays all jobs, select all jobs and right click on your selection then choose the SCRIPT AS option to create the script for all jobs.

Next is to secure your logins, passwords and their access levels. You can use sp_help_revlogin stored procedure to create a script for all logins so they can be recreated easily. And take server level permissions using script

Step 4

Detach all user databases before rebuilding your system databases. If you leave databases attached they will be detached and will be found in the database folder.

Step 5

Now its time to rebuild your system databases. This operation will recreate your master database and all existing settings will be reset. Run the below command from a Windows command prompt. Make sure to run this command from the directory where you have placed your SQL Server setup files. Once you press enter, a separate window will appear to show you the progress bar. Once the rebuild is done, that window will disappear.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MANVENDRA /SQLSYSADMINACCOUNTS=domain\adminUser /SAPWD= SaPWD@SqlRebuild /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

* SQL_Latin1_General_CP1_CI_AI –this is the new collation need to change

Once the rebuild operation is complete, check the server collation to verify whether this change is successful or not. As we can see in the screenshot below, the server collation has changed to SQL_Latin1_General_CP1_CI_AI. At this point we cannot restore any of the system databases, because doing so will revert back to the previous collation setting. So we will need to use the scripts that were created to recreate logins, jobs

Step 6

Attach all user databases which were detached in Step 4.

Step 7

Now change the collation settings of all user databases. It's not necessary to change the collation settings for the user databases, it totally depends on your requirement.

Run the commands below to change the collation settings of your user databases.

ALTER DATABASE CollationChangeDBName collate SQL_Latin1_General_CP1_CI_AI

Sometimes the command fails to execute and you get this error:

Msg 5075, Level 16, State 1, Line 1 The object 'PK_xxxx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

In that case you may need to export all data and recreate the database with the new collation settings.

Step 8

Now run all of the scripts which were created in Step 3 to restore jobs, alerts, logins, operators, etc...  Also don't forget to change the server level configuration settings which were captured in Step 2.

Now your instance is ready to use the new server level collation.