Changing SQL Server 201X Instance Collation after installation

Code to change SQL Server instance collation

In this short tutorial, we will be looking at changing the collation for various versions of SQL Server Databases after installation is done.

What is a database collation?

Collation is a set of rules that guides character encoding that determines how data is stored.

In SQL Server, the default collation set for an instance after installation is SQL_Latin1_General_CP1_CI_AS. It is important to note that new databases created in a particular instance will take on that instance collation unless stated otherwise.

Changing the Collation in various SQL Server versions

To begin we will be changing the SQL Server’s collation from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.

  • In our first step we will first check the current collation of the instance as well as the system databases. To do this open management studio and execute the query below.
SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Collation';
SELECT name, collation_name FROM sys.databases WHERE name = 'master';
SELECT name, collation_name FROM sys.databases WHERE name = 'tempdb';
  • The next step is to stop the database services. We can do this from the database configuration manager. See the gif below. Stopping sql server database services
  • Next we will have to navigate to the binn directory of SQL Server using cmd with administrator privilege. Follow the GIF below to access the path quickly. Once in the binn directory, please make note of the sqlservr.exe file. We will be using this file in the next step. Navigate to SQL server binn directory
  • With the database service stopped, we now need to execute the code below to start the process of changing the database collation. sqlservr -m -T4022 -T3659 -s"MyInstance" -q"Latin1_General_CI_AS" Code to change SQL Server instance collation

The break Down

sqlservr

SQL Server executable file

-m

Puts the database in single user mode, so only you can connect.

-T

Turns on the trace flag at startup. 4022 – skips SQL server startup procedures. 3569 – enables error logging at startup

-s

Specify the instance name (only use if SQL Server has a named instance)

-q

Specify the new collation that you want to change to

  • One you’ve hit enter above, the conversion process will now begin. All databases and indexes on the instance will be converted to the newly selected collation. This might take some time depending on the number of databases on the instance and their sizes. Press Ctrl + C when the process is completed and then Y to shut down the database.
  • Start up the instance and execute the script below to confirm if the collation of the instance and the databases were indeed changed.
SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Collation';
SELECT name, collation_name FROM sys.databases WHERE name = 'master';
SELECT name, collation_name FROM sys.databases WHERE name = 'tempdb';

Conclusion

Their might be need to change a database instance collation for various reasons. It is important to note that changing the instance collation will change the collation for the databases within that instance. Before installing a new database instance it is best to get the necessary information from the end users of the database before proceeding. Please see our article on How to add a new Named Instance in SQL Server.